SQL实现时间序列错位还原案列

一、需求描述

1 原表T1某条记录(记做r1，相邻下一条为r2)的下一行记录的STARTDATE小于上一行ENDDATE，针对这样的记录做转换即：

r1的STARTDATE保持不变，ENDDATE为r1的STARTDATE-1

r2的STARTDATE为r1的ENDDATE，ENDDATE为r1的ENDDATE

2 如果原表T1不存在相邻行“时间重叠”（即为1的定义）时保持原有数据不变。

 # 文本版#T1seq id  startdate   enddate     num1 1 2021-04-20 2021-05-03 2002 1 2021-05-01 2021-05-24 1003 1 2021-05-18 2021-05-31 694 1 2021-05-20 2021-07-31 345 1 2021-08-05 2021-08-25 456 1 2021-08-15 2021-09-25 65  #输出结果ID STARTDATE    ENDDATE     NUM1  2021-04-20 2021-04-30 2001  2021-05-01 2021-05-02 3001  2021-05-03 2021-05-17 1001  2021-05-18 2021-05-19 1691  2021-05-20 2021-05-23 2031  2021-05-24 2021-05-30 1031  2021-05-31 2021-07-30 341  2021-08-05 2021-08-14 451  2021-08-15 2021-08-25 1101  2021-08-26 2021-09-25 65

2 思路概述

1) T0 通过上下行函数生成的时间序列

id      new_DATE        nextSTARTDATE   preEndDATE     rn      1 2021-05-24          2021-05-03 11 2021-05-03 2021-05-24 2021-05-01 21 2021-05-01 2021-05-03 2021-04-20 31 2021-04-20 2021-05-01          4

new_Date        preENDDATE      id2021-05-24 2021-05-03 1

4）T_Serial 统一定义STARTDATE、ENDDATE，首次修正T0。

id      STARTDATE       ENDDATE1 2021-04-20 2021-04-301 2021-05-01 2021-05-031 2021-05-04 2021-05-24

6) T2关联T1(原始表)，汇总后取得最终值

STARTDATE   ENDDATE     NUM2021-04-20 2021-04-30 2002021-05-01 2021-05-03 3002021-05-04 2021-05-24 100

DROP TABLE IF EXISTS test_ShenLiang2025;CREATE TABLE test_ShenLiang2025 (  seq int DEFAULT NULL,  id int DEFAULT NULL,  STARTDATE date DEFAULT NULL,  ENDDATE date DEFAULT NULL,  NUM int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO test_ShenLiang2025 VALUES ('1', '1', '2021-04-20', '2021-05-03', '200');INSERT INTO test_ShenLiang2025 VALUES ('2', '1', '2021-05-01', '2021-05-24', '100');INSERT INTO test_ShenLiang2025 VALUES ('3', '1', '2021-05-18', '2021-05-31', '69');INSERT INTO test_ShenLiang2025 VALUES ('4', '1', '2021-05-20', '2021-07-31', '34');INSERT INTO test_ShenLiang2025 VALUES ('5', '1', '2021-08-05', '2021-08-25', '45');INSERT INTO test_ShenLiang2025 VALUES ('6', '1', '2021-08-15', '2021-09-25', '65');

WITH T0 AS(SELECT id,    new_DATE,   LEAD(NEW_DATE,1) OVER (PARTITION BY ID ORDER BY NEW_DATE ) nextSTARTDATE,   LAG(NEW_DATE,1) OVER (PARTITION BY ID ORDER BY NEW_DATE ) preENDDATE,   ROW_NUMBER()OVER(PARTITION BY ID ORDER BY new_DATE DESC) rn   FROM  (  SELECT DISTINCT ID,STARTDATE new_DATE  FROM test_ShenLiang2025       WHERE seq in (1,2) -- 可加注释验证，当前仅取原表里2条记录  UNION  SELECT DISTINCT ID,ENDDATE new_DATE FROM test_ShenLiang2025   WHERE seq in (1,2) -- 可加注释验证，当前仅取原表里2条记录      ORDER BY new_DATE   )A),last AS( SELECT new_DATE,preENDDATE,idFROM T0 WHERE nextSTARTDATE IS NULL),normal AS( SELECT * FROM ( SELECT id,     ENDDATE,    LEAD(STARTDATE,1) OVER (PARTITION BY ID ORDER BY ENDDATE ) nextSTARTDATE,    LAG(ENDDATE,1) OVER (PARTITION BY ID ORDER BY ENDDATE ) preENDDATE    FROM test_ShenLiang2025 )A WHERE ENDDATE > preENDDATE AND ENDDATE < nextSTARTDATE),T_Serial AS ( SELECT ID,ADDDATE(preENDDATE, INTERVAL 1 DAY ) STARTDATE,new_DATE ENDDATEFROM last  UNION SELECT bottom_2.ID,bottom_2.new_DATE STARTDATE,CASE WHEN rn =3 THEN bottom_2.nextSTARTDATE  ELSE ADDDATE(bottom_2.nextSTARTDATE, INTERVAL -1 DAY ) END ENDDATEFROM last JOIN T0 bottom_2ON bottom_2.nextSTARTDATE<=last.preENDDATE AND bottom_2.id = last.id),T2 AS(SELECT B.ID,B.STARTDATE,B.ENDDATE FROM  (   SELECT A.*,ROW_NUMBER()OVER(PARTITION BY ID,STARTDATE ORDER BY ENDDATE) rn   FROM   (   SELECT A.ID,A.STARTDATE,A.ENDDATE   FROM T_Serial A   LEFT JOIN normal B   ON A.STARTDATE = B.ENDDATE AND A.ID = B.ID   WHERE B.ENDDATE IS NULL    UNION        SELECT A.ID,A.STARTDATE,B.ENDDATE      FROM T_Serial A   INNER JOIN normal B   ON ADDDATE(A.ENDDATE, INTERVAL 1 DAY ) = B.ENDDATE AND A.ID = B.ID       )A  )B WHERE rn =1)

SELECT T2.STARTDATE,T2.ENDDATE,SUM(T1.NUM) TOTAL FROM T2JOIN test_ShenLiang2025 T1ON T2.STARTDATE>=T1.STARTDATE  AND T2.ENDDATE<=T1.ENDDATEGROUP BY T2.STARTDATE,T2.ENDDATEORDER BY T2.STARTDATE

STARTDATE ENDDATE NUM
2021-04-202021-04-30200
2021-05-012021-05-03300
2021-05-042021-05-24100

执行结果：

到此这篇关于时间序列错位还原之SQL实现案例详解的文章就介绍到这了,更多相关SQL时间错位与还原生成案例内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

一、需求描述

2 思路概述

更多相关文章

随机推荐