PostgreSQL如何将表中日期范围展开为每日一行的独立记录

问题根因 #

你原有查询的序列生成逻辑是基于TAB1表本身来生成行号，TAB1只有600行数据，所以生成的seqnum最大只能到600，自然无法支撑超过600天的日期间隔展开。另外原有逻辑的行号是从1开始计数，会丢失open_date当天的第一条记录。

最优解决方案 #

PostgreSQL内置了generate_series函数可以直接生成连续序列，搭配LATERAL关联可以完美适配按行展开日期间隔的需求：

SELECT
  t.id,
  t.open_date,
  t.close_date,
  s.valid_date::date AS valid_date -- 转成date类型避免带时分秒
FROM TAB1 t
LEFT JOIN LATERAL
  generate_series(t.open_date, t.close_date, interval '1 day') AS s(valid_date)
  ON true
ORDER BY t.id, s.valid_date;

这个方案不需要提前预估最大间隔长度，会自动根据每行的open_date和close_date生成对应长度的日期序列，性能和易用性都更高。

兼容写法（不使用LATERAL） #

如果需要适配不支持LATERAL的旧 PostgreSQL 版本，可以先生成一个足够长的全局序列：

SELECT
  t.id,
  t.open_date,
  t.close_date,
  (t.open_date + s.seqnum * interval '1 day')::date AS valid_date
FROM TAB1 t
LEFT JOIN (
  -- 序列上限设置为你表中最大的日期间隔即可，这里设置为20000可以覆盖超过50年的间隔
  SELECT generate_series(0, 20000) AS seqnum
) s ON s.seqnum <= (t.close_date - t.open_date)
ORDER BY t.id, valid_date;

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