在Amazon Redshift中编写SQL查询获取各品类最畅销订单的技术咨询

嘿，我来帮你搞定这个Redshift的查询需求！你要找每个品类的最畅销订单，核心思路是先计算每个订单的总营收，再在每个品类里筛选出营收最高的那个订单，用窗口函数就能轻松实现。

完整查询语句（基础版：每个品类仅取一个最畅销订单） #

WITH order_revenue AS (
    -- 先计算每个订单的总营收，按品类和订单ID分组
    SELECT 
        category,
        orderid,
        SUM(revenue) AS total_rev
    FROM XXX
    GROUP BY category, orderid
)
SELECT 
    category,
    orderid,
    total_rev AS rev
FROM (
    -- 给每个品类下的订单按营收降序排名
    SELECT 
        category,
        orderid,
        total_rev,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_rev DESC) AS rank_num
    FROM order_revenue
) ranked_orders
WHERE rank_num = 1;

关键部分解释 #

• CTE order_revenue：这一步是基础，因为你需要的是每个订单的总营收（而不是单条记录的营收），所以通过GROUP BY category, orderid来聚合求和，确保每个订单ID对应的总营收是准确的。
• 窗口函数ROW_NUMBER()：用PARTITION BY category把数据按品类分组，每个分组内按total_rev DESC（营收从高到低）排序，给每条记录分配一个排名。排名为1的就是该品类下营收最高的订单。

处理并列最畅销的情况 #

如果你的数据里存在同一个品类下多个订单营收相同且都是最高的，想要把这些并列的订单都保留下来，只需要把ROW_NUMBER()换成RANK()即可：

WITH order_revenue AS (
    SELECT 
        category,
        orderid,
        SUM(revenue) AS total_rev
    FROM XXX
    GROUP BY category, orderid
)
SELECT 
    category,
    orderid,
    total_rev AS rev
FROM (
    SELECT 
        category,
        orderid,
        total_rev,
        RANK() OVER (PARTITION BY category ORDER BY total_rev DESC) AS rank_num
    FROM order_revenue
) ranked_orders
WHERE rank_num = 1;

RANK()会给相同营收的订单分配相同的排名，所以所有并列第一的订单都会被筛选出来，而ROW_NUMBER()只会随机选一个（因为即使营收相同，它也会给不同的行分配不同的排名）。

另外提一句：你原查询里的DISTINCT是多余的，因为GROUP BY category, orderid已经保证了每个组合是唯一的，不需要再去重啦。

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