我认为这个问题不可能有一个非常快的解决方案,因为它需要将表中的每一行与表中的每一行进行比较(或者至少是在指定范围内的每一行与其他每一行进行比较)。
假设你的表的主键列名为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()
解决方案可以在找到第一行后停止。