使用 SQL 有条件地对连续行求和

Question

我的数据以 5 分钟的套餐形式传送给我。我正在逐秒读取设备的状态，并且已经构建了一个脚本来使用 pandas 聚合相同状态的连续行。我的数据看起来像这样。

timestamp   status     length
00:00:00    1          38
00:00:38    0          72
00:01:50    1          27
...

我希望查询一整天的 5 分钟包，而没有明显的 5 分钟数据包边缘。目前，当我在 AWS Athena 中查询多个数据包时，我看到未聚合具有相同状态的连续行。

例如

timestamp status length
00:04:02 1 24
00:04:26 0 15
00:04:41 1 19
00:05:00 1 11
00:05:11 0 8
00:05:19 1 22
...

我想聚合这两行，以便使用标准 SQL 将 5 分钟包边缘的状态聚合在一起，以便上面的示例如下所示。具有相同状态位的连续行聚合为一个，并将这些连续条目的长度相加。

timestamp status length
00:04:02 1 24
00:04:26 0 15
00:04:41 1 30
00:05:11 0 8
00:05:19 1 22
...

SQL 中是否有功能根据上述结构返回此查询？

Answer 1

这是一个"间隙和孤岛"问题。 亚马逊雅典娜支持row_number()，因此您可以使用行号差分方法解决它：

select status, min(timestamp) as timestamp, sum(length) as length
from (select t.*,
row_number() over (order by timestamp) as seqnum,
row_number() over (partition by status order by timestamp) as seqnum_s
from t
) t
group by status, (seqnum - seqnum_s);