如何在ClickHouse中按时间顺序合并相同值行并获取状态变更的起止时间

刚好之前处理过类似的需求，这在ClickHouse里属于连续相同值的分组聚合问题，核心是把时间顺序下连续的相同状态归为一组，再提取每组的起止时间，给你两种可行的解法：

方法一：通用窗口函数实现 #

这是跨SQL引擎都适用的思路，通过标记状态变更点来生成分组ID，再聚合计算起止时间：

WITH input_data AS (
    -- 模拟你的输入数据
    SELECT * FROM VALUES
    ('status' Int32, 'time' DateTime),
    (1, '2020-11-08 01:00:01'),
    (1, '2020-11-08 01:00:02'),
    (2, '2020-11-08 01:00:03'),
    (2, '2020-11-08 01:00:04'),
    (2, '2020-11-08 01:00:05'),
    (2, '2020-11-08 01:00:06'),
    (1, '2020-11-08 01:00:07'),
    (1, '2020-11-08 01:00:08')
)
SELECT
    status,
    MIN(time) AS start_time,
    MAX(time) AS end_time
FROM (
    SELECT
        status,
        time,
        -- 累加状态变更次数，生成连续相同状态的分组ID
        SUM(CASE WHEN status != prev_status THEN 1 ELSE 0 END) OVER (ORDER BY time) AS group_id
    FROM (
        SELECT
            status,
            time,
            -- 获取前一行的状态，用于判断是否发生变更
            LAG(status) OVER (ORDER BY time) AS prev_status
        FROM input_data
    ) t1
) t2
GROUP BY group_id, status
ORDER BY start_time;

步骤解释： #

1. 内层查询用LAG(status)获取当前行的上一行状态，判断状态是否发生变化；
2. 中间层用累加函数SUM() OVER (ORDER BY time)，每次状态变更时加1，这样连续相同的状态会被分配同一个group_id；
3. 最后按group_id和status分组，取每组的最小时间作为开始时间，最大时间作为结束时间，再按开始时间排序。

方法二：用ClickHouse专属的sessionWindow函数简化 #

ClickHouse提供了sessionWindow函数，专门用于处理这种“连续相同/满足条件则归为同一会话”的场景，写法更简洁：

WITH input_data AS (
    SELECT * FROM VALUES
    ('status' Int32, 'time' DateTime),
    (1, '2020-11-08 01:00:01'),
    (1, '2020-11-08 01:00:02'),
    (2, '2020-11-08 01:00:03'),
    (2, '2020-11-08 01:00:04'),
    (2, '2020-11-08 01:00:05'),
    (2, '2020-11-08 01:00:06'),
    (1, '2020-11-08 01:00:07'),
    (1, '2020-11-08 01:00:08')
)
SELECT
    status,
    MIN(time) AS start_time,
    MAX(time) AS end_time
FROM input_data
-- 会话窗口：当当前状态不等于前一个状态时，结束当前会话，开启新会话
GROUP BY sessionWindow(time, 0, status = prev(status)), status
ORDER BY start_time;

参数解释： #

• sessionWindow(time, 0, status = prev(status))：
  • 第一个参数time：排序用的时间列；
  • 第二个参数0：会话超时时间（设为0表示只要状态变化就立即结束会话）；
  • 第三个参数status = prev(status)：会话持续的条件，当该条件不成立时（即状态变化），会话结束。

最终输出结果 #

两种方法运行后都会得到你期望的结果：

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