如何在Snowflake中筛选每2分钟内的首次有效通话记录？

在Snowflake中处理单日来电数据集，需要实现以下规则：

• 保留每个用户的首次通话记录
• 排除该记录之后2分钟内的所有来电
• 若后续来电与最近保留的记录间隔超过2分钟，则保留该来电，并继续排除其后续2分钟内的来电

当前通过Python迭代实现该逻辑，希望找到Snowflake原生的高效SQL替代方案。

示例数据 #

输入数据 #

期望输出 #

推荐两种高效实现方式，根据数据规模选择：

方案1：纯窗口函数实现（性能最优） #

适合大规模数据集，利用窗口函数的累积计算标记有效组，仅保留每组第一条记录：

WITH sorted_calls AS (
    -- 按用户+通话时间排序，转换为时间戳类型
    SELECT 
        user,
        TO_TIMESTAMP(Call_Date) AS call_ts,
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY TO_TIMESTAMP(Call_Date)) AS rn
    FROM your_call_table
),
valid_call_groups AS (
    SELECT 
        user,
        call_ts,
        rn,
        -- 累积标记有效组：当前记录晚于上一个有效组截止时间则新建组
        SUM(CASE 
            WHEN rn = 1 THEN 1
            ELSE CASE WHEN call_ts > DATEADD(MINUTE, 2, LAG(valid_until) OVER (PARTITION BY user ORDER BY rn)) THEN 1 ELSE 0 END
        END) OVER (PARTITION BY user ORDER BY rn) AS group_id,
        -- 每组的有效截止时间（当前记录+2分钟）
        DATEADD(MINUTE, 2, call_ts) AS valid_until
    FROM sorted_calls
)
-- 保留每个有效组的第一条记录
SELECT 
    user,
    TO_VARCHAR(call_ts, 'MM/DD/YYYY HH24:MI:SS') AS "Call Date"
FROM valid_call_groups
QUALIFY ROW_NUMBER() OVER (PARTITION BY user, group_id ORDER BY call_ts) = 1
ORDER BY user, call_ts;

方案2：递归CTE实现（逻辑清晰） #

适合中小规模数据集，通过递归追踪每个用户的有效记录链：

WITH sorted_calls AS (
    -- 按用户+通话时间排序，添加序号
    SELECT 
        user,
        TO_TIMESTAMP(Call_Date) AS call_ts,
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY TO_TIMESTAMP(Call_Date)) AS rn
    FROM your_call_table
),
recursive_valid_calls AS (
    -- 递归起始：每个用户的第一条记录
    SELECT 
        user,
        call_ts,
        rn
    FROM sorted_calls
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归步骤：找到下一条晚于上一条有效记录+2分钟的最早记录
    SELECT 
        sc.user,
        sc.call_ts,
        sc.rn
    FROM sorted_calls sc
    JOIN recursive_valid_calls rvc 
        ON sc.user = rvc.user
        AND sc.rn > rvc.rn
        AND sc.call_ts > DATEADD(MINUTE, 2, rvc.call_ts)
    QUALIFY ROW_NUMBER() OVER (PARTITION BY sc.user, rvc.rn ORDER BY sc.call_ts) = 1
)
SELECT 
    user,
    TO_VARCHAR(call_ts, 'MM/DD/YYYY HH24:MI:SS') AS "Call Date"
FROM recursive_valid_calls
ORDER BY user, call_ts;

1. 确保Call Date字段通过TO_TIMESTAMP转换为时间戳，避免字符串比较的误差
2. 时间间隔可通过DATEADD(MINUTE, N, ...)中的N调整，若需包含2分钟整的记录，将>改为>=即可
3. 大规模数据优先选择窗口函数方案，Snowflake对窗口函数的并行优化更高效

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