如何通过SQL窗口函数计算用户各状态周期的起止日期

这是SQL领域经典的连续相同状态区间合并（岛屿问题），可以通过窗口函数实现，具体逻辑和代码如下：

核心思路 #

1. 对每个用户的所有记录按日期升序排序
2. 为同一个用户下、连续相同的状态生成相同的分组标识：同一连续状态段内的所有记录会被分到同一个组
3. 按用户、状态、分组标识聚合，取每组的最小日期作为区间开始日期，最大日期作为区间结束日期

具体SQL实现（支持MySQL8.0+、PostgreSQL、Hive等所有支持窗口函数的数据库） #

WITH t1 AS (
    -- 第一步：分别生成单用户全量排序编号、单用户同状态下的排序编号
    SELECT 
        user,
        status,
        date,
        ROW_NUMBER() OVER(PARTITION BY user ORDER BY date) AS rn1,
        ROW_NUMBER() OVER(PARTITION BY user, status ORDER BY date) AS rn2
    FROM 你的表名
),
t2 AS (
    -- 第二步：生成连续状态的分组标识，同一段连续相同状态的rn1 - rn2差值固定
    SELECT 
        user,
        status,
        date,
        rn1 - rn2 AS grp
    FROM t1
)
-- 第三步：分组聚合得到每个状态段的起止日期
SELECT 
    user,
    status,
    MIN(date) AS `start date`,
    MAX(date) AS `end date`
FROM t2
GROUP BY user, status, grp
ORDER BY user, `start date`;

逻辑说明 #

用两个排序编号的差值作为分组标识是该场景的通用解法：

• 当同一个用户的状态没有发生变化时，rn1和rn2都是逐行+1，差值保持不变
• 当状态发生变化时，同状态排序编号rn2的计数会中断，差值就会发生变化，自动生成新的分组

执行上述SQL后输出结果和你期望的结果完全一致。

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