Thomas's questions

我在 Postgres 中有一个关于水道上船只位置的数据集。这是该表的示例：

boat_id 是船的唯一标识，ts 是时间戳，water_id 是航道的唯一标识。我想知道数据集中每小时有多少艘船通过每条水道。结果应如下所示：

原始数据包含船只的位置，而不是通道。因此：

1. 同一航道上同一条船的多个数据点应计为一个通道。
2. 如果一艘船已经在另一条航道上并且回来了，它应该被算作另一条航道。
3. 如果在多个小时内在同一水道上检测到一艘船，而其间没有在另一个水道上，则应在首次检测到的小时内将其计为一次通过。在上面的示例数据中，boat_A 在 16 小时在水道 WW_01 上通过 1 次，在 17 小时在 WW_02 上通过 1 次，boat_b 在 16 小时在 WW_01 上进行 1 次通过（18 小时没有通过，因为它没有去中间的另一个水道），boat_C在 16 小时在水路 WW_01 上进行 2 次通过，在 16 小时在 WW_02 上进行 1 次通过。在表格中（结果中不必包含 0 次航道小时组合）：

得到这个结果的查询应该是什么样的？在我看来，它包括两个步骤：

1. 计算每条船每条航道的独特通道
2. 将这些组织在一个表格中，如上面的示例

在这里提琴