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