ClickHouse：如何计算指定时间区间内各状态的总持续时长

针对仅记录状态变更的表，要计算指定时间区间内各状态的总持续时长，需包含区间开始前的最后状态，可通过以下步骤实现：

完整查询语句 #

WITH
  toDateTime('2023-12-01 10:00:00') AS start_ts,
  toDateTime('2023-12-01 10:05:00') AS end_ts
SELECT
  name,
  val,
  concat(toString(sum(duration)), ' seconds') AS duration
FROM (
  SELECT
    name,
    val,
    toUInt32(least(next_ts, end_ts) - greatest(ts, start_ts)) AS duration
  FROM (
    SELECT
      name,
      ts,
      val,
      lead(ts, 1, end_ts) OVER (PARTITION BY name ORDER BY ts) AS next_ts
    FROM (
      -- 取区间内的变更记录 + 区间前的最后一条状态记录
      SELECT name, ts, val FROM ts_vals WHERE ts BETWEEN start_ts AND end_ts
      UNION ALL
      SELECT
        name,
        ts,
        val
      FROM (
        SELECT
          name,
          ts,
          val,
          row_number() OVER (PARTITION BY name ORDER BY ts DESC) AS rn
        FROM ts_vals WHERE ts < start_ts
      ) WHERE rn = 1
    )
  )
  WHERE duration > 0
)
GROUP BY name, val
ORDER BY name, val;

逻辑拆解 #

1. 定义查询时间范围：通过WITH子句统一声明起止时间，便于后续修改。
2. 获取完整状态序列：
   • 先取出时间区间内的所有状态变更记录；
   • 再为每个设备获取区间开始前的最后一条状态记录（用row_number()按时间倒序取第一条）；
   • 用UNION ALL合并两部分数据，确保每个设备的初始状态被纳入统计。
3. 计算状态结束时间：用lead()窗口函数，按设备分组、时间排序，获取当前状态的下一次变更时间；若没有后续变更，则用查询结束时间填充。
4. 计算有效持续时长：当前状态的有效时长取「当前记录时间与区间开始时间的较大值」到「下一次变更时间与区间结束时间的较小值」的差值，避免超出查询范围。
5. 过滤无效数据：剔除持续时长为0的记录（如变更时间刚好等于区间结束时间）。
6. 汇总结果：按设备和状态分组求和，将时长格式化为要求的文本形式。

执行上述语句后，即可得到与示例一致的统计结果。

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