Postgres 14中聚合datemultirange计算并集的更优方案

问题原因 #

range_agg 函数仅支持单个range类型（如daterange）作为输入，不直接兼容datemultirange类型，因此直接调用会提示函数不存在；aggregate_union 并非PostgreSQL内置的针对multirange的聚合函数，自然也无法使用。

优雅解决方案 #

方案1：简化的unnest+range_agg写法 #

无需CTE，直接通过行展开语法拆分multirange后聚合，写法更紧凑：

SELECT
  type_id,
  range_agg(r)
FROM foo, unnest(some_date_ranges) r
GROUP BY type_id;

此方法利用unnest将每行的datemultirange拆分为单个daterange，再通过range_agg自动聚合所有拆分后的range并计算并集。

方案2：自定义multirange聚合函数 #

如果需要长期复用该逻辑，可以创建一个针对datemultirange的自定义聚合函数，后续调用方式与内置函数完全一致：

1. 创建聚合函数：

CREATE AGGREGATE multirange_agg(datemultirange) (
  SFUNC = range_union,
  STYPE = datemultirange,
  INITCOND = '{}'
);

• SFUNC = range_union：指定每次迭代执行的函数，range_union原生支持两个datemultirange的并集计算
• STYPE = datemultirange：聚合过程中的状态类型为datemultirange
• INITCOND = '{}'：初始状态设为空的datemultirange

2. 使用自定义聚合函数：

SELECT
  type_id,
  multirange_agg(some_date_ranges)
FROM foo
GROUP BY type_id;

此方法只需创建一次函数，后续调用逻辑完全匹配你最初的预期写法，是最优雅的长期解决方案。

结果验证 #

以type_id=4的数据为例，合并后的结果为：
{[2016-07-02,2016-07-24),[2017-10-03,2017-10-13),[2018-05-23,2021-04-08)}
完全符合多个datemultirange的并集计算预期。

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