sql - 从有序列表构建对

Question

我正在研究火车时刻表，并考虑在各个车站之间建立时间联系。

举个例子:

Trip I:  A (t1) ------------> B (t3) ------------> E (t5)
Trip II:          C (t2) ------------> D (t4) ---> E (t6)

我们有 5 个站点(A 到 E)，其中火车在时间 ti 停止，有两次不同的行程(从 A 到 E 到 B 和从 C 到 EthoughD`)。

时间表看起来像这样(从数据库的角度来看，还有一个技术标识符):

 --------------------
| Trip | Stop | Time |
 --------------------
| I    | A    | t1   |
| I    | B    | t3   |
| I    | E    | t5   |
| II   | C    | t2   |
| II   | D    | t4   | 
| II   | E    | t6   | 
 --------------------

我想以此为基础建立车站之间的联系，例如(为简单起见，我们不考虑火车在车站停留的时间):

 ---------------------------------------------------------------
| Departure stop | Departure time | Arrival stop | Arrival time |
 ---------------------------------------------------------------
| A              | t1             | B            | t3           |
| B              | t3             | E            | t5           |
| C              | t2             | D            | t4           |
| D              | t4             | E            | t6           |
 ---------------------------------------------------------------

我已经用一些代码在内存中完成了:

1. 按行程分组，
2. 按时间排序，
3. 在滑动窗口中获取元素 2×2。

然而，它很简单但效率不高。

这就是为什么我想知道是否可以使用 SQL 来表达它。是吗？

你会怎么做？

我正在使用 PostgreSQL 进行测试，但我对大纲更感兴趣，而不是特定数据库的具体实现。

Answer 1

1st 我会为每个行程分区生成一个行号，然后在 row_number + 1=row_number 上自行加入

每一行对于每一次行程，引擎都会按照时间从小到大的顺序分配一个编号1-N。这使我们能够简单地在 row_number + 1 上自行加入。

这可能是一个问题，如果时间是 JUST time 并且您在 2300 出发，在 0100 到达，或者如果行程跨越数天。我也在这里假设军事时间。如果是 11:00 和 1:00，那么我们也可能会遇到问题。

由于每次编号都会从1重新开始，所以我们也需要加入这趟旅程。

WITH CTE As (
SELECT A.*, row_Number() over (partition by trip Order by time asc) RN FROM TIME A)

SELECT A.Stop, A.Time, B.Stop, B.Time
FROM CTE A 
INNER JOIN CTE B 
  on A.RN+1 = B.RN
 and A.trip = B.Trip