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_date
            FROM    (
                        SELECT  start_date,
                                end_date,
                                datediff(end_date, start_date) AS day_num
                        FROM    (
                                    SELECT  '2023-12-01' AS start_date,
                                            '2023-12-01' AS end_date
                                    UNION ALL
                                    SELECT  '2023-12-01' AS start_date,
                                            '2023-12-02' AS end_date
                                    UNION ALL
                                    SELECT  '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