sql - 如何在多行之间的 Postgresql 中查找重叠的日期范围？

Question

我有一张员工表，其中包含他们的职位、团队、团队领导等信息。每一行都有一个 valid_from 和 valid_to 字段，它们构成了特定的日期范围与其他规范。如果有任何信息更改，它将自动创建一个新行，其中包含 valid_from = now() 并将上一行设置为 valid_to = now() - interval '1 day'。

问题是这种自动生成的行可能会产生重叠的日期范围，因为它是从不同的电子表格生成的，我需要手动更正它们。所以我想编写一个 sql 查询来找到这些重叠。

我从表格中做了一个 View :

select
  employee_id,
  rank() over (partition by employee_id order by valid_from) as rank,
  valid_from,
  valid_to
from myTable

它给我的数据如下:

| employee_id | rank | valid_from | valid_to   |
|-------------|------|------------|------------|
| 1000        | 1    | 2016-11-28 | 2017-06-30 |
| 1000        | 2    | 2017-07-01 | 2018-02-26 |
| 1000        | 3    | 2018-02-25 | 2018-03-12 |
| 1001        | 1    | 2016-11-28 | 2017-07-30 |
| 1001        | 2    | 2017-07-31 | 2017-07-31 |
| 1001        | 3    | 2017-08-01 | 2017-08-01 |

如果当前行日期范围与同一 employee_id 中的任何其他行重叠，我想再添加一个具有 true/false 值或类似值的列:

| employee_id | rank | valid_from | valid_to   | overlapp_exist |
|-------------|------|------------|------------|----------------|
| 1000        | 1    | 2016-11-28 | 2017-06-30 | false          |
| 1000        | 2    | 2017-07-01 | 2018-02-26 | true           |
| 1000        | 3    | 2018-02-25 | 2018-03-12 | true           |
| 1001        | 1    | 2016-11-28 | 2017-07-30 | false          |
| 1001        | 2    | 2017-07-31 | 2017-07-31 | false          |
| 1001        | 3    | 2017-08-01 | 2017-08-01 | false          |

该表包含大约 50k 行。查询将按需从前端触发，因此应该对其进行优化，以便能够尽快发回数据。任何帮助表示赞赏!

Answer 1

它不会很快，因为每一行都必须与其他每一行相匹配:

SELECT a.*, b.*
FROM mytable AS a
   JOIN mytable AS b
      ON daterange(a.valid_from, a.valid_to) && daterange(b.valid_from, b.valid_to)
WHERE (a.valid_from, a.valid_to) <= (b.valid_from, b.valid_to);

最好在表上设置一个排除约束，以防止首先添加此类数据。