sql - 在具有非基于时间的开始和结束列的表中查找间隙

Question

我需要根据 double 开始和结束列找出连续数据点中的间隙。

为简单起见，我们称它们为 startPoint 和 endPoint，它们跟踪一条直线上的空间位置。 endPoint 和 startPoint 之间的差异将命名为距离。在这个“距离”上，捕获特定的力/效果信号值，并根据这些值将状态存储在表中。每行都有一个唯一的 id 标识符。

因此，表格如下所示:

| id | startPoint | endPoint | state    |
|----|------------|----------|----------|
| 1  | 0.0        | 5.8      | Active   |
| 2  | 5.8        | 7.1      | Inactive |
| 3  | 7.5        | 10.2     | Inactive |
| 4  | 10.2       | 11.3     | Inactive |
| 5  | 11.6       | 12.1     | Active   |

我一直在努力想出一个可以在 PostgresSQL 中运行并产生以下结果的查询:

| startGap   | endGap   |
|------------|----------|
| 7.1        | 7.5      |
| 11.3       | 11.6     |

我很清楚，我所要做的就是将之前的 endPoint 与接下来的 startPoint 进行比较，但到目前为止我运气不好。

如有任何帮助，我们将不胜感激。

Answer 1

使用窗口函数lead() :

with my_table(id, startpoint, endpoint, state) as (
values
    (1, 0.0, 5.8, 'Active'),
    (2, 5.8, 7.1, 'Inactive'),
    (3, 7.5, 10.2, 'Inactive'),
    (4, 10.2, 11.3, 'Inactive'),
    (5, 11.6, 12.1, 'Active')
)

select *
from (
    select endpoint as startgap, lead(startpoint) over (order by startpoint) as endgap
    from my_table
    ) s
where startgap <> endgap;

 startgap | endgap 
----------+--------
      7.1 |    7.5
     11.3 |   11.6
(2 rows)