ClickHouse如何实现重采样后带长度限制的缺失值前向填充

完全可以在ClickHouse中实现该需求，核心通过时间序列补全+窗口函数控制前向填充阈值实现，具体逻辑和代码如下：

实现步骤 #

1. 先完成30秒粒度重采样，规避原生WITH FILL默认给非时间字段填0的问题，单独生成完整的30秒时间轴，固定equipment_id、sensor_id维度
2. 对非空的数值点打分组标记，计算每个缺口的连续长度
3. 仅对缺口长度≤3的行做前向填充，超过长度的保留null，同时维度字段直接继承上一行有效值

完整代码 #

WITH
  -- 可配置参数
  @start_time = '2021-02-05 09:18:00'::DateTime, -- 查询起始时间
  @end_time = '2021-02-05 09:28:00'::DateTime, -- 查询结束时间
  @resample_step = 30, -- 重采样粒度，单位秒
  @max_fill_rows = 3 -- 最多前向填充行数，3行对应90秒缺口
AS
SELECT
  equipment_id,
  sensor_id,
  datetime,
  original_value,
  -- 仅缺口长度小于等于阈值时才用前向值，否则保留null
  IF(gap_length <= @max_fill_rows, last_valid_value, null) AS desired_value
FROM (
  SELECT
    -- 维度字段前向填充，继承上一行有效值
    any(equipment_id) OVER (ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS equipment_id,
    any(sensor_id) OVER (ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sensor_id,
    datetime,
    original_value,
    -- 取当前分组内最近的有效值
    any(original_value) OVER (PARTITION BY value_group ORDER BY datetime) AS last_valid_value,
    -- 计算当前行距离上一个有效值的缺口长度
    rowNumberInBlock() OVER (PARTITION BY value_group ORDER BY datetime) -1 AS gap_length
  FROM (
    SELECT
      t1.datetime,
      t2.equipment_id,
      t2.sensor_id,
      t2.value AS original_value,
      -- 给每个有效值段打分组标记，后续窗口函数按该分组处理填充逻辑
      SUM(IF(t2.value IS NOT NULL, 1, 0)) OVER (ORDER BY t1.datetime) AS value_group
    FROM (
      -- 第一步：生成完整的30秒时间轴
      SELECT
        toDateTime(@start_time) + number * @resample_step AS datetime
      FROM numbers(dateDiff('second', @start_time, @end_time) / @resample_step)
    ) t1
    LEFT JOIN (
      -- 第二步：原始数据30秒重采样
      SELECT
        equipment_id,
        sensor_id,
        toStartOfInterval(datetime, INTERVAL @resample_step SECOND) AS datetime,
        avg(value) AS value
      FROM table_name
      WHERE
        datetime >= @start_time
        AND datetime <= @end_time
        AND sensor_id = 456
        AND equipment_id = 123
      GROUP BY equipment_id, sensor_id, datetime
    ) t2 ON t1.datetime = t2.datetime
  )
)
ORDER BY datetime

逻辑说明 #

• 单独生成时间轴的逻辑，规避了原WITH FILL语法自动给非时间字段填充0的问题
• 通过value_group分组将每个有效值和后续的连续缺口划分为同一组，可精准计算每个缺口的长度
• 填充阈值可通过@max_fill_rows参数灵活调整，适配不同的业务要求

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