聚合SQL表中的连续行

debugcn 发表于 Dev

云

我正在尝试在下面的SQL表上运行聚合函数，以汇总由“长+纬度”分组的所有“ LengthOfRecord”，以及仅连续的行（即，处于运行顺序的“ RowNumber”）。

+-----------+-----------+---------------+----------------+
| RowNumber | Vessel ID |   Long+Lat    | LengthOfRecord |
+-----------+-----------+---------------+----------------+
| 102313179 | Vessel 01 | 123.751 1.196 |            181 |
| 102313180 | Vessel 01 | 123.751 1.196 |            179 |
| 102313181 | Vessel 01 | 123.751 1.196 |            361 |
| 102313182 | Vessel 01 | 123.751 1.196 |            359 |
| 102313183 | Vessel 01 | 123.751 1.196 |            180 |
| 102313184 | Vessel 01 | 123.751 1.196 |            181 |
| 102313185 | Vessel 01 | 123.751 1.196 |            179 |
| 102313186 | Vessel 01 | 123.751 1.196 |            180 |
| 102313187 | Vessel 01 | 123.751 1.196 |            360 |
| 102313188 | Vessel 01 | 123.751 1.196 |            360 |
| 102313189 | Vessel 01 | 123.751 1.196 |            180 |
| 102313191 | Vessel 01 | 123.751 1.196 |            181 |
| 102313298 | Vessel 01 | 123.750 1.197 |            180 |
| 102313375 | Vessel 01 | 123.742 1.196 |            179 |
| 102313376 | Vessel 01 | 123.742 1.196 |            359 |
| 102313377 | Vessel 01 | 123.742 1.196 |            180 |
| 102313379 | Vessel 01 | 123.742 1.196 |            181 |
| 102313380 | Vessel 01 | 123.742 1.196 |            178 |
+-----------+-----------+---------------+----------------+

以下是我试图通过SQL语句实现的结果。无论如何，我可以通过SQL查询来做到这一点吗？

+-----------+---------------+----------------+
| Vessel ID |   Long+Lat    | LengthOfRecord |
+-----------+---------------+----------------+
| Vessel 01 | 123.751 1.196 |           2881 |
| Vessel 01 | 123.750 1.197 |            180 |
| Vessel 01 | 123.742 1.196 |           1077 |
+-----------+---------------+----------------+

戈登·利诺夫（Gordon Linoff）

您可以使用行数差异方法来执行此操作：

select vesselId, latLong, sum(lengthOfRecord)
from (select t.*,
             row_number() over (partition by vesselId order by rowNumber) as seqnum,
             row_number() over (partition by vesselId, latlong order by rowNumber) as seqnum_latlong
      from table t
     ) t
group by (seqnum  - seqnum_latlong), latLong, vesselId;

行号方法的区别很难解释。它标识具有相同值的相邻行。如果运行子查询，您将看到计算的工作方式。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。