Presto/SQL中使用前序非空值填充缺失空值的实现方法

你的需求是按产品维度，补全该产品存在的时间区间内缺失的年份，缺失值沿用上一次的非空价值数据，用Presto可以按以下方案实现：

实现思路 #

• 首先计算所有数据的年份上下限，生成连续的全量年份序列
• 统计每个产品的时间区间（从首次出现年份到末次出现年份），和全量年份序列做关联得到每个产品需要补全的所有年份
• 左关联原表得到价值字段，用窗口函数取最近的非空值填充缺失位

完整可运行代码 #

WITH 
-- 步骤1：计算全局年份上下限，生成连续年份序列
year_range AS (
    SELECT 
        MIN(Year) AS min_year,
        MAX(Year) AS max_year
    FROM your_table_name
),
all_years AS (
    SELECT year
    FROM year_range
    CROSS JOIN UNNEST(SEQUENCE(min_year, max_year)) AS t(year)
),
-- 步骤2：统计每个产品的有效时间区间
product_range AS (
    SELECT 
        Product,
        MIN(Year) AS product_start_year,
        MAX(Year) AS product_end_year
    FROM your_table_name
    GROUP BY Product
),
-- 步骤3：生成每个产品对应有效区间内的全量年份行
product_all_years AS (
    SELECT 
        p.Product,
        a.year AS Year
    FROM product_range p
    JOIN all_years a 
        ON a.year >= p.product_start_year AND a.year <= p.product_end_year
)
-- 步骤4：关联原表并填充缺失值
SELECT 
    pay.Year,
    pay.Product,
    LAST_VALUE(t.Value IGNORE NULLS) OVER(
        PARTITION BY pay.Product 
        ORDER BY pay.Year 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Value
FROM product_all_years pay
LEFT JOIN your_table_name t 
    ON pay.Year = t.Year AND pay.Product = t.Product
ORDER BY pay.Product, pay.Year

使用说明 #

• 把代码里的your_table_name替换成你实际的表名即可直接运行，输出结果和你给出的预期完全一致
• 如果你需要的填充逻辑不是沿用上一个非空值，而是固定值（比如0），把窗口函数部分替换成COALESCE(t.Value, 0)即可

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