如何在Presto中用上一季度数据补全缺失季度的交付预测数据

我在Presto中编写累计交付预测的SQL查询，但部分季度没有交付记录（无对应数据行），希望用上一季度的数据填充这些缺失的季度。我尝试过生成包含所有目标季度的CTE，用窗口函数补全缺失数据，也曾想用CASE语句判断null时取上一季度值，但CASE语句未生效。

我尝试的SQL代码 #

with forecasts as (
    select quarter, type, location, sum(total)
    from delivery_db
    group by 1,2,3
)

quarters AS (
    SELECT
        *
    FROM (
        VALUES
            ('2024-Q1'),
            ('2024-Q2'),
            ('2024-Q3'),
            ('2024-Q4')
    ) v(quarter)
)

SELECT
    q.quarter,
    f.type,
    f.location,
    --CASE(WHEN f.quarter is not null then SUM(total) else LAG(f.quarter, 1))
    SUM(total) OVER(
        PARTITION BY
            f.type
        ORDER BY
            q.quarter
    ) AS total
FROM quarters q
FULL JOIN forecasts f
on f.quarter = q.quarter

DeliveryDB表原始数据 #

当前查询结果 #

期望查询结果 #

解决方案 #

问题核心在于原查询的FULL JOIN导致缺失季度的type和location为null，窗口函数无法正确分区；同时未生成完整的维度-季度组合，导致填充逻辑失效。以下是修正后的SQL：

WITH forecasts AS (
    SELECT quarter, type, location, SUM(total) AS total
    FROM delivery_db
    GROUP BY 1, 2, 3
),
-- 生成所有目标季度
quarters AS (
    SELECT * FROM (VALUES ('2024-Q1'), ('2024-Q2'), ('2024-Q3'), ('2024-Q4')) v(quarter)
),
-- 生成每个type+location组合与所有季度的笛卡尔积，确保维度完整
all_combinations AS (
    SELECT q.quarter, f.type, f.location
    FROM quarters q
    CROSS JOIN (SELECT DISTINCT type, location FROM forecasts) f
),
-- 关联原始聚合数据，保留所有季度维度
combined AS (
    SELECT 
        ac.quarter,
        ac.type,
        ac.location,
        f.total
    FROM all_combinations ac
    LEFT JOIN forecasts f 
        ON ac.quarter = f.quarter 
        AND ac.type = f.type 
        AND ac.location = f.location
)
-- 用LAST_VALUE填充缺失的total值
SELECT
    quarter,
    type,
    location,
    LAST_VALUE(total IGNORE NULLS) OVER (
        PARTITION BY type, location 
        ORDER BY quarter 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total
FROM combined
ORDER BY quarter;

关键逻辑说明 #

1. 生成完整维度-季度组合：用CROSS JOIN将所有季度与type+location组合配对，避免缺失季度的维度字段为null。
2. LEFT JOIN关联数据：确保每个季度都被保留，无交付记录的季度显示为null。
3. LAST_VALUE填充null：LAST_VALUE(total IGNORE NULLS)会自动取当前行之前最近的非null值，实现用上一季度数据填充的需求。
4. 分区与排序：按type和location分区、按季度排序，保证填充逻辑在正确的维度范围内生效。

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