SQL实现按分组volume总和排序后取各分组Top N记录

核心逻辑分两步走：先计算每个channel的volume总和作为channel全局排序的权重，再基于这个权重完成组内编号、取TopN。

具体实现步骤 #

• 第一步：给原表每一行关联上所属channel的总volume值，不需要单独聚合后做join，用窗口函数可以直接在原表计算，性能更好。
• 第二步：用ROW_NUMBER()编号时，排序规则优先按channel总volume降序，再按你需要的组内排序规则（样例中为组内volume降序）排序，这样既保证高总量的channel整体优先级更高，也能正确标记组内排名。
• 第三步：过滤行号小于等于2的记录即可得到目标结果。

兼容所有SQL引擎的通用写法 #

WITH channel_with_total AS (
    SELECT
        *,
        -- 计算当前行所属channel的总volume
        SUM(volume) OVER(PARTITION BY channel) AS channel_total_vol
    FROM your_biz_table
),
record_with_rn AS (
    SELECT
        *,
        ROW_NUMBER() OVER(
            PARTITION BY channel
            -- 先按channel总volume降序保证channel优先级，再按组内规则排序
            ORDER BY channel_total_vol DESC, volume DESC
        ) AS row_num
    FROM channel_with_total
)
-- 过滤每个channel的前2条记录，最终结果按channel优先级、组内排序返回
SELECT channel, volume -- 替换为实际需要查询的字段
FROM record_with_rn
WHERE row_num <= 2
ORDER BY channel_total_vol DESC, volume DESC;

简化写法说明 #

如果用的是支持窗口函数嵌套排序的SQL引擎（比如BigQuery、Spark SQL、MySQL 8.0.2+等），可以把两层CTE简化成一层，不需要单独计算channel_total_vol字段，直接在ROW_NUMBER的ORDER BY里嵌入总volume计算逻辑即可，代码更简洁：

SELECT channel, volume
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(
            PARTITION BY channel
            ORDER BY SUM(volume) OVER(PARTITION BY channel) DESC, volume DESC
        ) AS rn
    FROM your_biz_table
) t
WHERE rn <=2
ORDER BY SUM(volume) OVER(PARTITION BY channel) DESC, volume DESC;

之前用ROW_NUMBER()没达到效果，本质是排序规则里没加入channel维度的总权重排序，只配置了组内字段的排序规则，自然没法实现先给channel整体排序、再取组内TopN的需求。

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