SQL實現(xiàn)時間序列錯位還原案列
一、需求描述
1 原表T1某條記錄(記做r1,相鄰下一條為r2)的下一行記錄的STARTDATE小于上一行ENDDATE,針對這樣的記錄做轉換即:
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 #輸出結果 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里的最后一條記錄,為后面的矯正做準備。
new_Date preENDDATE id 2021-05-24 2021-05-03 1
3) normal 取出原始數(shù)據(jù)里不會出現(xiàn)時間疊加的記錄,為后面的矯正做準備。
當前演示數(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對應值,更新對應ENDDATE)。
當前示例結果集為空,即無需要修正。
6) T2關聯(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代碼
當前演示版本是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 構建臨時結果集以生成時間序列。
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,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 時間序列關聯(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 查看結果
STARTDATE ENDDATE NUM
2021-04-202021-04-30200
2021-05-012021-05-03300
2021-05-042021-05-24100
執(zhí)行結果:
到此這篇關于時間序列錯位還原之SQL實現(xiàn)案例詳解的文章就介紹到這了,更多相關SQL時間錯位與還原生成案例內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站!
版權聲明:本站文章來源標注為YINGSOO的內(nèi)容版權均為本站所有,歡迎引用、轉載,請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像,否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學習參考,不代表本站立場,如有內(nèi)容涉嫌侵權,請聯(lián)系alex-e#qq.com處理。