将日期区间拆分为多行

sql_1">将二维表格中的开始时间结束间用sql拆分成多行连续的时间

源数据

start_date	end_date
2023-12-01	2023-12-03

结果

biz_date	start_date	end_date
2023-01-01	2023-12-01	2023-12-03
2023-01-02	2023-12-01	2023-12-03
2023-01-03	2023-12-01	2023-12-03

代码

sql">SELECT  *
FROM    (SELECT  a.*,b.pos,b.val,DATE_ADD(a.start_date, b.pos -1) AS biz_dateFROM    (SELECT  start_date,end_date,datediff(end_date, start_date) AS day_numFROM    (SELECT  '2023-12-01' AS start_date,'2023-12-01' AS end_dateUNION ALLSELECT  '2023-12-01' AS start_date,'2023-12-02' AS end_dateUNION ALLSELECT  '2023-12-01' AS start_date,'2023-12-03' AS end_date)) a LATERAL VIEW posexplode(split(space(day_num), '')) b AS pos,val) t
WHERE   biz_date<=end_date