Postgres检查表行中时间戳范围重叠

Question

Postgres检查表行中时间戳范围重叠

sqlpostgresqldatetimetime-series

5

我们有一个包含约200万行的Postgres数据表（材料化视图），其中包含以下列：

- start_time (timestampz) - 有索引 - end_time (timestampz) - 有索引

对于表中的每一行，我们想要添加一个结果列，其中包含：

- 如果该行的开始和结束时间范围与任何其他行重叠，则为1。 - 如果该行的开始和结束时间范围与任何其他行不重叠，则为0。

标记每一行是否存在重叠的有效方法是什么？

编辑：

预期输出应类似于：

- row_id - has_overlap - 布尔或整数（1或0）

- Brylie Christopher Oxley

我们找到的最接近的SO答案是这个： https://dev59.com/8l8e5IYBdhLWcg3wdqGT然而，它只返回重叠的行。我们特别需要计算重叠的数量，并将这些计数用于后续的报告/异常分析。 - Brylie Christopher Oxley

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user330315 · Accepted Answer

我认为这个问题不可能有一个非常快的解决方案，因为它需要将表中的每一行与表中的每一行进行比较（或者至少是在指定范围内的每一行与其他每一行进行比较）。

假设你的表的主键列名为id，你可以使用Postgres的范围函数来检查重叠的行：

with check_period (check_range) as (
   values ( tstzrange(timestamptz '2018-10-01 00:00:00', timestamptz '2018-10-14 20:15:00') )
)
select id, 
       start_Time, 
       end_time, 
       exists (select *
        from the_table t2
           cross join check_perioud
        where t2.id <> t1.id 
        and tstzrange(t1.start_time, t1.end_time) && tstzrange(t2.start_time, t2.start_time)
        and tstzrange(t2.start_time, t2.start_time) <@ check_range
       ) has_overlapping_rows
from the_table t1
  cross join check_period
where tstzrange(t1.start_time, t1.end_time) <@ check_range;

CTE（公共表达式） check_period 只是为了避免要分析的时间段值重复。如果您不关心它们是否重复，可以将其删除：

select id, 
       start_Time, 
       end_time, 
       exists (select *
        from the_table t2
        where t2.id <> t1.id 
        and tstzrange(t1.start_time, t1.end_time) && tstzrange(t2.start_time, t2.start_time)
        and tstzrange(t2.start_time, t2.start_time) <@ tstzrange(timestamptz '2018-10-01 00:00:00', timestamptz '2018-10-14 20:15:00')
       ) has_overlapping_rows
from the_table t1
where tstzrange(t1.start_time, t1.end_time) <@ tstzrange(timestamptz '2018-10-01 00:00:00', timestamptz '2018-10-14 20:15:00');

为了让时间戳范围查询更快，您应该在时间戳字段上创建索引：

create index on the_table( (tstzrange(start_time, end_time), id );

您可以扩展上述查询，返回重叠行的计数而不是true/false标志：

select id, 
       start_Time, 
       end_time, 
       (select count(*)
        from the_table t2
        where t2.id <> t1.id 
        and tstzrange(t1.start_time, t1.end_time) && tstzrange(t2.start_time, t2.start_time)
        and tstzrange(t2.start_time, t2.start_time) <@ tstzrange(timestamptz '2018-10-01 00:00:00', timestamptz '2018-10-14 20:15:00')
       ) has_overlapping_rows
from the_table t1
where tstzrange(t1.start_time, t1.end_time) <@ tstzrange(timestamptz '2018-10-01 00:00:00', timestamptz '2018-10-14 20:15:00');

然而，对于存在许多重叠行的行，这种方法会变得更慢，因为 count(*) 强制数据库检查所有重叠的行。而 exists() 解决方案可以在找到第一行后停止。