sql - 时间窗口定义的开始/停止事件中列的总和值

Question

我正在努力在 Snowflake 中编写 SQL 窗口函数来对列中的连续值求和。

表中数据stg_events：

机器人ID	时间戳	msg_type	obj_count
1	2020-12-14 09:30:00.000	route_start	空值
1	2020-12-14 09:30:00.100	object_detected	2
1	2020-12-14 09:30:00.300	object_detected	1
1	2020-12-14 09:30:05.000	object_detected	2
1	2020-12-14 09:30:40.000	route_stop	空值

SQL语句的所需输出，我正在尝试编写：

机器人ID	route_id	route_start	route_stop	sum_obj
1	1	2020-12-14 09:30:00.000	2020-12-14 09:30:40.000	5

我只提供了一个机器人的一条路线的例子，但是会有更多的机器人将数据推送到表格中，而且还有更多的路线。

非常感谢您的任何想法！

标签： sqldatetimeaggregate-functionssnowflake-cloud-data-platformgaps-and-islands

Answer 1

假设同一个机器人可能有几条路线，单靠聚合并不能解决问题。这是一个gaps-and-island 问题，其中一个岛以“route_start”消息类型开始，以“route_stop”结束。

如果启动和停止正确交错，这是一种使用窗口函数的方法：

select robot_id, min(timestamp) as route_start, max(timestamp) as route_end, sum(obj_count) as obj_count
from (
    select t.*,
        sum(case when msg_type = 'route_start' then 1 else 0 end) over(partition by robot_id order by timestamp) as cnt_start,
        sum(case when msg_type = 'route_stop'  then 1 else 0 end) over(partition by robot_id order by timestamp rows between unbounded preceding and 1 preceding) as cnt_end
    from mytable t
) t
where cnt_start = coalesce(cnt_end, 0) + 1
group by robot_id, cnt_start

这个想法是计算开始（直到包括当前行）和停止（直到前一行）并比较这两个值以识别岛屿。剩下的只是聚合。

这是一个演示，样本数据较少：

机器人ID | 时间戳 | msg_type | obj_count
--------: | :-------------------- | :---------------- | --------:
       1 | 2020-12-14 09:30:00 | 路线开始 |      空值
       1 | 2020-12-14 09:30:00.1 | object_detected | 2
       1 | 2020-12-14 09:30:00.3 | object_detected | 1
       1 | 2020-12-14 09:30:05 | object_detected | 2
       1 | 2020-12-14 09:30:40 | 路线停止 |      空
       1 | 2020-12-15 00:30:00 | 路线开始 |      空值
       1 | 2020-12-15 00:30:05 | object_detected | 2
       1 | 2020-12-15 00:30:40 | 路线停止 |      空值

结果：

机器人ID | 路线开始 | 路线结束 | obj_count
--------: | :----------------- | :----------------- | --------:
       1 | 2020-12-14 09:30:00 | 2020-12-14 09:30:40 | 5
       1 | 2020-12-15 00:30:00 | 2020-12-15 00:30:40 | 2