Oracle中如何补全无数据日期的前一日对应数据？

实现思路 #

通过全量日期表与原始数据表左连接获取所有日期的记录，再利用Oracle的LAST_VALUE()窗口函数（配合IGNORE NULLS参数），向前填充最近的非空产品名称和数量数据，实现缺失日期的数值补全。

假设表名 #

• 原始数据表：product_data（包含DATE、SECURITY_SEALS、NUM字段，之前的LAG预处理无需保留，直接用原始表即可）
• 全量日期表：date_range（包含DATE字段，存储指定时间段的所有日期）

完整SQL代码 #

WITH joined_data AS (
    SELECT
        dr.date AS full_date,
        pd.security_seals,
        pd.num
    FROM date_range dr
    LEFT JOIN product_data pd
        ON TRUNC(dr.date) = TRUNC(pd.date)
)
SELECT
    full_date AS "Date",
    LAST_VALUE(security_seals IGNORE NULLS) OVER (
        ORDER BY full_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS "SECURITY_SEALS",
    LAST_VALUE(num IGNORE NULLS) OVER (
        ORDER BY full_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS "NUM"
FROM joined_data
ORDER BY full_date;

代码解释 #

1. CTE joined_data：

   • 左连接全量日期表和原始数据表，确保所有日期都被保留；
   • 使用TRUNC()函数截断日期的时间部分，避免因时分秒不一致导致连接失败。

2. 窗口函数LAST_VALUE()：

   • IGNORE NULLS：指定忽略空值，只取最近的非空值进行填充；
   • ORDER BY full_date：按日期排序，保证填充顺序正确；
   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：窗口范围从结果集的第一行到当前行，确保每次都取到当前行之前最近的有效数据。

测试结果 #

执行上述SQL后，会生成你期望的结果：

Date            SECURITY_SEALS   NUM     
23/09/2024      SEAL_XXX         133    
24/09/2024      SEAL_XXX         133    
25/09/2024      SEAL_XXX         148    

内容的提问来源于stack exchange，提问作者Adrian Lopez Martinez