如何聚合SQL表中因bug生成的同参数连续有效时间记录？

嘿，这个场景我太熟悉了！因为开发环境没测出来的bug导致生成了一堆冗余的连续时间段记录，现在需要把同一entity_static_id下参数完全一致、时间连续的记录合并成一条对吧？

下面是一个适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库解决方案：

WITH grouped_records AS (
    SELECT
        *,
        -- 生成分组ID：当参数变化或时间不连续时，开启新分组
        SUM(CASE
            WHEN prev_vals_match = 1 AND time_continuous = 1 THEN 0
            ELSE 1
        END) OVER (PARTITION BY entity_static_id ORDER BY valid_from) AS group_id
    FROM (
        SELECT
            *,
            -- 判断前一条记录的参数是否和当前完全一致（这里要把所有需要判断的字段都加上）
            CASE WHEN LAG(val1) OVER w = val1 AND LAG(val2) OVER w = val2 THEN 1 ELSE 0 END AS prev_vals_match,
            -- 判断时间是否连续：前一条的valid_to刚好是当前valid_from的前一秒
            CASE WHEN LAG(valid_to) OVER w = DATE_SUB(valid_from, INTERVAL 1 SECOND) THEN 1 ELSE 0 END AS time_continuous
        FROM entity_dynamic
        WINDOW w AS (PARTITION BY entity_static_id ORDER BY valid_from)
    ) AS sub
)
SELECT
    MIN(id) AS id, -- 取分组里最早的记录ID作为合并后的ID，可按需调整
    entity_static_id,
    val1,
    val2,
    MIN(valid_from) AS valid_from, -- 合并后的起始时间取最早的
    -- 处理valid_to：如果分组里有NULL（表示当前仍有效），则保留NULL，否则取最晚的结束时间
    CASE 
        WHEN MAX(CASE WHEN valid_to IS NULL THEN 1 ELSE 0 END) = 1 THEN NULL 
        ELSE MAX(valid_to) 
    END AS valid_to
    -- 其他需要保留的字段，因为分组内参数都相同，直接MIN/MAX或者取任意值都可以
FROM grouped_records
GROUP BY entity_static_id, val1, val2, group_id
ORDER BY entity_static_id, valid_from;

关键逻辑解释： #

1. 窗口函数分组：用LAG()函数获取同一entity_static_id下前一条记录的参数和结束时间，判断是否和当前记录参数一致且时间连续。
2. 生成分组ID：通过SUM() OVER()累计分组标记，每当参数变化或时间断开时，分组ID加1，这样连续相同的记录会被归为同一组。
3. 聚合合并：按分组ID聚合，取最早的起始时间、最晚的结束时间（处理NULL的情况），保留唯一的参数值。

注意事项： #

• 如果还有其他需要判断是否相同的字段，一定要在prev_vals_match的CASE条件里加上（比如AND LAG(val3) OVER w = val3）。
• 不同数据库的时间函数略有差异：
  • PostgreSQL用valid_from - INTERVAL '1 second'替代DATE_SUB
  • SQL Server用DATEADD(second, -1, valid_from)
• 如果valid_to为NULL的记录是当前有效的状态，合并后保留NULL是合理的，代表这个状态持续到现在。

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