每维顶N项的计算

Question

我有下面的查询，它显示每个产品每小时的总销售额。然而，这是非常大的数据，我不想看到所有的产品，所以我想看到的1000 product_id基于sales为每个date，hour和category_id维度。

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

在雅典娜怎么做？

提前谢谢。

Answer 1

您可以对结果使用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