SQL實現(xiàn)時間序列錯位還原案列

更新時間：2021年09月16日 17:19:25 作者：ShenLiang2025

這篇文章小編主要向大家介紹的是時間序列錯位還原之SQL實現(xiàn)案例詳解的相關(guān)資料,需要的小伙伴可以參考下面文章的具體內(nèi)容

一、需求描述

1 原表T1某條記錄(記做r1，相鄰下一條為r2)的下一行記錄的STARTDATE小于上一行ENDDATE，針對這樣的記錄做轉(zhuǎn)換即：

r1的STARTDATE保持不變，ENDDATE為r1的STARTDATE-1

r2的STARTDATE為r1的ENDDATE，ENDDATE為r1的ENDDATE

2 如果原表T1不存在相鄰行“時間重疊”（即為1的定義）時保持原有數(shù)據(jù)不變。

 # 文本版
#T1
seq id  startdate   enddate     num
1 1 2021-04-20 2021-05-03 200
2 1 2021-05-01 2021-05-24 100
3 1 2021-05-18 2021-05-31 69
4 1 2021-05-20 2021-07-31 34
5 1 2021-08-05 2021-08-25 45
6 1 2021-08-15 2021-09-25 65
 
 
#輸出結(jié)果
ID STARTDATE    ENDDATE     NUM
1  2021-04-20 2021-04-30 200
1  2021-05-01 2021-05-02 300
1  2021-05-03 2021-05-17 100
1  2021-05-18 2021-05-19 169
1  2021-05-20 2021-05-23 203
1  2021-05-24 2021-05-30 103
1  2021-05-31 2021-07-30 34
1  2021-08-05 2021-08-14 45
1  2021-08-15 2021-08-25 110
1  2021-08-26 2021-09-25 65

二、思路概述

1 需求延展

SEQ     ID      STARTDATE       ENDDATE         NUM
1 1 2021-04-20 2021-05-03 200
2 1 2021-05-01 2021-05-24 100
3 1 2021-05-18 2021-05-31 69
4 1 2021-05-20 2021-07-31 34

這里第4條記錄同時疊加在第2和3條記錄里。

2 思路概述

1) T0 通過上下行函數(shù)生成的時間序列

id      new_DATE        nextSTARTDATE   preEndDATE     rn      
1 2021-05-24          2021-05-03 1
1 2021-05-03 2021-05-24 2021-05-01 2
1 2021-05-01 2021-05-03 2021-04-20 3
1 2021-04-20 2021-05-01          4

2) last 取出T0里的最后一條記錄，為后面的矯正做準(zhǔn)備。

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

3) normal 取出原始數(shù)據(jù)里不會出現(xiàn)時間疊加的記錄，為后面的矯正做準(zhǔn)備。
當(dāng)前演示數(shù)據(jù)無記錄，代碼加注釋可浮現(xiàn)。

4）T_Serial 統(tǒng)一定義STARTDATE、ENDDATE，首次修正T0。

id      STARTDATE       ENDDATE
1 2021-04-20 2021-04-30
1 2021-05-01 2021-05-03
1 2021-05-04 2021-05-24

5) T2 對時間沒有重疊的記錄進行修正(刪除T0對應(yīng)值，更新對應(yīng)ENDDATE)。
當(dāng)前示例結(jié)果集為空，即無需要修正。

6) T2關(guān)聯(lián)T1(原始表)，匯總后取得最終值

STARTDATE   ENDDATE     NUM
2021-04-20 2021-04-30 200
2021-05-01 2021-05-03 300
2021-05-04 2021-05-24 100

三、SQL代碼

當(dāng)前演示版本是Mysql 8.0.23，支持CTE、窗口函數(shù)的SQL Server、Oracle需要修改Order by和ADDDATE處語法。
Step0 創(chuàng)建表并初始化數(shù)據(jù)

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');

Step1 構(gòu)建臨時結(jié)果集以生成時間序列。

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) -- 可加注釋驗證，當(dāng)前僅取原表里2條記錄
  UNION
  SELECT DISTINCT ID,ENDDATE new_DATE FROM test_ShenLiang2025
   WHERE seq in (1,2) -- 可加注釋驗證，當(dāng)前僅取原表里2條記錄
      ORDER BY new_DATE 
  )A
),last AS
( SELECT new_DATE,preENDDATE,id
FROM 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 ENDDATE
FROM 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 ENDDATE
FROM last 
JOIN T0 bottom_2
ON 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
)

Step2 時間序列關(guān)聯(lián)原表生成NUM字段。

SELECT T2.STARTDATE,T2.ENDDATE,SUM(T1.NUM) TOTAL FROM T2
JOIN test_ShenLiang2025 T1
ON T2.STARTDATE>=T1.STARTDATE 
 AND T2.ENDDATE<=T1.ENDDATE
GROUP BY T2.STARTDATE,T2.ENDDATE
ORDER BY T2.STARTDATE

Step4 查看結(jié)果