如何找到日期范围最重叠的时间段
假设您有一个包含标识符，开始时间和结束时间的表.这些开始和结束时间可以是任何时间长度.开始时间总是早于结束时间.假设没有空值.
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"聚合仅用于说明.
...，输出:
| 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] |