在ClickHouse中合并连续发生的相同事件

假设你的表名为event_log，包含time（事件时间）和event（事件类型）字段，要合并连续出现的相同事件并保留任意时间，可以通过窗口函数+分组聚合实现，以下是两种可行的SQL方案：

方案一：通过lag()生成分组标记 #

SELECT
    min(time) AS time, -- 可替换为max(time)/first_value(time)，保留组内任意时间即可
    event
FROM (
    SELECT
        time,
        event,
        -- 当当前事件与上一行不同时累加1，生成连续事件的分组ID
        sum(CASE WHEN event = lag(event) OVER (ORDER BY time) THEN 0 ELSE 1 END) OVER (ORDER BY time) AS group_id
    FROM event_log
)
GROUP BY group_id, event
ORDER BY time;

方案二：通过行号差值生成分组ID #

SELECT
    min(time) AS time,
    event
FROM (
    SELECT
        time,
        event,
        -- 全局行号减去事件分组内的行号，连续相同事件的差值一致
        row_number() OVER (ORDER BY time) - row_number() OVER (PARTITION BY event ORDER BY time) AS group_id
    FROM event_log
)
GROUP BY group_id, event
ORDER BY time;

原理说明 #

两种方案核心都是给连续相同的事件分配唯一分组ID，再按分组聚合：

• 方案一通过lag()获取上一行事件，判断是否变化来生成分组标记，累加后得到分组ID；
• 方案二利用全局行号与事件内部分组行号的差值，连续相同事件的差值固定，以此作为分组依据。

聚合时使用min()/max()等函数即可保留组内任意时间，最终结果会按时间排序输出符合需求的合并记录。

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