我不确定应该如何处理id
和source_id
列,但根据您的描述,可能是这样的:
select e1.source_id,
count(distinct e2.source_id) as overlap_count,
array_agg(e2.source_id) as overlap_events
from events e1
join events e2
on e1.source_id <> e2.source_id
and (e1.start_datetime, e1.end_datetime) overlaps (e2.start_datetime, e2.end_datetime)
group by e1.source_id
order by overlap_count desc;
根据你提供的样本数据,将返回以下结果:
source_id | overlap_count | overlap_events
----------+---------------+---------------
23 | 2 | {42,11}
11 | 1 | {23}
42 | 1 | {23}
如果只需要获取最大行,可以在查询中添加limit 1
。
另一个选项(可能较慢),如果需要从事件表中获取完整的行:
select e1.id, e1.source_id, e1.start_datetime, e1.end_datetime,
(select count(*)
from events e2
where e2.source_id <> e1.source_id
and (e1.start_datetime, e1.end_datetime) overlaps (e2.start_datetime, e2.end_datetime)
) as overlap_count
from events e1
order by overlap_count desc;
另一种选择是使用
范围类型和
&&
运算符,而不是
overlaps
:
select e1.source_id,
count(distinct e2.source_id) as overlap_count,
array_agg(e2.source_id) as overlap_events
from events e1
join events e2 on e1.source_id <> e2.source_id
and tsrange(e1.start_datetime, e1.end_datetime,'[]') && tsrange(e2.start_datetime, e2.end_datetime, '[]')
group by e1.source_id
order by overlap_count desc;