使用GROUP BY时如何创建“other”分组？

可以通过单次查询实现需求，利用窗口函数和条件分组即可完成，以下是针对你的场景的具体实现：

WITH grouped_cities AS (
    SELECT 
        name,
        COUNT(*) AS count,
        -- 按计数降序给每个分组排名
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
    FROM cities
    GROUP BY name
)
SELECT 
    -- 排名前2的保留原名称，其余统一为'other'
    CASE WHEN rn <= 2 THEN name ELSE 'other' END AS name,
    -- 对相同名称（包括other）的计数求和
    SUM(count) AS count
FROM grouped_cities
GROUP BY CASE WHEN rn <= 2 THEN name ELSE 'other' END
-- 最终结果按计数降序排序
ORDER BY count DESC;

执行结果： #

| name   | count |
| ------ | ----- |
| london | 4     |
| paris  | 2     |
| other  | 2     |

逻辑说明： #

1. CTE阶段：先对城市进行分组计数，同时用ROW_NUMBER()窗口函数给每个分组按计数从高到低分配排名。
2. 外层分组：通过CASE WHEN判断分组的排名，将排名超出前2的分组统一标记为other，然后基于这个标记重新分组求和，得到合并后的计数。
3. 灵活调整：如果需要保留前X行，只需把rn <= 2中的2替换为目标数字即可。

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