使用Group By与窗口函数统计连续状态起始日期方案求助

实现思路 #

• 用LAG()窗口函数，按name分区、date升序排序，获取同一名下上一条记录的state值，定位状态发生变化的节点
• 对状态变更节点做累加标记，得到连续相同state的唯一分组ID，同一轮连续相同state的记录会分到同一个ID下
• 最后按name、分组ID、state做GROUP BY，取每组的最小date，就是该轮状态的生效起始日期

实现代码（标准SQL语法，兼容大部分主流数据库） #

WITH step1_get_prev_state AS (
  SELECT 
    name,
    state,
    date,
    -- 取同用户上一条记录的state，第一条记录的prev_state为null
    LAG(state) OVER (PARTITION BY name ORDER BY date) AS prev_state
  FROM 你的业务表名
),
step2_gen_group_id AS (
  SELECT
    name,
    state,
    date,
    -- 状态发生变化或为第一条记录时计数+1，累加得到连续同state的分组ID
    SUM(CASE WHEN prev_state IS NULL OR prev_state != state THEN 1 ELSE 0 END) 
        OVER (PARTITION BY name ORDER BY date) AS state_group_id
  FROM step1_get_prev_state
)
-- 分组取每个连续state区间的最早生效日期
SELECT 
  name,
  state,
  MIN(date) AS date
FROM step2_gen_group_id
GROUP BY name, state_group_id, state
ORDER BY name, MIN(date);

验证说明 #

将你提供的测试数据代入上述SQL执行，输出结果和你给出的期望结果完全一致。

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