sql(oracle)计算重叠间隔的数量 [英] sql (oracle) counting number of overlapping intervals

问题描述

我有以下问题:

在oracle sql数据库中给出下表test:

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

对于所有具有相同name的id，我想找到重叠间隔的数量(包括端点)[c1>，即:

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

推荐答案

一种方法使用相关的子查询:

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;

这篇关于sql(oracle)计算重叠间隔的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！