我有一个time_entries表，其中我使用Postgres 15的多范围特性来合并重叠和相邻的时间范围：
CREATE TABLE time_entries (
id bigint NOT NULL,
contract_id bigint,
"from" timestamp(6) without time zone,
"to" timestamp(6) without time zone,
range tsrange GENERATED ALWAYS AS (tsrange("from", "to")) STORED
);
INSERT INTO time_entries VALUES (1, 1, '2022-12-07T09:00', '2022-12-07T09:45');
INSERT INTO time_entries VALUES (2, 1, '2022-12-07T09:45', '2022-12-07T10:00');
INSERT INTO time_entries VALUES (2, 1, '2022-12-07T09:55', '2022-12-07T10:15');
INSERT INTO time_entries VALUES (2, 1, '2022-12-07T10:20', '2022-12-07T10:30');
INSERT INTO time_entries VALUES (2, 1, '2022-12-07T10:45', '2022-12-07T11:00');
SELECT contract_id, unnest(range_agg(range)) AS range FROM time_entries GROUP BY contract_id;当前结果为：
| 合同标识|范围|
| - ------|- ------|
| 1个|【“2022年12月7日09时00分”、“2022年12月7日10时15分”】|
| 1个|[“2022年12月7日10：20：00”、“2022年12月7日10：30：00”）|
| 1个|【“2022年12月7日10时45分”、“2022年12月7日11时00分”】|
现在，当两个范围相距最多5分钟时，我也希望将它们合并。因此，所需的结果将是：
| 合同标识|范围|
| - ------|- ------|
| 1个|【“2022年12月7日09时00分”、“2022年12月7日10时30分”】|
| 1个|【“2022年12月7日10时45分”、“2022年12月7日11时00分”】|
在此工作dbfiddle：https://dbfiddle.uk/owHkVaZ5
这可以只用SQL实现吗？或者我需要某种定制的Postgres函数来实现？我听说过聚合器函数，但从来没有用过。
1条答案
按热度按时间6rqinv9w1#
RichardHuxton在聚合前增加5分钟的想法很好，下面是一个简单的实现：
您需要
CROSS JOIN，因为您希望将每个组与属于它的所有多范围元素连接起来。这个“belong”用LATERAL表示，这意味着您在后面的列表条目中引用前面FROM列表条目中的元素。本质上，需要该构造和子查询，以便我可以将未嵌套的范围放入它们所属的FROM表达式中。以便 * 可以在SELECT列表中使用它们。AS u(r)是一个表别名，即表名和它的列名同时使用的别名。