PostgreSQL如何基于日期范围将单行数据拆分为多行
单条日期区间记录拆分为逐日记录实现方案
实现该需求的核心是先生成覆盖所需时间范围的连续日期序列,再将该序列与ABC表做关联,过滤出序列日期落在[start_date, end_date]区间内的结果即可。以下是不同主流数据库的具体实现方式:
MySQL 实现
8.0及以上版本(支持递归CTE)
WITH RECURSIVE date_sequence AS ( -- 递归起始点:取全表最小起始日期作为初始值 SELECT MIN(start_date) AS cur_date FROM ABC UNION ALL -- 递归逻辑:每次日期加1,直到超过全表最大结束日期 SELECT DATE_ADD(cur_date, INTERVAL 1 DAY) FROM date_sequence WHERE cur_date < (SELECT MAX(end_date) FROM ABC) ) -- 关联原表获取全字段 SELECT a.id, a.name, d.cur_date AS single_date FROM ABC a JOIN date_sequence d ON d.cur_date BETWEEN a.start_date AND a.end_date;
注:如果日期区间超过1000天,需要提前执行SET SESSION cte_max_recursion_depth = 10000;调整递归深度限制
5.x版本(无递归CTE支持)
可以提前维护一张数字辅助表nums,表中包含从0开始的连续整数(数值上限需要覆盖你可能遇到的最大日期间隔天数),查询写法如下:
SELECT a.id, a.name, DATE_ADD(a.start_date, INTERVAL n.n DAY) AS single_date FROM ABC a JOIN nums n ON DATE_ADD(a.start_date, INTERVAL n.n DAY) <= a.end_date;
PostgreSQL 实现
PostgreSQL内置generate_series函数可以直接生成连续日期,写法更简洁:
SELECT a.id, a.name, generate_series(a.start_date, a.end_date, '1 day'::interval)::date AS single_date FROM ABC a;
Oracle 实现
可以用CONNECT BY语法生成序列:
SELECT a.id, a.name, a.start_date + (level - 1) AS single_date FROM ABC a CONNECT BY a.start_date + (level - 1) <= a.end_date AND PRIOR a.id = a.id AND PRIOR SYS_GUID() IS NOT NULL;
注:最后两行是为了避免多记录批量拆分时出现循环错误
结果示例
针对你给出的测试数据,执行后会返回6条记录,single_date字段分别为2021-01-01到2021-01-06的连续日期,其余id、name字段保持原值不变。
内容的提问来源于stack exchange,提问作者Rahul
相关产品推荐
相关产品推荐

