sql将日期区间拆分为多行

本文主要是介绍sql将日期区间拆分为多行，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

将日期区间拆分为多行

将二维表格中的开始时间结束间用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

代码

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

这篇关于sql将日期区间拆分为多行的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---