时间戳

首页 > 解决方案 > 如何在 Postgresql 中查找多行之间的重叠日期范围？

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

问题描述

我有一张员工表，其中包含他们的职位、团队、团队负责人等信息。每一行都有一个valid_from和valid_to字段，它与其他规范组成一个特定的日期范围。如果任何信息发生更改，它将自动使用valid_from= now() 创建一个新行，并将前一行设置为valid_to= now() - 间隔“1 天”。

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

我从表中做了一个视图：

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：

| 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 行。查询将根据需要从前端触发，因此应该对其进行优化，以便能够尽快发回数据。任何帮助表示赞赏！

标签： sqlpostgresql

解决方案

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

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);

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

推荐阅读