SQL to implement time series dislocation restoration case

1. Requirements description
2. Overview of ideas

1. Demand extension
2. Overview of ideas

3. SQL code

1. Requirements description

1 The STARTDATE of the next row of records in a record of the original table T1 (remembered as r1, the next row is r2) is less than the ENDDATE of the previous row. For such records, the conversion is performed as follows:

STARTDATE of r1 remains unchanged, ENDDATE為r1 STARTDATE-1

r2 's STARTDATE is r1's ENDDATE , ENDDATE為r1 's ENDDATE

2 If there is no "time overlap" in adjacent rows of the original table T1 ( that is, the definition of 1), keep the original data unchanged.

 # Text version#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
 
 
# Output result 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

2. Overview of ideas

1. Demand extension

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

Here, the 4th record is superimposed on the 2nd and 3rd records.

2. Overview of ideas

1) T0 is the time series generated by the up and down functions

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 retrieves the last record in T0 to prepare for subsequent correction.

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

3) Normal extracts the records without time overlap in the original data, in preparation for subsequent correction.
There is no record of current demonstration data, but the code with comments can emerge.

4) T_Serial unified the definition of STARTDATE and ENDDATE, and revised T0 for the first time.

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 corrects the records that do not overlap in time (delete the corresponding value of T0 and update the corresponding ENDDATE).
The current example result set is empty, which means no correction is required.

6) T2 is associated with T1 (original table) and the final value is obtained after aggregation.

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

3. SQL code

The current demo version is MySQL 8.0.23. SQL Server and Oracle that support CTE and window functions need to modify the syntax of Order by and ADDDATE .
Step 0 Create a table and initialize data

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

Step 1 Build a temporary result set to generate a time series.

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) -- You can add comments to verify that only two records in the original table are taken.
  SELECT DISTINCT ID,ENDDATE new_DATE FROM test_ShenLiang2025
   WHERE seq in (1,2) -- You can add comments to verify that only 2 records in the original table are currently taken 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: Associate the time series with the original table to generate the NUM field.

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

Step 4 View the results

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

Execution Result:

This concludes this article on the detailed explanation of the SQL implementation case of time series misalignment restoration. For more relevant SQL time misalignment and restoration generation case content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

Description of the default transaction isolation level of mysql and oracle
Summary of commonly used multi-table modification statements in Mysql and Oracle
A brief discussion on the differences between the three major databases: Mysql, SqlServer, and Oracle
Detailed explanation of paging query methods in mysql, mssql and oracle

<<: HTML uses the title attribute to display text when the mouse hovers

>>: Add a floating prompt for the header icon in the ElementUI table