Oracle 19c如何用最近非空值填充缺失日期的Amt字段？

假设你的表名为your_table，可以通过以下方案实现需求：

核心思路 #

1. 生成原表最小日期到最大日期的连续日期序列
2. 用左连接关联原表数据，保留所有日期行
3. 通过窗口函数取最近的非空Amt值填充缺失行

场景1：Date字段为DATE类型

WITH date_range AS (
    -- 生成连续日期范围
    SELECT TRUNC(MIN("Date")) + LEVEL - 1 AS dt
    FROM your_table
    CONNECT BY TRUNC(MIN("Date")) + LEVEL - 1 <= TRUNC(MAX("Date"))
),
original_data AS (
    -- 预处理原表日期，截断时间部分避免匹配异常
    SELECT TRUNC("Date") AS dt, Amt
    FROM your_table
)
SELECT 
    TO_CHAR(d.dt, 'DD/MM/YYYY') AS "Date",
    -- 按日期排序，取最近的非空Amt值填充
    LAST_VALUE(o.Amt IGNORE NULLS) OVER (ORDER BY d.dt) AS Amt
FROM date_range d
LEFT JOIN original_data o ON d.dt = o.dt
ORDER BY d.dt;

场景2：Date字段为字符串类型

如果Date是字符串格式（如示例中的02/04/2023），需先转换为DATE类型再处理：

WITH date_range AS (
    SELECT TRUNC(MIN(TO_DATE("Date", 'DD/MM/YYYY'))) + LEVEL - 1 AS dt
    FROM your_table
    CONNECT BY TRUNC(MIN(TO_DATE("Date", 'DD/MM/YYYY'))) + LEVEL - 1 <= TRUNC(MAX(TO_DATE("Date", 'DD/MM/YYYY')))
),
original_data AS (
    SELECT TRUNC(TO_DATE("Date", 'DD/MM/YYYY')) AS dt, Amt
    FROM your_table
)
SELECT 
    TO_CHAR(d.dt, 'DD/MM/YYYY') AS "Date",
    LAST_VALUE(o.Amt IGNORE NULLS) OVER (ORDER BY d.dt) AS Amt
FROM date_range d
LEFT JOIN original_data o ON d.dt = o.dt
ORDER BY d.dt;

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