如何找到日期范围内最重叠的时间段

Question

假设您有一张带有标识符、开始时间和结束时间的表。这些起始和结束时间可以是任意长度的时间。起始时间始终在结束时间之前。假设不存在空值。

什么样的查询能告诉我最“流行”的时间，即每行中的两个范围与最多其他行重叠的时间？

这个问题的实际应用是记录用户登录和注销时间的表。我想编写一个查询，告诉我最多有多少并发用户登录，并查看此期间是什么时间。

谢谢。

- Oliver Martin

Answer 1

有几种方法可以做到这一点。其中之一使用相关子查询，但那并不好玩。相反，让我们使用累加和方法，因为你使用的是Oracle。

关键是从一个时间戳列表开始，起始值为+1，结束值为-1。这很容易实现：

select t.*
from ((select starttime as thetime, 1 as value from table t) union all
      (select endtime, -1 as value from table t)
     ) t

现在，value 的累加总和可以告诉您任何时间点上活动重叠的数量：

select t.*, sum(value) over (order by thetime) as numactives
from ((select starttime as thetime, 1 as value from table t) union all
      (select endtime, -1 as value from table t)
     ) t

这将解决您的问题。您可能想为特定时间添加order by numactives desc。

- Gordon Linoff

Answer 2

下面是一个使用简单自连接和 GROUP BY 的示例解决方案：

WITH d(id, t1, t2) AS (
    SELECT 1, date '2010-01-01', date '2010-03-01' FROM DUAL UNION ALL
    SELECT 2, date '2010-02-01', date '2010-04-01' FROM DUAL UNION ALL
    SELECT 3, date '2010-02-01', date '2010-04-01' FROM DUAL UNION ALL
    SELECT 4, date '2010-01-01', date '2010-01-03' FROM DUAL UNION ALL
    SELECT 5, date '2011-01-01', date '2011-02-15' FROM DUAL
)
SELECT d1.id, d1.t1, d1.t2, 
       COUNT(*) "Overlap count", 
       LISTAGG('[' || d2.t1 || ', ' || d2.t2 || ']', ', ')
       WITHIN GROUP (ORDER BY d2.id) "Overlapping intervals"
FROM d d1 
LEFT OUTER JOIN d d2 
ON d2.t1 <= d1.t2 AND d1.t1 <= d2.t2
GROUP BY d1.id, d1.t1, d1.t2
ORDER BY COUNT(*) DESC

"重叠区间"聚合仅用于说明。 SQLFiddle ... 输出结果为： "

| ID | OVERLAP COUNT |                                                                          OVERLAPPING INTERVALS |
|----|---------------|------------------------------------------------------------------------------------------------|
|  1 |             4 | [01-JAN-10, 01-MAR-10], [01-FEB-10, 01-APR-10], [01-FEB-10, 01-APR-10], [01-JAN-10, 03-JAN-10] |
|  2 |             3 |                         [01-JAN-10, 01-MAR-10], [01-FEB-10, 01-APR-10], [01-FEB-10, 01-APR-10] |
|  3 |             3 |                         [01-JAN-10, 01-MAR-10], [01-FEB-10, 01-APR-10], [01-FEB-10, 01-APR-10] |
|  4 |             2 |                                                 [01-JAN-10, 01-MAR-10], [01-JAN-10, 03-JAN-10] |
|  5 |             1 |                                                                         [01-JAN-11, 15-FEB-11] |

- Lukas Eder