Clickhouse中按状态首次变化分组数据的技术问询

AI 小北

2026-8-10

需求背景

给定如下数据：

┌─id────────────┬──────────created_at─┬─state─┐
│ 1234567890123 │ 2022-11-26 22:58:28 │     0 │
│ 1234567890123 │ 2022-11-26 22:57:00 │     0 │
│ 1234567890123 │ 2022-11-26 22:50:38 │     0 │
│ 1234567890123 │ 2022-11-26 22:41:46 │     0 │
│ 1234567890123 │ 2022-11-26 22:37:08 │     0 │
│ 1234567890123 │ 2022-11-26 22:28:09 │     0 │
│ 1234567890123 │ 2022-11-26 22:28:09 │     0 │
│ 1234567890123 │ 2022-11-26 22:25:13 │     0 │
│ 1234567890123 │ 2022-11-26 22:21:25 │     0 │
│ 1234567890123 │ 2022-11-26 22:15:43 │     0 │
│ 1234567890123 │ 2022-11-26 22:03:41 │     0 │
│ 1234567890123 │ 2022-11-26 21:28:39 │     1 │
│ 1234567890123 │ 2022-11-26 21:28:39 │     1 │
│ 1234567890123 │ 2022-11-26 21:08:03 │     1 │
│ 1234567890123 │ 2022-11-26 21:08:03 │     1 │
│ 1234567890123 │ 2022-11-26 20:03:45 │     1 │
│ 1234567890123 │ 2022-11-26 20:03:45 │     1 │
│ 1234567890123 │ 2022-11-26 20:02:34 │     0 │
│ 1234567890123 │ 2022-11-26 20:00:58 │     0 │
│ 1234567890123 │ 2022-11-26 19:58:26 │     0 │
│ 1234567890123 │ 2022-11-26 19:56:53 │     0 │
│ 1234567890123 │ 2022-11-26 19:55:29 │     0 │
│ 1234567890123 │ 2022-11-26 19:51:41 │     0 │
│ 1234567890123 │ 2022-11-26 19:51:41 │     0 │
│ 1234567890123 │ 2022-11-26 19:26:19 │     1 │
│ 1234567890123 │ 2022-11-26 19:26:19 │     1 │
│ 1234567890123 │ 2022-11-26 16:06:16 │     1 │
│ 1234567890123 │ 2022-11-26 16:06:16 │     1 │
│ 1234567890123 │ 2022-11-26 15:34:28 │     0 │
│ 1234567890123 │ 2022-11-26 15:27:46 │     0 │
└───────────────┴─────────────────────┴───────┘

需要将数据按如下规则分组：将首次出现state=1（true）的created_at与后续首次出现state=0（false）的created_at关联，最终期望结果为：

┌─id────────────┬───────────────start─┬─────────────────end─┐
│ 1234567890123 │ 2022-11-26 16:06:16 │ 2022-11-26 19:51:41 │
│ 1234567890123 │ 2022-11-26 20:03:45 │ 2022-11-26 22:03:41 │
└───────────────┴─────────────────────┴─────────────────────┘

为此，需先将数据过滤为如下形式，再通过LEAD/LAG窗口函数进行分组：

┌─id────────────┬──────────created_at─┬─state─┐
│ 1234567890123 │ 2022-11-26 22:03:41 │     0 │
│ 1234567890123 │ 2022-11-26 20:03:45 │     1 │
│ 1234567890123 │ 2022-11-26 19:51:41 │     0 │
│ 1234567890123 │ 2022-11-26 16:06:16 │     1 │
└───────────────┴─────────────────────┴───────┘

遇到的问题

尝试了多种LEAD/LAG、RANK函数组合，但无法匹配每个事件的首次出现（首次state=1，后续首次state=0）。以下是最接近的查询语句，但结果不符合预期：

WITH states AS (
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:58:28') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:57:00') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:50:38') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:41:46') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:37:08') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:28:09') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:28:09') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:25:13') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:21:25') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:15:43') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 22:03:41') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 21:28:39') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 21:28:39') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 21:08:03') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 21:08:03') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 20:03:45') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 20:03:45') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 20:02:34') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 20:00:58') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 19:58:26') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 19:56:53') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 19:55:29') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 19:51:41') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 19:51:41') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 19:26:19') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 19:26:19') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 16:06:16') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 16:06:16') AS created_at, 1 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 15:34:28') AS created_at, 0 AS state UNION ALL
  SELECT '1234567890123' AS id, toDateTime('2022-11-26 15:27:46') AS created_at, 0 AS state
)
SELECT
  id,
  created_at,
  state,
  next.1 AS next_created_at,
  next.2 AS next_state
FROM (
  SELECT
    id,
    created_at,
    state,
    any((created_at, state)) OVER (PARTITION BY id ORDER BY created_at ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next
  FROM states
  ORDER BY created_at DESC
)
WHERE
  state = 1
  AND next_state = 0

该查询的结果为：

┌─id────────────┬──────────created_at─┬─state─┬─────next_created_at─┬─next_state─┐
│ 1234567890123 │ 2022-11-26 21:28:39 │     1 │ 2022-11-26 22:03:41 │          0 │
│ 1234567890123 │ 2022-11-26 19:26:19 │     1 │ 2022-11-26 19:51:41 │          0 │
└───────────────┴─────────────────────┴───────┴─────────────────────┴────────────┘

现寻求正确的实现方法，以达成预期的数据分组效果。

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