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

kq0g1dla 于 4个月前发布在 PostgreSQL

关注(0)|答案(3)|浏览(68)

我有一个员工表，其中包含他们的位置，团队，团队领导等信息，每行都有一个valid_from和valid_to字段，使一个特定的日期范围与其他规格。如果有任何信息更改，它会自动创建一个新的行valid_from =现在（），并将前一行设置为valid_to =现在（）-间隔'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中的任何其他行重叠，我想添加一个带有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          |

型
该表包含约50 k行。查询将从前端按需触发，因此应进行优化，以便能够尽快发回数据。感谢任何帮助！

postgresql

来源：https://stackoverflow.com/questions/57608155/how-to-find-overlapping-date-ranges-in-postgresql-between-multiple-rows

3条答案

按热度按时间

brjng4g31#

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

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

字符串
最好在表上设置一个排除约束，以防止在第一时间添加此类数据。

赞(0）回复(0）举报 4个月前

taor4pac2#

如果你想避免重叠的日期，一个约束可能是一个很好的解决方案：

ALTER TABLE my_table ADD CONSTRAINT my_table_date_range_check EXCLUDE USING GIST (
        employee_id WITH =,
        DATERANGE(valid_from, valid_to) WITH &&
);

字符串
注：此定义中的日期范围为半开放。

赞(0）回复(0）举报 4个月前

pxyaymoc3#

可以使用exists：

select t.*,
       (exists (select 1
                from mytable t2
                where t2.employee_id = t.employee_id and
                      t2.valid_from < t.valid_to and
                      t2.valid_to > t.valid_from and
                      (t2.valid_from <> t.valid_from or
                       t2.valid_to <> t.valid_to
                      )
                )
        ) as overlaps_flag
from mytable t;

字符串
注意事项：如果每一行都有唯一的标识符（我强烈推荐），那么最后两个条件就使用这一列。
如果您的目的是向数据本身添加列，则可以在update查询中使用此子查询。

编辑

另一种可能具有更好性能的方法是使用窗口函数：

select t.*,
       (max(valid_to) over (partition by employee_id over order by valid_from rows between unbounded preceding and 1 preceding) > valid_to
       ) as overlaps_flag
from mytable t;

型
我不知道应该是>还是>=。
并处理在同一日期开始（不返回null）：

select t.*,
       ( (max(valid_to) over (partition by employee_id over order by valid_from rows between unbounded preceding and 1 preceding) > valid_to
         ) or
         count(*) over (partition by employee_id, valid_from) >= 2
       ) as overlaps_flag
from mytable t;

型

赞(0）回复(0）举报 4个月前

我来回答

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

3条答案

编辑

相关问题

热门标签

最新问答