如何在SQL中统计分组连续日期的组数？

嘿，这个需求用SQL的窗口函数就能轻松搞定！先理清楚核心逻辑：我们需要统计每个id下grp发生首次变化的次数，再加上初始的1组，就是每个id对应的grp组数，最后把所有id的结果加起来就能得到总组数5了。

原始数据回顾 #

先把你给出的原始数据整理成更清晰的表格：

具体SQL实现 #

这里用通用的SQL语法（支持窗口函数的数据库如MySQL 8.0+、PostgreSQL、SQL Server等都可以用）：

WITH group_change_flags AS (
    SELECT
        id,
        grp,
        -- 用LAG函数获取当前行的上一行grp值，对比是否不同
        CASE WHEN LAG(grp) OVER (PARTITION BY id ORDER BY admin_date) != grp THEN 1 ELSE 0 END AS new_group_flag
    FROM your_table_name
)
-- 先统计每个id的组数，再求和得到总组数
SELECT SUM(id_group_count) AS total_groups
FROM (
    SELECT
        id,
        -- 初始1组加上变化的次数，就是该id的总组数
        1 + SUM(new_group_flag) AS id_group_count
    FROM group_change_flags
    GROUP BY id
) AS id_group_stats;

逻辑解释 #

1. 窗口函数LAG的作用：按id分组（PARTITION BY id），按日期排序（ORDER BY admin_date），获取当前行的上一行grp值。如果当前grp和上一行不同，说明进入了新的分组，标记为1，否则为0。
2. 统计每个id的组数：每个id的初始组是1，再加上所有new_group_flag的和（也就是grp变化的次数），就是该id的总组数。比如id1的变化次数是1，所以1+1=2组；id2的变化次数是2，所以1+2=3组。
3. 求和得到总组数：把所有id的组数加起来，2+3=5，正好符合你的预期结果。

另一种简化写法（如果不需要单独看每个id的组数） #

如果只需要总组数，也可以直接统计所有new_group_flag的和，再加上id的总个数（因为每个id至少有1组）：

SELECT COUNT(DISTINCT id) + SUM(new_group_flag) AS total_groups
FROM (
    SELECT
        id,
        CASE WHEN LAG(grp) OVER (PARTITION BY id ORDER BY admin_date) != grp THEN 1 ELSE 0 END AS new_group_flag
    FROM your_table_name
) AS group_changes;

这个写法的逻辑是：每个id默认有1组（所以COUNT(DISTINCT id)就是初始的总组数基数），再加上所有grp变化的次数，结果也是5。

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