SQL實現時間序列錯位還原案列
更新時間：2021年09月16日 17:19:25 作者：ShenLiang2025
這篇文章小編主要向大家介紹的是時間序列錯位還原之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 4
2) last 取出T0里的最后一條記錄，為后面的矯正做準備。
new_Date preENDDATE id 2021-05-24 2021-05-03 1
3) normal 取出原始數據里不會出現時間疊加的記錄，為后面的矯正做準備。
當前演示數據無記錄，代碼加注釋可浮現。
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關聯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 創(chuàng)建表并初始化數據
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 時間序列關聯原表生成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
執(zhí)行結果：
到此這篇關于時間序列錯位還原之SQL實現案例詳解的文章就介紹到這了,更多相關SQL時間錯位與還原生成案例內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
如何解決MYSQL8.4.1 MySQL84--ERROR 1524(HY000):Plugin&n
這篇文章主要介紹了如何解決MYSQL8.4.1 MySQL84--ERROR 1524(HY000):Plugin ‘msql_native_password‘ is not loaded問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08
union 是對數據進行并集操作，不包括重復行，同時進行默認排序而Union all 是對數據進行并集操作，包括重復行，不進行排序，下面給大家詳細介紹mysql union和union all，感興趣的朋友一起看看吧2017-10-10
MySQL當中的限制條件可以使用on或者where,兩者在不同的情況下具有不同而意義,這篇文章主要給大家介紹了關于Mysql中where與on的區(qū)別及何時使用的相關資料,需要的朋友可以參考下2021-08-08
這篇文章主要為大家詳細介紹了Ubuntu 18.04 安裝mysql 5.7的相關資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-02-02
本文帶領大家通過5分鐘了解MySQL5.7中union all用法的黑科技,需要的朋友可以參考下2017-04-04
這篇文章主要為大家詳細介紹了MySQL數據庫中表常用的一些操作方法，文中的示例代碼講解詳細， 對我們學習MySQL有一定幫助，需要的可以參考一下2022-09-09
這篇文章主要為大家詳細介紹了Ubuntu下MySQL安裝及配置遠程登錄教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-10-10
這篇文章主要介紹了的相關資料,需要的朋友可以參考下2015-10-10
MySQL 在安裝時會自動創(chuàng)建一個名為 mysql 的數據庫，mysql 數據庫中存儲的都是用戶權限表。本文就詳細的介紹一下MySQL user權限表 ，感興趣的可以了解一下2021-06-06
這篇文章主要介紹了MySQL索引失效的幾種情況，幫助大家更好的理解和使用MySQL索引，感興趣的朋友可以了解下2020-09-09