Oracle根据连续性日期的重复数据取最大或最小值日期

淩亂°似流年 2023-08-17 16:14 122阅读 0赞

原始数据：

![1042744-20190924124421843-1172673013.png][]

结果数据：

![1042744-20190924124513407-1487703978.png][]

对比两个图，要是不处理连续性中的重复值，我们直接可以用LEAD函数了事，但处理出来的结果貌似多余。

我的思路是先将原始数据中连续性日期有重复值的处理好，即选择最小的一个，比如2011/01/01和2012/01/01的值是一样，日期又是连续的，所以要去除2012/01/01，保留2011/01/01,让处理出来的截止日期为第4行的日期-1即2012/12/30.

以下是实现代码，有更好更方便的欢迎提出来，共同学习。

/\*CREATE TABLE TB(PB\_DATE DATE,SAVE\_RATE VARCHAR2(10),LEN\_RATE VARCHAR2(10));  
INSERT INTO TB SELECT DATE'2010-01-01','8%','10%' FROM DUAL;  
INSERT INTO TB SELECT DATE'2011-01-01','5%','7%' FROM DUAL;  
INSERT INTO TB SELECT DATE'2012-01-01','5%','7%' FROM DUAL;  
INSERT INTO TB SELECT DATE'2012-10-31','5%','6%' FROM DUAL;  
INSERT INTO TB SELECT DATE'2013-01-01','5%','6%' FROM DUAL;  
INSERT INTO TB SELECT DATE'2013-03-31','8%','9%' FROM DUAL;  
INSERT INTO TB SELECT DATE'2013-09-01','8%','10%' FROM DUAL;  
INSERT INTO TB SELECT DATE'2014-01-01','8%','9%' FROM DUAL;  
INSERT INTO TB SELECT DATE'2015-01-01','6%','9%' FROM DUAL;  
SELECT MIN(PB\_DATE)START\_DATE,MAX(END\_DATE)END\_DATE,SAVE\_RATE,LEN\_RATE FROM(  
SELECT PB\_DATE,LEAD(PB\_DATE,1,DATE'9999-12-31')OVER(ORDER BY PB\_DATE)-1 AS END\_DATE,SAVE\_RATE,LEN\_RATE FROM TB)  
GROUP BY SAVE\_RATE,LEN\_RATE;  
SELECT T.\*,MAX(PB\_DATE)KEEP(DENSE\_RANK LAST ORDER BY SAVE\_RATE,LEN\_RATE)OVER(PARTITION BY SAVE\_RATE,LEN\_RATE)AS RN FROM TB T ORDER BY 1 ;  
SELECT \* FROM TB;\*/  
/\* Formatted on 2019/9/24 12:29:14 (QP5 v5.227.12220.39754) \*/  
/\* Formatted on 2019/9/24 12:41:16 (QP5 v5.227.12220.39754) \*/

实现代码，以上代码是造数脚本：  
WITH TMP  
AS ( SELECT PB\_DATE,  
SAVE\_RATE,  
LEN\_RATE,  
LEAD (PB\_DATE, 1, DATE '9999-12-31') OVER (ORDER BY PB\_DATE)  
NEXT\_DATE,  
LAG (PB\_DATE, 1, PB\_DATE) OVER (ORDER BY PB\_DATE) PRE\_DATE,  
LEAD (SAVE\_RATE) OVER (ORDER BY PB\_DATE) NEXT\_SAVE,  
LAG (SAVE\_RATE) OVER (ORDER BY PB\_DATE) PRE\_SAVE,  
LEAD (LEN\_RATE) OVER (ORDER BY PB\_DATE) NEXT\_LEN,  
LAG (LEN\_RATE) OVER (ORDER BY PB\_DATE) PRE\_LEN  
FROM TB  
ORDER BY PB\_DATE, SAVE\_RATE, LEN\_RATE),  
TMP2  
AS ( SELECT MAX (T.PB\_DATE) AS PB\_DATE, T.SAVE\_RATE, T.LEN\_RATE  
FROM TMP T  
WHERE (CASE  
WHEN (SAVE\_RATE = NEXT\_SAVE AND LEN\_RATE = NEXT\_LEN)  
OR (SAVE\_RATE = PRE\_SAVE AND LEN\_RATE = PRE\_LEN)  
THEN  
1  
ELSE  
0  
END) = 1  
GROUP BY T.SAVE\_RATE, T.LEN\_RATE  
ORDER BY 1),  
TMP3  
AS (SELECT PB\_DATE，SAVE\_RATE,LEN\_RATE  
FROM TB T  
WHERE NOT EXISTS  
(SELECT NULL  
FROM TMP2 R  
WHERE R.SAVE\_RATE = T.SAVE\_RATE  
AND R.LEN\_RATE = T.LEN\_RATE  
AND R.PB\_DATE = T.PB\_DATE))  
SELECT PB\_DATE AS START\_DATE,  
LEAD (PB\_DATE, 1, DATE '9999-12-31') OVER (ORDER BY PB\_DATE) - 1  
AS END\_DATE,  
SAVE\_RATE,  
LEN\_RATE  
FROM TMP3;

转载于:https://www.cnblogs.com/guipeng/p/11577728.html

[1042744-20190924124421843-1172673013.png]: /images/20230810/25244040728346b793d812b296e9fdce.png
[1042744-20190924124513407-1487703978.png]: /images/20230810/0eb2eb210e374988be13bd12062b91ef.png