PostgreSQL 15中如何计算时间范围的聚合差值？

问题背景 #

使用PostgreSQL 15，现有time_entries表记录工作追踪（punch_clock）和计费（billed）的时间范围，需要从合并后的工作追踪时段中扣除计费时段，得到未计费的连续时段。表结构和测试数据如下：

CREATE TABLE time_entries (
    id bigint NOT NULL,
    contract_id bigint,
    "from" timestamp(6) without time zone,
    "to" timestamp(6) without time zone,
    type varchar,
    range tsrange GENERATED ALWAYS AS (tsrange("from", "to")) STORED
);

INSERT INTO time_entries VALUES (1, 1, '2022-12-07T09:00', '2022-12-07T10:00', 'billed');
INSERT INTO time_entries VALUES (2, 1, '2022-12-07T08:00', '2022-12-07T10:30', 'punch_clock');

INSERT INTO time_entries VALUES (1, 1, '2022-12-07T12:00', '2022-12-07T12:30', 'billed');
INSERT INTO time_entries VALUES (2, 1, '2022-12-07T11:30', '2022-12-07T12:15', 'punch_clock');

INSERT INTO time_entries VALUES (2, 1, '2022-12-07T13:00', '2022-12-07T13:30', 'billed');
INSERT INTO time_entries VALUES (2, 1, '2022-12-07T13:15', '2022-12-07T13:45', 'punch_clock');

INSERT INTO time_entries VALUES (2, 1, '2022-12-07T14:00', '2022-12-07T15:00', 'punch_clock');

期望得到的未计费时段结果：

由于PostgreSQL没有直接的range_difference_agg函数，可通过以下方式实现需求。

解决方案 #

核心逻辑是先分别聚合合并两种类型的时间范围，再利用多范围的减法运算得到差集，最后拆分差集为单个时段：

WITH aggregated_ranges AS (
    SELECT
        contract_id,
        -- 聚合合并所有工作追踪时段
        range_agg(range) FILTER (WHERE type = 'punch_clock') AS punch_clock_ranges,
        -- 聚合合并所有计费时段
        range_agg(range) FILTER (WHERE type = 'billed') AS billed_ranges
    FROM time_entries
    GROUP BY contract_id
)
SELECT
    ar.contract_id,
    -- 拆分差集后的每个未计费时段
    unnest(ar.punch_clock_ranges - ar.billed_ranges) AS unbilled
FROM aggregated_ranges ar;

原理说明 #

1. 聚合阶段：通过range_agg分别对punch_clock和billed类型的时间范围做并集聚合，自动合并重叠或相邻的时段，得到tsmultirange类型的合并后范围。
2. 差集计算：利用PostgreSQL对多范围的原生减法运算（-），直接从工作追踪多范围中扣除计费多范围，得到未计费的多范围集合。
3. 结果拆分：使用unnest将多范围拆分为单个tsrange记录，输出逐行的未计费时段。

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