如何在ClickHouse中将时间范围行拆分为秒级连续行

可以通过窗口函数+数组展开的方式实现需求，具体SQL如下：

WITH
  -- 替换成你的实际表名
  raw_data AS (
    SELECT uuid, toDateTime(time) AS time, counter FROM your_table
  ),
  -- 为每组uuid的行按时间排序，获取下一行的时间和counter值
  grouped_data AS (
    SELECT
      uuid,
      time,
      counter,
      LEAD(time) OVER (PARTITION BY uuid ORDER BY time) AS next_time,
      LEAD(counter) OVER (PARTITION BY uuid ORDER BY time) AS next_counter
    FROM raw_data
  )
SELECT
  uuid,
  -- 生成从当前时间到下一行时间的所有秒级时间戳并展开成行
  toDateTime(arrayJoin(range(toUnixTimestamp(time), toUnixTimestamp(next_time) + 1))) AS time,
  -- 计算均分后的counter值
  (next_counter - counter) / (toUnixTimestamp(next_time) - toUnixTimestamp(time) + 1) AS counter
FROM grouped_data
-- 过滤掉每组的最后一行（没有下一行可关联）
WHERE next_time IS NOT NULL
ORDER BY uuid, time;

关键逻辑说明： #

1. 窗口函数LEAD：按uuid分组、时间排序后，获取当前行的下一行时间和counter值，以此确定每个时间区间的起止点。
2. range+arrayJoin：将时间区间转换为秒级时间戳数组，再通过arrayJoin把数组的每个元素拆分为单独行，实现秒级连续行的生成。
3. counter均分计算：用上下行的counter差值除以时间区间的总秒数（包含起止时间，所以是时间差+1），得到每行的均分counter。

如果你的time字段是字符串格式（如示例中的13:13:13），需要先通过toDateTime转换为时间类型；如果已经是DateTime/DateTime64类型，直接使用即可。

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