实现TEST_FORWARD表零值/空值记录的后续非零日期映射

需求说明 #

已有表TEST_FORWARD，需将数据转换后插入到TEST_FORWARD_MODIFIED表，新增列CUST_MOD_DATE的规则：

• 若CUST_VALUE为0或null，CUST_MOD_DATE取该行之后第一个非零/非空值对应的CUST_DATE
• 若CUST_VALUE非零且非空，CUST_MOD_DATE等于当前行的CUST_DATE

实现SQL #

使用Oracle窗口函数FIRST_VALUE结合IGNORE NULLS特性，可高效实现需求：

INSERT INTO SCPOMGR.TEST_FORWARD_MODIFIED (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
SELECT
    CUST_DATE,
    CUST_VALUE,
    -- 标记非零/非空值的日期，其余为null；取当前行及后续行中第一个非null值
    FIRST_VALUE(CASE 
                  WHEN CUST_VALUE IS NOT NULL AND CUST_VALUE != 0 THEN CUST_DATE 
                END) OVER (
                    ORDER BY CUST_DATE ASC 
                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                ) IGNORE NULLS AS CUST_MOD_DATE
FROM TEST_FORWARD
ORDER BY CUST_DATE;

COMMIT;

逻辑解释 #

1. CASE语句：仅保留CUST_VALUE非零且非空的行的CUST_DATE，其余行设为null
2. 窗口函数范围：ORDER BY CUST_DATE ASC确保按日期顺序处理，ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING指定窗口包含当前行及之后所有行
3. FIRST_VALUE + IGNORE NULLS：跳过窗口内的null值，取第一个有效日期，即当前行之后的第一个非零/非空值对应的日期；对于本身就是非零的行，CASE语句返回自身日期，因此直接取当前日期

验证结果 #

执行上述SQL后，TEST_FORWARD_MODIFIED表的数据将完全符合预期要求。

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