如何在Snowflake数据库中基于时间戳为同一Prospect_ID下的相近时间记录生成分组唯一ID

这是一个典型的会话分组场景，咱们可以通过Snowflake的窗口函数组合来实现需求——给同一个Prospect_ID下时间间隔在30分钟以内的连续记录分配相同的Timestamp_id。下面是具体的分步方案：

核心思路 #

1. 先计算每个记录与同Prospect_ID下上一条记录的时间间隔
2. 标记出每个分组的起始点（第一条记录或时间间隔超过30分钟的记录）
3. 通过累积求和生成唯一的分组编号，最后转换成你想要的abcX格式

完整SQL代码 #

-- 第一步：计算每条记录与上一条的时间差
WITH ranked_records AS (
    SELECT 
        Prospect_ID,
        Channel,
        Timestamp,
        -- 用LAG窗口函数获取上一条记录的时间，计算间隔（分钟）
        TIMESTAMPDIFF(MINUTE, LAG(Timestamp) OVER (PARTITION BY Prospect_ID ORDER BY Timestamp), Timestamp) AS time_interval
    FROM your_table_name -- 替换成你的实际表名
),
-- 第二步：标记分组起始点
group_starts AS (
    SELECT 
        *,
        -- 第一条记录（time_interval为NULL）或间隔>30分钟时，标记为分组起始
        CASE 
            WHEN time_interval IS NULL OR time_interval > 30 THEN 1 
            ELSE 0 
        END AS is_new_group
    FROM ranked_records
),
-- 第三步：生成分组编号
grouped_data AS (
    SELECT 
        *,
        -- 对分组起始标记做累积求和，得到每个分组的唯一编号
        SUM(is_new_group) OVER (PARTITION BY Prospect_ID ORDER BY Timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_number
    FROM group_starts
)
-- 第四步：转换为要求的Timestamp_id格式
SELECT 
    Prospect_ID,
    Channel,
    Timestamp,
    'abc' || group_number AS Timestamp_id
FROM grouped_data
ORDER BY Prospect_ID, Timestamp;

结果验证 #

运行上述SQL后，会得到你期望的输出：

完全匹配你给出的示例效果，同一个Prospect_ID下30分钟内的记录会被分配相同的Timestamp_id，超过30分钟的则开启新的分组。

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