SQL日志数据合并连续重复行及去重的实现问题

需求：将SQL存储的日志数据中连续重复的行合并为单行，新行需包含该连续重复块的StartTime、EndTime及重复次数NumOccur，同时区分行内容变化的节点。

示例输入数据 #

期望结果 #

当前错误结果 #

尝试过的SQL语句 #

GROUP BY 方式（未达预期） #

SELECT MIN(TimeStamp) AS StartTime, MIN(TimeStamp) AS EndTime, Column A, Column B, Column C, Column D, Column E, count(*)
FROM table
GROUP BY Column A, Column B, Column C, Column D, Column E
HAVING COUNT(*) > 1

窗口函数方式（效果有限） #

SELECT * FROM (
    SELECT MIN(TimeStamp) AS StartTime, MIN(TimeStamp) AS EndTime, Column A, Column B, Column C, Column D, Column E,
    ROW_NUMBER () OVER(Partition by Column A, Column B, Column C, Column D, Column E ORDER BY TimeStamp) RowNum
    FROM table
) d

正确实现方法 #

核心思路是通过窗口函数标记连续重复的分组，再按分组聚合。以下方案适用于支持窗口函数的SQL数据库（MySQL 8.0+、PostgreSQL、SQL Server等）：

方案1：通过内容变化标记分组 #

WITH grouped_logs AS (
    SELECT 
        TimeStamp,
        `Column A`, `Column B`, `Column C`, `Column D`, `Column E`,
        -- 标记当前行与上一行内容是否不同，不同则生成新分组
        SUM(CASE 
            WHEN LAG(CONCAT(`Column A`, `Column B`, `Column C`, `Column D`, `Column E`)) OVER(ORDER BY TimeStamp) 
                = CONCAT(`Column A`, `Column B`, `Column C`, `Column D`, `Column E`) 
            THEN 0 
            ELSE 1 
        END) OVER(ORDER BY TimeStamp) AS group_id
    FROM your_table_name -- 替换为实际表名
)
SELECT 
    MIN(TimeStamp) AS `开始时间（StartTime）`,
    MAX(TimeStamp) AS `结束时间（EndTime）`,
    `Column A`, `Column B`, `Column C`, `Column D`, `Column E`,
    COUNT(*) AS `重复次数（NumOccur）`
FROM grouped_logs
GROUP BY group_id, `Column A`, `Column B`, `Column C`, `Column D`, `Column E`
ORDER BY `开始时间（StartTime）`;

方案2：通过行号差生成分组（更简洁） #

WITH numbered_logs AS (
    SELECT 
        TimeStamp,
        `Column A`, `Column B`, `Column C`, `Column D`, `Column E`,
        ROW_NUMBER() OVER(ORDER BY TimeStamp) AS row_num,
        ROW_NUMBER() OVER(PARTITION BY `Column A`, `Column B`, `Column C`, `Column D`, `Column E` ORDER BY TimeStamp) AS group_row_num
    FROM your_table_name
),
grouped_logs AS (
    SELECT 
        TimeStamp,
        `Column A`, `Column B`, `Column C`, `Column D`, `Column E`,
        row_num - group_row_num AS group_id
    FROM numbered_logs
)
SELECT 
    MIN(TimeStamp) AS `开始时间（StartTime）`,
    MAX(TimeStamp) AS `结束时间（EndTime）`,
    `Column A`, `Column B`, `Column C`, `Column D`, `Column E`,
    COUNT(*) AS `重复次数（NumOccur）`
FROM grouped_logs
GROUP BY group_id, `Column A`, `Column B`, `Column C`, `Column D`, `Column E`
ORDER BY `开始时间（StartTime）`;

说明 #

• 方案1通过LAG函数对比当前行与上一行的内容，用累计求和生成连续重复块的唯一ID。
• 方案2通过计算全局行号与分组内行号的差值，相同差值对应同一个连续重复块，逻辑更高效。

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