如何用Timescale time_bucket_gapfill()设置最大范围，不填充真实缺失值？

错误原因 #

你遇到的ERROR: multiple interpolate/locf function calls per resultset column not supported错误，是因为Timescale不允许在同一个结果列中多次调用locf()（或interpolate()）函数。你的原查询在CASE语句的同一列里两次调用了locf()，触发了这个限制。

解决方案 #

通过子查询先一次性计算出填充后的值和最近已知数据的时间，再在外部查询中判断时间差是否超过1小时，决定是否保留填充值。

修正后的查询 #

SELECT
    point_five,
    CASE
        -- 判断当前时间桶与最近已知数据的时间差是否超过1小时
        WHEN point_five - last_known_time > INTERVAL '1 hour' THEN NULL
        ELSE filled_value
    END AS gapfilled_value
FROM (
    SELECT
        time_bucket_gapfill('5 minutes', at) AS point_five,
        -- 获取填充后的值
        locf(avg(value)) AS filled_value,
        -- 获取最近已知数据的时间戳
        locf(at) AS last_known_time
    FROM powers
    -- 过滤目标配送点和时间范围，提升查询效率
    WHERE delivery_point_id = 1
      AND at BETWEEN '2021-01-01 00:00:00' AND '2021-01-01 08:00:00'
    GROUP BY point_five
) AS sub_query
ORDER BY point_five;

效果说明 #

• 对于时间差≤1小时的缺口：会用最近的已知值填充，生成5分钟间隔的完整数据。
• 对于2021-01-01 04:00至07:00的3小时缺口：从04:50之后（距离最后一个已知数据03:50超过1小时），所有时间桶的gapfilled_value会返回NULL，标识真实缺失；直到07:00之后出现新的已知数据，才会重新开始填充。

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