如何在SQL中按窗口统计各类别出现频次？

现有数据表数据如下：

id  flag  date
101   A     may
101   A     jun
101   1     jul
101   A     aug
101   1     sep
101   2     oct
101   3     nov
201   A     jun
201   A     jul

需要按id分组，统计各类flag的出现频次：

• flag为'1'的次数计入flag_1
• flag为'2'的次数计入flag_2
• flag为'3'的次数计入flag_3
• 其他所有flag值的次数计入flag_else
  最终要得到这样的结果：

id   flag_1  flag_2  flag_3  flag_else
101  2       1       1       2
201  0       0       0       2

直接用CASE WHEN配合GROUP BY就能搞定，这是大部分数据库都支持的通用写法：

SELECT
    id,
    SUM(CASE WHEN flag = '1' THEN 1 ELSE 0 END) AS flag_1,
    SUM(CASE WHEN flag = '2' THEN 1 ELSE 0 END) AS flag_2,
    SUM(CASE WHEN flag = '3' THEN 1 ELSE 0 END) AS flag_3,
    SUM(CASE WHEN flag NOT IN ('1','2','3') THEN 1 ELSE 0 END) AS flag_else
FROM your_table_name
GROUP BY id
ORDER BY id;

关于窗口函数的补充 #

你提到想尝试窗口函数，但这里需求是按id做汇总统计，GROUP BY的写法更直接高效。如果一定要用窗口函数，可以给每个行计算所在id的汇总值，最后去重，示例如下：

SELECT DISTINCT
    id,
    SUM(CASE WHEN flag = '1' THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS flag_1,
    SUM(CASE WHEN flag = '2' THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS flag_2,
    SUM(CASE WHEN flag = '3' THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS flag_3,
    SUM(CASE WHEN flag NOT IN ('1','2','3') THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS flag_else
FROM your_table_name
ORDER BY id;

不过这种方式会生成重复的汇总行，必须加DISTINCT去重，效率不如第一种方案，优先推荐用GROUP BY的写法。

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