如何查询用户状态变更的间隔时长及中间步骤数

我需要查询一份用户状态变更数据集，获取状态变更所需的间隔天数以及中间步骤数（行数）。

示例数据 #

期望输出 #

解决方案（SQL） #

通过窗口函数和聚合操作实现需求，以下是标准SQL代码：

WITH status_groups AS (
    SELECT 
        user_id,
        Status,
        date,
        -- 标记连续相同状态的分组
        SUM(CASE WHEN prev_status != Status THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY date) AS group_id
    FROM (
        SELECT 
            user_id,
            Status,
            date,
            LAG(Status) OVER (PARTITION BY user_id ORDER BY date) AS prev_status
        FROM your_table
    ) t
),
group_agg AS (
    SELECT 
        user_id,
        Status AS current_status,
        MIN(date) AS start_date,
        MAX(date) AS end_date,
        COUNT(*) AS step_count
    FROM status_groups
    GROUP BY user_id, group_id, Status
)
SELECT 
    user_id,
    current_status AS `From`,
    next_status AS `to`,
    DATEDIFF(next_start_date, end_date) AS 间隔天数,
    step_count AS 中间步骤数
FROM (
    SELECT 
        user_id,
        current_status,
        step_count,
        end_date,
        LEAD(current_status) OVER (PARTITION BY user_id ORDER BY start_date) AS next_status,
        LEAD(start_date) OVER (PARTITION BY user_id ORDER BY start_date) AS next_start_date
    FROM group_agg
) t
WHERE next_status IS NOT NULL -- 过滤无后续状态的记录
ORDER BY user_id, start_date;

逻辑说明 #

1. 状态分组：用LAG获取每条记录的前一个状态，通过累加标记生成连续相同状态的分组ID，把同一状态的连续记录归为一组。
2. 分组聚合：按用户和分组ID聚合，得到每个状态阶段的起止日期、该阶段的记录行数（即中间步骤数）。
3. 关联计算：用LEAD获取下一个状态阶段的信息，计算两个阶段之间的间隔天数（下阶段开始日期减去当前阶段结束日期），最终筛选出有状态变更的记录。

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