Athena如何按date等维度取各分组销量前1000的product_id

你可以用Athena内置的Presto窗口函数实现需求，完整实现逻辑如下：

WITH product_sales AS (
    -- 原有聚合逻辑，先统计每个商品分时维度的总销量
    SELECT 
        date,
        hour,
        category_id,
        product_id,
        sum(sales) AS sales
    FROM a
    -- 补全你原查询漏写的关联表名b，可替换为实际表名
    LEFT JOIN b 
        ON a.product_id = b.product_id
    WHERE date(date) >= date('2021-01-01')
    GROUP BY 1, 2, 3, 4
),
ranked_products AS (
    SELECT 
        *,
        -- 按date、hour、category_id分区，按销量降序排名
        ROW_NUMBER() OVER (
            PARTITION BY date, hour, category_id 
            ORDER BY sales DESC
        ) AS sales_rank
    FROM product_sales
)
-- 筛选每个分组下前1000的商品
SELECT date, hour, category_id, product_id, sales
FROM ranked_products
WHERE sales_rank <= 1000

逻辑说明 #

• 第一层CTE直接复用你的原有聚合逻辑，计算每个date+hour+分类+商品维度的总销量
• 第二层CTE通过ROW_NUMBER()窗口函数，在每个date+hour+category_id的分组内给商品按销量从高到低打排名
• 最后过滤排名小于等于1000的记录，即可得到每个分组下销量前1000的商品

如果需要保留并列排名的商品，可以根据需求替换窗口函数：

• 用RANK()：相同销量排名相同，后续排名跳空，可能返回超过1000条记录
• 用DENSE_RANK()：相同销量排名相同，后续排名连续，也可能返回超过1000条记录

内容的提问来源于stack exchange，提问作者datazang