在Snowflake中编写SQL窗口函数实现起止事件窗口列值求和

我来帮你搞定这个需求！看起来你需要把每个机器人的每一段路线（从route_start到route_stop）里的object_detected计数加总，同时输出路线的起止时间对吧？在Snowflake里用窗口函数+分组聚合就能轻松实现，思路和代码我给你理清楚：

核心思路 #

我们需要先把同一段路线里的所有记录（包括起点、中间检测、终点）归为同一个分组，然后再对每个分组做聚合计算。这里的关键是用窗口函数给每条记录打上对应的route_id标记，确保同一路线的所有记录共享同一个ID。

具体实现代码 #

WITH route_grouped AS (
    SELECT
        robot_id,
        timestamp,
        msg_type,
        obj_count,
        -- 生成每个路线的唯一ID：每遇到一个route_start，计数加1，同一路线的所有记录会得到相同的ID
        COUNT(CASE WHEN msg_type = 'route_start' THEN 1 END) OVER (
            PARTITION BY robot_id 
            ORDER BY timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS route_id
    FROM stg_events
)
SELECT
    robot_id,
    route_id,
    -- 提取该路线的起始时间（每个分组只有一条route_start记录，MAX能精准拿到对应值）
    MAX(CASE WHEN msg_type = 'route_start' THEN timestamp END) AS route_start,
    -- 提取该路线的结束时间
    MAX(CASE WHEN msg_type = 'route_stop' THEN timestamp END) AS route_stop,
    -- 对该路线下的object_detected计数求和，非检测记录计为0
    SUM(CASE WHEN msg_type = 'object_detected' THEN obj_count ELSE 0 END) AS sum_obj
FROM route_grouped
GROUP BY robot_id, route_id
ORDER BY robot_id, route_id;

代码细节解释 #

1. CTE阶段（route_grouped）：

   • 用PARTITION BY robot_id保证每个机器人的路线独立计数，不会和其他机器人的路线混淆
   • 按timestamp排序后，COUNT(CASE...)窗口函数会累计遇到的route_start数量，这样从某个route_start到下一个route_start之前的所有记录（包括对应的route_stop）都会被标记为同一个route_id

2. 主查询聚合阶段：

   • 按robot_id和route_id分组，把同一路线的所有记录合并
   • 用MAX(CASE...)提取路线的起止时间（每个分组里只有一条route_start和一条route_stop，MAX能精准拿到对应值）
   • 用SUM(CASE...)只对object_detected类型的记录求和，其他类型记录的计数按0处理

测试示例数据 #

把你给出的单机器人单路线数据代入，这个SQL会输出你期望的结果：

robot_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

扩展场景适配 #

如果有多个机器人，或者一个机器人有多条连续路线（比如robot_id=1有route_start1→route_stop1，接着又有route_start2→route_stop2），这个代码也能正确分组：PARTITION BY robot_id会隔离不同机器人的路线，COUNT(...)会为每个机器人的每条路线生成递增的route_id，完全适配你的实际业务场景。

内容的提问来源于stack exchange，提问作者JKC