I am using MySQL 8.0
I have a table that looks like this:
id user_id item_id time
1 3111 4423 2020-06-01 01:00:00
2 3111 4423 2020-06-01 02:00:00
3 2971 36 2020-06-01 01:00:00
4 2971 36 2020-06-01 02:00:00
5 2971 36 2020-06-01 03:00:00
6 2971 815 2020-06-01 22:00:00
I am trying to partition by user_id, item_id and create a column that counts number of row for that partition.
I've done:
select *
, count(id) over(partition by user_id, item_id order by time) as cnt
from table
This outputs:
id user_id item_id time cnt
1 3111 4423 2020-06-01 01:00:00 1
2 3111 4423 2020-06-01 02:00:00 2
3 2971 36 2020-06-01 01:00:00 1
4 2971 36 2020-06-01 02:00:00 2
5 2971 36 2020-06-01 03:00:00 3
6 2971 815 2020-06-01 22:00:00 1
Which is almost what I want, however I want max cnt value for each partition.
Desired output:
id user_id item_id time cnt
1 3111 4423 2020-06-01 01:00:00 2
2 3111 4423 2020-06-01 02:00:00 2
3 2971 36 2020-06-01 01:00:00 3
4 2971 36 2020-06-01 02:00:00 3
5 2971 36 2020-06-01 03:00:00 3
6 2971 815 2020-06-01 22:00:00 1
Thanks in advance.
EDIT:
This still outputs same result...
SELECT *
, max(cnt) over(partition by user_id, item_id order by time) as max_cnt
FROM ( select *
, count(id) over(partition by user_id, item_id order by time) as cnt
from table) subtable;
Just remove the ORDER BY clause from call to COUNT:
SELECT *, COUNT(id) OVER (PARTITION BY user_id, item_id) AS cnt
FROM yourTable;
Using COUNT in this way will just return the total count of records for each user/item partition, which seems to be what you want here.