Snowflake SQL中按连续节点分组求时间范围及时长的实现问题

这是典型的**间隙和岛屿（Gaps and Islands）**问题，核心是通过计算分组标识区分同一Node非连续出现的不同组，具体实现如下：

实现思路 #

1. 按Person分组，对每条记录按TS升序生成全局行号；
2. 按Person+Node分组生成组内行号，用全局行号减去组内行号，得到的分组标识值相同的即为连续的同一Node组；
3. 按Person、Node和分组标识分组，计算每组的最小时间、最大时间及时长，同时处理单条记录的特殊情况。

完整SQL代码 #

-- 模拟测试数据
WITH test_data AS (
    SELECT TO_TIMESTAMP('2022-11-29 12:01:00 AM') AS TS, 1 AS Node, 'Adam' AS Person UNION ALL
    SELECT TO_TIMESTAMP('2022-11-29 12:02:00 AM') AS TS, 1 AS Node, 'Adam' AS Person UNION ALL
    SELECT TO_TIMESTAMP('2022-11-29 12:03:00 AM') AS TS, 1 AS Node, 'Adam' AS Person UNION ALL
    SELECT TO_TIMESTAMP('2022-11-29 12:04:00 AM') AS TS, 2 AS Node, 'Adam' AS Person UNION ALL
    SELECT TO_TIMESTAMP('2022-11-29 12:05:00 AM') AS TS, 2 AS Node, 'Adam' AS Person UNION ALL
    SELECT TO_TIMESTAMP('2022-11-29 12:06:00 AM') AS TS, 1 AS Node, 'Adam' AS Person
),
-- 生成分组标识
grouped_data AS (
    SELECT 
        TS,
        Node,
        Person,
        -- 全局行号与组内行号的差值作为分组标识
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY TS) 
        - ROW_NUMBER() OVER (PARTITION BY Person, Node ORDER BY TS) AS group_id
    FROM test_data
)
-- 统计每组结果
SELECT 
    Person,
    Node,
    MIN(TS) AS "Min TS",
    -- 组内仅一条记录时Max TS设为Null
    CASE WHEN COUNT(*) > 1 THEN MAX(TS) ELSE NULL END AS "Max TS",
    -- 计算时长（分钟差），单条记录时为Null
    CASE WHEN COUNT(*) > 1 THEN TIMESTAMPDIFF(MINUTE, MIN(TS), MAX(TS)) ELSE NULL END AS Elapsed
FROM grouped_data
GROUP BY Person, Node, group_id
ORDER BY "Min TS";

执行结果 #

执行后将得到与期望一致的输出：

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