SQL关联缺失日期并填充历史累计值技术咨询

问题说明 #

现有一张项目累计指标表，仅在有记录的日期存储累计值，缺失日期需要填充最近的历史累计值。已确认可以通过关联日期表获取完整日期范围，需要实现缺失值的填充逻辑。

原表数据：

Project      Date       Cumulative
Project A    10/1       5
Project A    10/2       6
Project A    10/4       7
Project A    10/7       8
Project A    10/8       9

期望结果：

Project      Date       Cumulative
Project A    10/1       5
Project A    10/2       6
Project A    10/3       6
Project A    10/4       7
Project A    10/5       7
Project A    10/6       7
Project A    10/7       8
Project A    10/8       9

核心思路 #

1. 生成完整日期范围：通过日期表（date_dim）与项目表做交叉连接，得到每个项目的所有目标日期。
2. 左连接原表：将完整日期范围与原累计表左连接，保留所有日期，缺失记录的Cumulative字段为NULL。
3. 填充NULL值：使用窗口函数或关联查询，将每个NULL值替换为当前日期之前最近的非空累计值。

分数据库实现 #

MySQL 8.0+（支持IGNORE NULLS） #

利用LAST_VALUE窗口函数结合IGNORE NULLS，直接取当前行之前最近的非空累计值：

SELECT
    d.Project,
    d.Date,
    LAST_VALUE(p.Cumulative IGNORE NULLS) OVER (
        PARTITION BY d.Project 
        ORDER BY d.Date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Cumulative
FROM (
    -- 生成每个项目的完整日期范围，需调整日期区间
    SELECT DISTINCT pc.Project, dd.Date
    FROM date_dim dd
    CROSS JOIN (SELECT DISTINCT Project FROM project_cumulative) pc
    WHERE dd.Date BETWEEN '2023-10-01' AND '2023-10-08'
) d
LEFT JOIN project_cumulative p
    ON d.Project = p.Project 
    AND d.Date = p.Date
ORDER BY d.Project, d.Date;

MySQL 8.0以下版本（无IGNORE NULLS支持） #

使用用户变量逐行填充，确保累计值继承最近的非空值：

SELECT
    Project,
    Date,
    @cumulative := CASE 
        WHEN Cumulative IS NOT NULL THEN Cumulative 
        ELSE @cumulative 
    END AS Cumulative
FROM (
    SELECT
        d.Project,
        d.Date,
        p.Cumulative
    FROM (
        SELECT DISTINCT pc.Project, dd.Date
        FROM date_dim dd
        CROSS JOIN (SELECT DISTINCT Project FROM project_cumulative) pc
        WHERE dd.Date BETWEEN '2023-10-01' AND '2023-10-08'
    ) d
    LEFT JOIN project_cumulative p
        ON d.Project = p.Project 
        AND d.Date = p.Date
    ORDER BY d.Project, d.Date
) t
CROSS JOIN (SELECT @cumulative := NULL) init
ORDER BY Project, Date;

PostgreSQL #

PostgreSQL原生支持LAST_VALUE的IGNORE NULLS参数，实现逻辑简洁：

SELECT
    d.Project,
    d.Date,
    LAST_VALUE(p.Cumulative) OVER (
        PARTITION BY d.Project 
        ORDER BY d.Date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS Cumulative
FROM (
    SELECT DISTINCT pc.Project, dd.Date
    FROM date_dim dd
    CROSS JOIN (SELECT DISTINCT Project FROM project_cumulative) pc
    WHERE dd.Date BETWEEN '2023-10-01' AND '2023-10-08'
) d
LEFT JOIN project_cumulative p
    ON d.Project = p.Project 
    AND d.Date = p.Date
ORDER BY d.Project, d.Date;

SQL Server 2022+ #

支持LAST_VALUE的IGNORE NULLS，写法类似PostgreSQL：

SELECT
    d.Project,
    d.Date,
    LAST_VALUE(p.Cumulative) OVER (
        PARTITION BY d.Project 
        ORDER BY d.Date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS Cumulative
FROM (
    SELECT DISTINCT pc.Project, dd.Date
    FROM date_dim dd
    CROSS JOIN (SELECT DISTINCT Project FROM project_cumulative) pc
    WHERE dd.Date BETWEEN '2023-10-01' AND '2023-10-08'
) d
LEFT JOIN project_cumulative p
    ON d.Project = p.Project 
    AND d.Date = p.Date
ORDER BY d.Project, d.Date;

SQL Server 2022以下版本 #

使用OUTER APPLY关联查询，取当前日期之前最新的累计值：

SELECT
    d.Project,
    d.Date,
    p.Cumulative
FROM (
    SELECT DISTINCT pc.Project, dd.Date
    FROM date_dim dd
    CROSS JOIN (SELECT DISTINCT Project FROM project_cumulative) pc
    WHERE dd.Date BETWEEN '2023-10-01' AND '2023-10-08'
) d
OUTER APPLY (
    SELECT TOP 1 Cumulative
    FROM project_cumulative p
    WHERE p.Project = d.Project 
      AND p.Date <= d.Date
    ORDER BY p.Date DESC
) p
ORDER BY d.Project, d.Date;

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