sql - 在 PostgreSQL 中查找日期范围之间的交集

Question

我有两个日期 check_in 和 check_out 的记录，我想知道多个人同时签到的范围。

所以如果我有以下 checkin / checkout :

• A 人:1PM - 6PM
• B 人:下午 3 点 - 10 点
• C 人:晚上 9 点 - 11 点

我想得到 3PM - 6PM(A 和 B 的重叠)和 9PM - 10PM(B 和 C 的重叠)。

我可以编写一个算法来用代码在线性时间内完成这项工作，是否也可以通过 PostgreSQL 的线性时间关系查询来完成？

它需要有一个最小的响应，这意味着没有重叠的范围。因此，如果结果给出的范围是 6PM - 9PM 和 8PM - 10PM，那将是不正确的。它应该返回 6PM - 10pm。

Answer 1

假设

解决方案在很大程度上取决于包括所有约束在内的确切表定义。由于问题中缺乏信息，我将假定此表:

CREATE TABLE booking (
  booking_id serial PRIMARY KEY
, check_in   timestamptz NOT NULL
, check_out  timestamptz NOT NULL
, CONSTRAINT valid_range CHECK (check_out > check_in)
);

因此，没有 NULL 值，只有包含下限和不包含上限的有效范围，我们并不关心谁 checkin 。

同时假设 Postgres 的当前版本至少为 9.2。

查询

一种方法是使用 UNION ALL 和窗口函数仅使用 SQL:

SELECT ts AS check_id, next_ts As check_out
FROM  (
   SELECT *, lead(ts) OVER (ORDER BY ts) AS next_ts
   FROM  (
      SELECT *, lag(people_ct, 1 , 0) OVER (ORDER BY ts) AS prev_ct
      FROM  (
         SELECT ts, sum(sum(change)) OVER (ORDER BY ts)::int AS people_ct
         FROM  (
            SELECT check_in AS ts, 1 AS change FROM booking
            UNION ALL
            SELECT check_out, -1 FROM booking
            ) sub1
         GROUP  BY 1
         ) sub2
      ) sub3
   WHERE  people_ct > 1 AND prev_ct < 2 OR  -- start overlap
          people_ct < 2 AND prev_ct > 1     -- end overlap
   ) sub4
WHERE  people_ct > 1 AND prev_ct < 2;

SQL Fiddle.

说明

• 在子查询sub1 中派生出check_in 和check_out 一列的表。 check_in 将人群加一，check_out 减一。

• 在 sub2 中，对同一时间点的所有事件求和并使用窗口函数计算运行计数:这就是窗口函数 sum()聚合 sum() - 并转换为 integer 或者我们从中得到 numeric:

     sum(sum(change)) OVER (ORDER BY ts)::int
  

• 在sub3中查看上一行的计数

• 在sub4中只保留重叠时间范围开始和结束的行，并将时间范围的结束拉到与lead()相同的行中。

• 最后，只保留时间范围开始的行。

---

为了优化性能，我会在 plpgsql 函数中遍历表一次，就像在 dba.SE 上的这个相关答案中演示的那样:

• Calculate Difference in Overlapping Time in PostgreSQL / SSRS