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.
您能否建议在某个日期范围内点击排名前10的产品如何获得结果?我被困在了解如何构造子查询。我只能按日期分组获取零件,但随后我的头脑就陷入了count()和聚合问题。
这是我查询的单个日期,但是我想输入一个日期范围。当然,我可以通过生成事件日期来进行子查询,但是想弄清楚如何更好地做到这一点。
SELECT TOP 10 COUNT() as count, PID
FROM view_product
WHERE EventDate = toDate('2020-05-11')
GROUP BY PID
ORDER BY count DESC
预期的输出是这样的:
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