PostgreSQL用后续非空值填充前置空值的技术实现问询

问题背景 #

数据库包含两张表：

• 计划服务表（scheduled services）：仅存计划日期，无金额字段
• 已完成服务表（services realized）：包含服务收入金额与完成日期

部分计划服务因延期重排，导致完成日期与计划日期不一致。为统计每日计划服务金额，创建视图vw_test通过LEFT JOIN关联两张表，关联条件为CONCAT(t1.date, t1.service) = CONCAT(t2.date, t2.service)，但结果出现大量Null值。

需求：按服务分组，用后续非空金额填充前置Null，末尾Null保留。尝试过ARRAY_AGG和JSONB_AGG方法未成功，询问PostgreSQL是否支持该需求。

实现方案 #

PostgreSQL完全可以实现这个需求，核心利用窗口函数处理分组内的Null填充逻辑，以下是两种可行方案：

方案1：使用LAST_VALUE（PostgreSQL 13+） #

PostgreSQL 13及以上版本支持LAST_VALUE函数的IGNORE NULLS选项，能直接跳过Null值取后续最近的非空金额：

SELECT
    service,
    plan_date,
    LAST_VALUE(amount) OVER (
        PARTITION BY service
        ORDER BY plan_date
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        IGNORE NULLS
    ) AS filled_amount
FROM vw_test
ORDER BY service, plan_date;

• PARTITION BY service：按服务独立分组处理，避免跨服务填充
• ORDER BY plan_date：按计划日期排序，确保后续值是时间上靠后的有效金额
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：窗口范围覆盖当前行到分组内最后一行
• IGNORE NULLS：让函数跳过Null值，直接定位到最近的非空金额

方案2：兼容低版本的替代方案（PostgreSQL <13） #

如果你的PostgreSQL版本低于13，可通过COALESCE结合MAX窗口函数实现：

SELECT
    service,
    plan_date,
    COALESCE(
        amount,
        MAX(amount) OVER (
            PARTITION BY service
            ORDER BY plan_date
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        )
    ) AS filled_amount
FROM vw_test
ORDER BY service, plan_date;

逻辑说明：当前行金额非空则直接使用，否则取当前行之后所有行的最大金额（因后续非空金额为有效数值，无后续非空值时MAX返回Null，符合末尾Null保留的要求）。

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