Top 10 products for dates between

user_696034 0
Viewed 93

I'm trying to figure out the better way of creating a single query that will produce a result with top 10 products for each date. I have a row with two columns - PID (int) and EventDate (date), a row for each click.

Can you suggest how I can get the result with Top 10 products clicked on for a date range? I'm getting stuck on understanding how the sub-query must be constructed. I can only get the part with group by date, but then my mind gets stuck on count() and aggregation issues.

Here's my query for an individual date, but I want to have to for a range of dates. I can, of course, do sub-query by generating event dates, but want to figure out how to do it more elegantly.

SELECT TOP 10 COUNT() as count, PID
FROM view_product
WHERE EventDate = toDate('2020-05-11')
GROUP BY PID
ORDER BY count DESC

The expected output is something like this:

PID Count Date
1   123   2020-02-04
21  101   2020-02-04
1332 99   2020-02-04
11   51   2020-02-04
634  49   2020-02-04
1332 43   2020-02-04
1   24   2020-02-04
21  23   2020-02-04
1332 6   2020-02-04
11   3   2020-02-04

1   266   2020-02-02
21  241   2020-02-02
1332 232   2020-02-02
11   179   2020-02-02
634  163   2020-02-02
1332 159   2020-02-02
1   144   2020-02-02
21  100   2020-02-02
1332 99   2020-02-02
11   74   2020-02-02
clickhouse
1 Answers

It needs to use LIMIT BY-clause that takes 10 top rows of each day:

SELECT
  PID, 
  EventDate,
  count() AS Count
FROM view_product
WHERE EventDate >= '2020-05-01' AND EventDate < '2020-06-01'
GROUP BY EventDate, PID
ORDER BY EventDate, Count DESC
LIMIT 10 BY EventDate;

The test example:

SELECT
  PID, 
  EventDate,
  count() AS Count
FROM (
  /* emulate test set */
  SELECT test_data.1 AS PID, toDate(test_data.2) AS EventDate
  FROM (        
    SELECT arrayJoin([
        (1,    '2020-02-04'),
        (21,   '2020-02-04'),
        (1332, '2020-02-04'),
        (11,   '2020-02-04'),
        (634,  '2020-02-04'),
        (1,    '2020-02-04'),
        (1,    '2020-02-04'),
        (21,   '2020-02-04'),
        (1,    '2020-02-04'),
        (1,    '2020-02-02'),
        (21,   '2020-02-02'),
        (11,   '2020-02-02'),
        (1332, '2020-02-02'),
        (1332, '2020-02-02'),
        (1332, '2020-02-02'),
        (11,   '2020-02-02')]) test_data))
GROUP BY EventDate, PID
ORDER BY EventDate, Count DESC
LIMIT 2 BY EventDate;
/* result
┌──PID─┬──EventDate─┬─Count─┐
│ 1332 │ 2020-02-02 │     3 │
│   11 │ 2020-02-02 │     2 │
│    1 │ 2020-02-04 │     4 │
│   21 │ 2020-02-04 │     2 │
└──────┴────────────┴───────┘
*/

To get just n-top items without count-values use topK-aggregated function:

SELECT 
  EventDate,
  topK(10)(PID)
FROM (
  /* emulate test set */
  SELECT test_data.1 AS PID, toDate(test_data.2) AS EventDate
  FROM (        
    SELECT arrayJoin([
        (1,    '2020-02-04'),
        (21,   '2020-02-04'),
        (1332, '2020-02-04'),
        (11,   '2020-02-04'),
        (634,  '2020-02-04'),
        (1,    '2020-02-04'),
        (1,    '2020-02-04'),
        (21,   '2020-02-04'),
        (1,    '2020-02-04'),
        (1,    '2020-02-02'),
        (21,   '2020-02-02'),
        (11,   '2020-02-02'),
        (1332, '2020-02-02'),
        (1332, '2020-02-02'),
        (1332, '2020-02-02'),
        (11,   '2020-02-02')]) test_data))
GROUP BY EventDate;
/* result
┌──EventDate─┬─topK(10)(PID)──────┐
│ 2020-02-02 │ [1332,11,1,21]     │
│ 2020-02-04 │ [1,21,1332,11,634] │
└────────────┴────────────────────┘
*/
user_3032980
Related