Presto 无需递归CTE补全缺失时间戳并填充对应数值字段的方案

核心逻辑 #

无需递归CTE，通过Presto内置的序列生成函数+窗口函数即可实现需求：

1. 统计目标表的时间上下界，生成指定粒度的连续时间序列
2. 连续时间序列左关联原表，得到包含空值的全量时间记录
3. 用窗口函数取每条空值记录前后最近的非空NumericField，判断是否相等后填充对应值

可直接运行的SQL示例 #

假设原表名为original_table，时间粒度为1分钟，代码如下：

WITH 
-- 统计时间范围上下界
time_bound AS (
    SELECT 
        MIN("Timestamp") AS min_ts,
        MAX("Timestamp") AS max_ts
    FROM original_table
),
-- 生成1分钟粒度的连续时间序列
full_time_series AS (
    SELECT 
        ts AS "Timestamp"
    FROM time_bound
    CROSS JOIN UNNEST(sequence(min_ts, max_ts, INTERVAL '1' MINUTE)) AS t(ts)
),
-- 关联原表得到带空值的全量数据
joined_data AS (
    SELECT 
        f."Timestamp",
        o.NumericField
    FROM full_time_series f
    LEFT JOIN original_table o ON f."Timestamp" = o."Timestamp"
),
-- 计算每条记录前后最近的非空值
prep_fill AS (
    SELECT
        "Timestamp",
        NumericField,
        -- 往前取最近的非空NumericField
        LAST_VALUE(NumericField) IGNORE NULLS OVER (ORDER BY "Timestamp" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_not_null_val,
        -- 往后取最近的非空NumericField
        FIRST_VALUE(NumericField) IGNORE NULLS OVER (ORDER BY "Timestamp" ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_not_null_val
    FROM joined_data
)
-- 按规则填充字段值
SELECT
    "Timestamp",
    CASE
        WHEN NumericField IS NOT NULL THEN NumericField
        WHEN prev_not_null_val = next_not_null_val THEN prev_not_null_val
        ELSE NULL
    END AS NumericField
FROM prep_fill
ORDER BY "Timestamp" DESC

扩展提示：如果需要按维度分组补全（比如按设备、区域分别补全各自的时间序列），只需要在time_bound的聚合逻辑、以及两个窗口函数中添加PARTITION BY 分组字段即可。

效果验证 #

• 缺失行前后非空NumericField值均为101时，补全记录的字段值为101，匹配第一个示例要求
• 缺失行前值为100、后值为101时，两者不等，补全记录的字段值为NULL，匹配第二个示例要求

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