sql (oracle) counting number of overlapping interv

问题:

I have the following problem:

Given the following table test in an oracle sql database:

+----+------+-------+------+
| id | name | start | stop |
+----+------+-------+------+
| 1  |   A  |   1   |  5   |
+----+------+-------+------+
| 2  |   A  |   2   |  6   |
+----+------+-------+------+
| 3  |   A  |   5   |  8   |
+----+------+-------+------+
| 4  |   A  |   9   |  10  |
+----+------+-------+------+
| 5  |   B  |   3   |  6   |
+----+------+-------+------+
| 6  |   B  |   4   |  8   |
+----+------+-------+------+
| 7  |   B  |   1   |  2   |
+----+------+-------+------+

I would like to find the number of overlapping intervals (endpoints included) [start, stop] n_overlap, for all id having the same name, i.e.:

+----+------+-------+------+-----------+
| id | name | start | stop | n_overlap |
+----+------+-------+------+-----------+
| 1  |   A  |   1   |  5   |     3     |
+----+------+-------+------+-----------+
| 2  |   A  |   2   |  6   |     3     |
+----+------+-------+------+-----------+
| 3  |   A  |   4   |  8   |     3     |
+----+------+-------+------+-----------+
| 4  |   A  |   9   |  10  |     1     |
+----+------+-------+------+-----------+
| 5  |   B  |   3   |  6   |     2     |
+----+------+-------+------+-----------+
| 6  |   B  |   4   |  8   |     2     |
+----+------+-------+------+-----------+
| 7  |   B  |   1   |  2   |     1     |
+----+------+-------+------+-----------+

回答1:

One method uses a correlated subquery:

select t.*,
       (select count(*)
        from test t2
        where t2.name = t.name and
              t2.start < t.end and
              t2.end > t.start
       ) as num_overlaps
from test t;