SQL带条件PARTITION BY实现按Type及Env模糊规则分组取TopN优化方案

优化方案 #

你的原有写法需要两次全表扫描再合并结果，大数据量下查询成本高的核心问题是重复扫表。可以通过一次全表扫描完成计算，直接在窗口函数的分区规则中嵌入Environment的分组逻辑即可：

SELECT Type, Environment, Count
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Type, CASE WHEN Environment LIKE '%M%' THEN 1 ELSE 0 END
            ORDER BY Count DESC
        ) AS rn
    FROM `table`
) AS t
WHERE t.rn <= 5

方案说明 #

1. 用CASE WHEN表达式将Environment字段按是否包含M划分为两类，返回的标识值和Type组合作为分区键，完全符合要求的分组规则
2. 仅需一次全表扫描即可完成所有窗口计算，省去了原方案中第二次扫表和UNION合并的开销，大数据量下性能提升非常显著
3. 写法兼容MySQL、PostgreSQL、Hive、Spark SQL等绝大多数主流SQL引擎，无需额外适配

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