SQL時間錯位與還原生成案例

需求描述

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

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

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

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

# 文本版 #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#輸出結果 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 通過上下行函數生成的時間序列 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 42) last 取出T0里的最后一條記錄，為后面的矯正做準備。 new_Date preENDDATE id 2021-05-24 2021-05-03 13) normal 取出原始數據里不會出現時間疊加的記錄，為后面的矯正做準備。 當前演示數據無記錄，代碼加注釋可浮現。4）T_Serial 統一定義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-245) T2 對時間沒有重疊的記錄進行修正(刪除T0對應值，更新對應ENDDATE)。 當前示例結果集為空，即無需要修正。6) T2關聯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代碼

-- 當前演示版本是Mysql 8.0.23，支持CTE、窗口函數的SQL Server、Oracle需要修改Order by和ADDDATE處語法。 -- Step0 創建表并初始化數據 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 構建臨時結果集以生成時間序列。 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) rnFROM(SELECT DISTINCT ID,STARTDATE new_DATE FROM test_ShenLiang2025 WHERE seq in (1,2) -- 可加注釋驗證，當前僅取原表里2條記錄UNIONSELECT DISTINCT ID,ENDDATE new_DATE FROM test_ShenLiang2025WHERE seq in (1,2) -- 可加注釋驗證，當前僅取原表里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 ) preENDDATEFROM test_ShenLiang2025)AWHERE ENDDATE > preENDDATE AND ENDDATE < nextSTARTDATE ),T_Serial AS (SELECT ID,ADDDATE(preENDDATE, INTERVAL 1 DAY ) STARTDATE, new_DATE ENDDATE FROM last UNIONSELECT 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) rnFROM(SELECT A.ID,A.STARTDATE,A.ENDDATEFROM T_Serial ALEFT JOIN normal BON A.STARTDATE = B.ENDDATE AND A.ID = B.IDWHERE B.ENDDATE IS NULLUNION SELECT A.ID,A.STARTDATE,B.ENDDATE FROM T_Serial AINNER JOIN normal BON ADDDATE(A.ENDDATE, INTERVAL 1 DAY ) = B.ENDDATE AND A.ID = B.ID )A)B WHERE rn =1 )-- Step2 時間序列關聯原表生成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 查看結果 /* 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实现案例详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。