下面是一个使用简单自连接和 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] |