如何在PostgreSQL中获取最大并发事件数量？

Question

我有一个名为 events 的表格，内容如下：

id: int
source_id: int
start_datetime: timestamp
end_datetime: timestamp  

这些事件可能存在重叠，我想知道在一段时间内发生的最大重叠事件数。例如，在类似这样的情况下：

id | source_id | start_datetime     | end_datetime
----------------------------------------------------------
1  | 23        | 2017-1-1T10:20:00  | 2017-1-1T10:40:00
1  | 42        | 2017-1-1T10:30:00  | 2017-1-1T10:35:00
1  | 11        | 2017-1-1T10:37:00  | 2017-1-1T10:50:00  

答案是2，因为在10:30到10:35之间最多有2个事件重叠。
我正在使用Postgres 9.6。

- sazary

---

时间跨度是一个输入吗？ - Radim Bača

为了简单起见，每个查询都将考虑为常量。 - sazary

{btsdaf} - sazary

Answer 1

这里的思路很简单：统计星号出现的次数并减去停止符出现的次数，以此来得到每个时间点的净值。余下的工作只需要聚合就可以了：

with e as (
      select start_datetime as dte, 1 as inc
      from events
      union all
      select end_datetime as dte, -1 as inc
      from events
     )
select max(concurrent)
from (select dte, sum(sum(inc)) over (order by dte) as concurrent
      from e
      group by dte
     ) e;

子查询显示每个时间段重叠事件的数量。

您可以获取时间框架如下：

select dte, next_dte, concurrent
from (select dte, sum(sum(inc)) over (order by dte) as concurrent,
             lead(dte) over (partition by dte) as next_dte
      from e
      group by dte
     ) e
order by concurrent desc
fetch first 1 row only;

- Gordon Linoff

---

在Windows函数中，我们是否有默认的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW？ - Nicholas Humphrey

@NicholasHumphrey . . . 我认为当指定了 ORDER BY 时，默认值为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。 - Gordon Linoff

@pure_true……是的，在子查询中。很好地捕捉到了。 - Gordon Linoff

@GordonLinoff 好的，那么当dte相同时，它可能不会按照预期工作，我在问这个问题是因为我已经使用了你的完美算法，但由于分组的原因，当我们有相同的dte时，它无法正确递增。 - Denis

@pure_true . . . 它应该仍然可以工作。但是，这可能取决于您是否将结束日期视为该期间的一部分。 - Gordon Linoff

Answer 2

我不确定应该如何处理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;

- user330315

---

{btsdaf} - klin