我有以下查询,显示每小时每种产品的总销售额。然而,这是一个非常大的数据,我不想看到所有的产品,所以我想看到前1000个product_id基于每个date
、hour
和category_id
维度的sales
。
SELECT date,
hour,
category_id,
product_id,
sum(sales) AS sales
FROM a
LEFT JOIN
ON a.product_id = b.product_id
WHERE date(date) >= date('2021-01-01')
GROUP BY 1, 2, 3, 4
在雅典怎么做?
提前谢谢。
您可以对结果使用rank
函数,然后过滤出相应的排名:
SELECT date,
hour,
category_id,
product_id,
sales
FROM
(
SELECT *,
rank() OVER (PARTITION BY date, hour, category_id
ORDER BY sales DESC) AS rnk
FROM (your query)
)
WHERE rnk <= 1000