使用SQL Group By计算键值对的生效起止日期（含延迟历史数据）

需求说明 #

现有存储键值对快照的表equipments_staging，包含equipment、item、value、timestamp字段，需为每组连续相同的equipment+item+value组合计算生效起止时间：

• 当value发生变化时，当前组合的生效期结束，新组合生成独立记录
• 相同value间隔重现时，需作为独立分组，不可合并

测试表结构与数据 #

create table equipments_staging
(
    equipment string,
    item string,
    value string,
    timestamp timestamp
);

insert into equipments_staging
values 
('M','X','1','2023-11-10 13:00'),
('M','X','2','2023-11-11 13:00'),
('M','X','2','2023-11-12 13:00'),
('M','X','1','2023-11-13 13:00'),
('M','X','1','2023-11-14 13:00'),
('M','X','1','2023-11-15 13:00'),
('M','X','2','2023-11-16 13:00'),
('M','X','2','2023-11-17 13:00'),
('M','X','1','2023-11-18 13:00');

解决方案SQL #

核心思路是通过分组标识区分连续相同value的区间：先标记value发生变化的行，再累加标记得到分组ID，最后按分组聚合计算起止时间。

WITH grouped_data AS (
    SELECT 
        equipment,
        item,
        value,
        timestamp,
        -- 标记当前行与上一行value是否不同，不同则记为1
        CASE 
            WHEN LAG(value) OVER (PARTITION BY equipment, item ORDER BY timestamp) != value 
            THEN 1 
            ELSE 0 
        END AS value_change_flag,
        -- 累加标记得到分组ID，连续相同value的行将拥有相同ID
        SUM(CASE 
            WHEN LAG(value) OVER (PARTITION BY equipment, item ORDER BY timestamp) != value 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY equipment, item ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM equipments_staging
),
interval_data AS (
    SELECT 
        equipment,
        item,
        value,
        MIN(timestamp) AS EffectiveFrom,
        -- 获取下一个分组的起始时间作为当前分组的结束时间
        LEAD(MIN(timestamp)) OVER (PARTITION BY equipment, item ORDER BY MIN(timestamp)) AS EffectiveTo
    FROM grouped_data
    GROUP BY equipment, item, value, group_id
)
SELECT 
    equipment,
    item,
    value,
    EffectiveFrom,
    -- 最后一组的结束时间设为指定最大值，也可改为NULL
    COALESCE(EffectiveTo, '9999-12-31 23:59:59') AS EffectiveTo
FROM interval_data
ORDER BY equipment, item, EffectiveFrom;

预期输出 #

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