两个日期区间重叠oracle,sql – 如何查找具有日期范围的大多数重叠时间段

这是一个使用简单的自连接和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

“重叠间隔”聚合仅用于说明.

…输出：

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