博客

原始数据：

结果数据：

对比两个图，要是不处理连续性中的重复值，我们直接可以用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;