PostgreSQL 中最大重叠区间数量

Question

假设有一个如下结构的表格：

id    start      end
--------------------
01    00:18    00:23
02    00:22    00:31
03    00:23    00:48
04    00:23    00:39
05    00:24    00:25
06    00:24    00:31
07    00:24    00:38
08    00:25    00:37
09    00:26    00:42
10    00:31    00:34
11    00:33    00:38

该目标是计算在任何给定时间段内处于活动状态的总行数的最大值（即在start和end之间）。使用过程性算法可以相对容易地实现，但我不确定如何在SQL中执行此操作。

根据上述示例，此最大值将为8，并将对应于00:31时间戳，其中活动行为2、3、4、6、7、8、9、10（如下所示模式）。

获取时间戳和与最大值对应的活动行并不重要，只需要实际的最大值即可。

- Jivan

---

嗨@AdrianKlaver，有点像。我想要重叠区间的最大计数，不用考虑它发生在哪个特定的时间戳。 - Jivan

那么是谁/什么决定了什么是活动的，或者更重要的是比较值（例如00:31）是什么？一个初步的想法是select count(*) from time_tbl where '00:31'::time between start and end，假设start和end是time字段。 - Adrian Klaver

我仍然不清楚“active”的定义是什么？或者换句话说，你想要找出什么？ - Adrian Klaver

作为一个例子（并非实际情况），您可以将每一行视为一名护士的工作班次，包括开始和结束时间。我想知道在同一时间内最多有多少名护士在工作。例如，如果在历史上的任何时刻，同时最多有48名护士在工作，我想获取这个值（48）。 - Jivan

我妹妹是一名护士，她会喜欢这么短的轮班时间:) 因此，“BETWEEN” 的想法可以实现，只需要定义时间点的区间即可。 - Adrian Klaver

Answer 1

我最初想的是使用generate_series()来迭代每一分钟并获取每个活跃时间间隔的计数，然后取其中最大值。

您可以改进您的想法，仅迭代表中的“开始”值，因为其中一个“开始”点包含具有最大活动行的时间间隔。

select id, start,
    (select count(1) from tbl t where tbl.start between t.start and t."end")
from tbl;

这是结果

id  start   count
-----------------
1   00:18:00    1
2   00:22:00    2
3   00:23:00    4
4   00:23:00    4
5   00:24:00    6
6   00:24:00    6
7   00:24:00    6
8   00:25:00    7
9   00:26:00    7
10  00:31:00    8
11  00:33:00    7

因此，这个查询会给你一些最大数量的行已经被激活。

select
    max((select count(1) from tbl t where tbl.start between t.start and t."end"))
from tbl;

max
-----
8

- Yevhen Bondar