如何找到日期范围最重叠的时间段 [英] How to find most overlapping time period with date ranges

问题描述

假设您有一个包含标识符，开始时间和结束时间的表.这些开始和结束时间可以是任何时间长度.开始时间总是早于结束时间.假设没有空值.

Suppose you have a table with an identifier, a start time and an end time. These start and end times can be any length of time. The start time is always before the end time. Assume there are no nulls.

什么样的查询会告诉我最受欢迎"的时间，即每行中的两个范围与其他大多数行重叠的地方?

What kind of query would tell me the most "popular" time, i.e. where the two ranges in each row overlaps with the most other rows?

此应用程序的实际应用是一张记录用户登录和注销时间的表格.我想编写一个查询，告诉我何时有最多并发用户登录，并查看这段时间.

The real life application of this is that it's a table recording users' sign in and sign out times. I want to write a query that will tell me when the most concurrent users were logged in and see what period of time this was.

谢谢.

推荐答案

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

Here's an example solution using a simple self-join and a 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

"Overlapping intervals"聚合仅用于说明.

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] |

这篇关于如何找到日期范围最重叠的时间段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！