Snowflake中实现7天内重复呼叫的分段排名与首尾标签生成

核心思路 #

要实现REPEAT_CALLS_7_DAYS_FLAG为0时重置排名，不能直接用RANK()函数，需先通过累计求和生成序列组ID，再在组内进行排名；首尾标识则通过判断当前记录在组内的位置实现。

完整SQL示例 #

假设你的原始表名为customer_calls，包含customer_id、call_time、REPEAT_CALLS_7_DAYS_FLAG字段，执行以下SQL：

WITH call_groups AS (
    SELECT
        customer_id,
        call_time,
        REPEAT_CALLS_7_DAYS_FLAG,
        -- 生成序列组ID：每次FLAG为0时组ID递增，同一7天序列的组ID一致
        SUM(CASE WHEN REPEAT_CALLS_7_DAYS_FLAG = 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY customer_id 
            ORDER BY call_time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS call_sequence_group
    FROM customer_calls
),
ranked_calls AS (
    SELECT
        *,
        -- 组内生成排名，FLAG为0时组ID变化，排名自动重置为1
        ROW_NUMBER() OVER (
            PARTITION BY customer_id, call_sequence_group 
            ORDER BY call_time
        ) AS Repeat_calls_rank,
        -- 计算组内总记录数，用于判断是否为序列末尾
        COUNT(*) OVER (PARTITION BY customer_id, call_sequence_group) AS group_total
    FROM call_groups
)
SELECT
    customer_id,
    call_time,
    REPEAT_CALLS_7_DAYS_FLAG,
    Repeat_calls_rank,
    -- 标识序列首尾：单条记录标记'首尾'，否则对应'首'/'尾'
    CASE
        WHEN Repeat_calls_rank = 1 AND Repeat_calls_rank = group_total THEN '首尾'
        WHEN Repeat_calls_rank = 1 THEN '首'
        WHEN Repeat_calls_rank = group_total THEN '尾'
        ELSE ''
    END AS tag
FROM ranked_calls
ORDER BY customer_id, call_time;

关键步骤说明 #

• 生成序列组ID：利用SUM() OVER()窗口函数，按customer_id分区、call_time排序，累计统计REPEAT_CALLS_7_DAYS_FLAG=0的次数。每出现一次0，组ID加1，确保同一个7天内的呼叫序列被归为同一组。
• 重置排名：在customer_id和call_sequence_group的联合分区内使用ROW_NUMBER()，每个新组的排名从1开始，实现FLAG为0时的排名重置需求。
• 首尾标识：通过对比当前排名与组内总记录数，判断是否为组内第一条或最后一条记录，生成对应的tag值。

补充说明 #

如果REPEAT_CALLS_7_DAYS_FLAG尚未计算，可先添加以下逻辑生成该字段：

LAG(call_time) OVER (PARTITION BY customer_id ORDER BY call_time) AS prev_call_time,
CASE
    WHEN DATEDIFF(DAY, prev_call_time, call_time) <= 7 THEN 1
    ELSE 0
END AS REPEAT_CALLS_7_DAYS_FLAG

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