我正在寻找一种高效的方法,以查找时间戳范围之间的所有交集。它需要与 PostgreSQL 9.2 兼容。
假设这些范围表示一个人可用于会面的时间。每个人可能有一段或多段可用时间范围。我想找到所有会议可以进行的时间段(即期间所有人都可用)。
以下是我目前的想法。它似乎可以工作,但我认为效率不高,因为它一次只考虑一个人的可用性。
假设这些范围表示一个人可用于会面的时间。每个人可能有一段或多段可用时间范围。我想找到所有会议可以进行的时间段(即期间所有人都可用)。
以下是我目前的想法。它似乎可以工作,但我认为效率不高,因为它一次只考虑一个人的可用性。
WITH RECURSIVE td AS
(
-- Test data. Returns:
-- ["2014-01-20 00:00:00","2014-01-31 00:00:00")
-- ["2014-02-01 00:00:00","2014-02-20 00:00:00")
-- ["2014-04-15 00:00:00","2014-04-20 00:00:00")
SELECT 1 AS entity_id, '2014-01-01'::timestamp AS begin_time, '2014-01-31'::timestamp AS end_time
UNION SELECT 1, '2014-02-01', '2014-02-28'
UNION SELECT 1, '2014-04-01', '2014-04-30'
UNION SELECT 2, '2014-01-15', '2014-02-20'
UNION SELECT 2, '2014-04-15', '2014-05-05'
UNION SELECT 3, '2014-01-20', '2014-04-20'
)
, ranges AS
(
-- Convert to tsrange type
SELECT entity_id, tsrange(begin_time, end_time) AS the_range
FROM td
)
, min_max AS
(
SELECT MIN(entity_id), MAX(entity_id)
FROM td
)
, inter AS
(
-- Ranges for the lowest ID
SELECT entity_id AS last_id, the_range
FROM ranges r
WHERE r.entity_id = (SELECT min FROM min_max)
UNION ALL
-- Iteratively intersect with ranges for the next higher ID
SELECT entity_id, r.the_range * i.the_range
FROM ranges r
JOIN inter i ON r.the_range && i.the_range
WHERE r.entity_id > i.last_id
AND NOT EXISTS
(
SELECT *
FROM ranges r2
WHERE r2.entity_id < r.entity_id AND r2.entity_id > i.last_id
)
)
-- Take the final set of intersections
SELECT *
FROM inter
WHERE last_id = (SELECT max FROM min_max)
ORDER BY the_range;
values
子句更简洁地完成,而无需使用select
和union
:values (1, '2014-01-01'::timestamp, '2014-01-31'::timestamp), (2, ...)
并在公用表达式中定义列名。 - user330315