如何基于DATE_1按规则填充DATE_2列并保留最大日期？

我需要在表中生成DATE_2列，逻辑如下：

• 第一条记录的DATE_2直接取DATE_1的'25-JUN-18'
• 后续每条记录：如果当前行DATE_1大于上一行的DATE_2，就取当前DATE_1；否则取上一行DATE_2，最终始终保留截至当前行的最大日期

当前错误结果 #

rn   DATE_1        DATE_2
1    25-JUN-18     25-JUN-18
2    25-JUN-18     NULL
3    25-JUN-18     NULL
4    09-JAN-19     NULL
5    02-SEP-20     NULL
6    01-DEC-23     NULL
7    01-DEC-23     NULL

预期结果 #

rn   DATE_1      DATE_2
1  25-JUN-18    25-JUN-18
2  25-JUN-18    25-JUN-18
3  25-JUN-18    25-JUN-18
4  09-JAN-19    09-JAN-19
5  02-SEP-20    02-SEP-20 
6  01-DEC-23    01-DEC-23
7  01-DEC-23    01-DEC-23

最终期望输出（仅DATE_2列） #

25-Jun-18
25-Jun-18
25-Jun-18
9-Jan-19
2-Sep-20
1-Dec-23
1-Dec-23
1-Dec-23
1-Dec-23
1-Dec-23
1-Dec-23

主表创建语句 #

CREATE TABLE tbl22
(MAXDATE, RN) AS 
 SELECT '25-JUN-18',  1 FROM DUAL UNION ALL
 SELECT '25-JUN-18',  2 FROM DUAL UNION ALL
 SELECT '25-JUN-18',  3 FROM DUAL UNION ALL
 SELECT '09-JAN-19',  4 FROM DUAL UNION ALL
 SELECT '02-SEP-20',  5 FROM DUAL UNION ALL
 SELECT '01-DEC-23',  6 FROM DUAL UNION ALL
 SELECT '01-DEC-23',  7 FROM DUAL UNION ALL
 SELECT '02-SEP-20',  8 FROM DUAL UNION ALL
 SELECT '01-DEC-23',  9 FROM DUAL UNION ALL
 SELECT '02-SEP-20',  10 FROM DUAL UNION ALL
 SELECT '01-DEC-23',  11 FROM DUAL 

我尝试过用LEAD和LAG函数，但没能得到想要的结果。

解决方案 #

你需要的是累计最大值，用Oracle的分析函数MAX() OVER()就能实现，无需LEAD/LAG。因为需求本质是保留从第一行到当前行的最大日期，直接按RN排序计算累计最大值即可：

基础查询（日期类型输出） #

SELECT 
    RN,
    MAXDATE AS DATE_1,
    MAX(TO_DATE(MAXDATE, 'DD-MON-RR')) OVER(ORDER BY RN ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DATE_2
FROM tbl22
ORDER BY RN;

格式化日期输出（匹配期望格式） #

如果需要将DATE_2格式化为DD-Mon-RR样式，添加TO_CHAR()转换：

SELECT 
    RN,
    MAXDATE AS DATE_1,
    TO_CHAR(MAX(TO_DATE(MAXDATE, 'DD-MON-RR')) OVER(ORDER BY RN ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 'DD-Mon-RR') AS DATE_2
FROM tbl22
ORDER BY RN;

逻辑说明 #

1. TO_DATE(MAXDATE, 'DD-MON-RR')：将字符串类型的日期转换为Oracle日期类型，确保日期比较逻辑准确
2. MAX() OVER(ORDER BY RN ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)：计算从第一行到当前行的最大日期，完全匹配需求：
   • 第一行的最大值就是自身DATE_1
   • 后续每行自动继承截至当前的最大日期，无需手动判断上一行值

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