将日期区间拆分为多行
将二维表格中的开始时间结束间用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