如何将Oracle按日拆分时间区间的CONNECT BY查询转换为PostgreSQL语法

PostgreSQL 等效实现 #

使用PostgreSQL内置的generate_series函数搭配横向连接即可实现同等效果，语法比Oracle层级查询更简洁：

SELECT
  ITEM,
  DATE_FROM,
  DATE_TO,
  gs.DATE_PER_DAY
FROM t
CROSS JOIN LATERAL generate_series(t.DATE_FROM, t.DATE_TO, INTERVAL '1 day') AS gs(DATE_PER_DAY);

如果需要和示例完全一致的dd/mm/yyyy格式的日期展示，可以加一层格式化处理：

SELECT
  ITEM,
  DATE_FROM,
  DATE_TO,
  to_char(gs.DATE_PER_DAY, 'dd/mm/yyyy') AS DATE_PER_DAY
FROM t
CROSS JOIN LATERAL generate_series(t.DATE_FROM, t.DATE_TO, INTERVAL '1 day') AS gs(DATE_PER_DAY);

逻辑说明 #

• generate_series 可以按照指定步长生成连续序列，此处设置步长为1天，直接输出目标时间区间内的所有单日日期
• CROSS JOIN LATERAL 会为原表的每一行数据匹配该行对应时间区间生成的所有日期，自动完成时间区间拆分的逻辑
• 该写法兼容PostgreSQL 9.4及以上版本，性能优于递归CTE实现，且如果存在DATE_FROM晚于DATE_TO的异常数据，不会生成无效记录，无需额外加判断条件

内容的提问来源于stack exchange，提问作者margo1395