如何在SQL中基于上月值填充连续空值？

输入表 #

注：表中-代表空值（NULL）

期望输出 #

我曾尝试使用SQL中的LAG()函数，但仅能填充紧邻的空值（如上例中的2月），而3月的空值仍未被填充。

解决方案 #

核心思路 #

先给每个非空值的行标记分组ID，让连续空值与最近的非空值归为同一分组，再在分组内提取非空值完成填充。

不同SQL方言的实现 #

1. PostgreSQL / SQL Server / Oracle

通过SUM()窗口函数生成分组ID，结合聚合函数提取分组内非空值：

WITH grouped_data AS (
    SELECT 
        月份,
        数值,
        -- 非空值行分配递增分组ID，空值继承前一行分组ID
        SUM(CASE WHEN 数值 IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY 月份) AS group_id
    FROM your_table
)
SELECT 
    月份,
    MAX(数值) OVER (PARTITION BY group_id) AS 数值
FROM grouped_data
ORDER BY 月份;

2. MySQL 8.0+

MySQL 8.0及以上支持窗口函数，写法与上文类似：

WITH grouped_data AS (
    SELECT 
        月份,
        数值,
        SUM(IF(数值 IS NOT NULL, 1, 0)) OVER (ORDER BY 月份) AS group_id
    FROM your_table
)
SELECT 
    月份,
    MAX(数值) OVER (PARTITION BY group_id) AS 数值
FROM grouped_data
ORDER BY 月份;

3. 支持IGNORE NULLS的数据库（PostgreSQL 11+、Oracle）

直接用LAST_VALUE函数简化实现：

SELECT 
    月份,
    LAST_VALUE(数值) OVER (
        ORDER BY 月份 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS 数值
FROM your_table;

说明 #

• 第一种方法兼容性最强，几乎所有支持窗口函数的数据库均可使用
• SUM()生成分组ID的逻辑：每遇到非空值则分组ID+1，空值保持前一行ID，确保连续空值与最近非空值同组
• 用MAX()/MIN()提取数值是因为分组内仅存在一个非空值，聚合后会直接返回该值

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