Snowflake中lag()函数的offset能否实现条件逻辑？

要实现这个需求，不能直接在lag()的offset里加条件逻辑，但可以通过分组连续相同destination的记录，再结合分组统计来达成目标。以下是具体的SQL实现步骤：

步骤1：给连续相同的destination打分组标签 #

先用窗口函数标记出连续相同destination的分组，把连续的outgoing记录归为一组：

WITH grouped_messages AS (
    SELECT 
        *,
        -- 当前行`destination`和上一行不同时，分组号+1；否则继承上一组号
        SUM(CASE WHEN destination = LAG(destination) OVER (PARTITION BY messageable_id ORDER BY sent_at) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY messageable_id ORDER BY sent_at) AS dest_group
    FROM your_table_name
)

步骤2：统计每个分组的核心信息 #

计算每个分组的总记录数，以及组内最早的sent_at值：

, group_stats AS (
    SELECT 
        dest_group,
        messageable_id,
        COUNT(*) AS group_count,
        MIN(sent_at) AS earliest_sent_at
    FROM grouped_messages
    GROUP BY dest_group, messageable_id
)

步骤3：关联分组统计，获取目标sent_at并计算响应时间 #

把原表和分组统计结果关联，通过条件判断得到目标时间，最后计算响应时长：

WITH grouped_messages AS (
    SELECT 
        *,
        SUM(CASE WHEN destination = LAG(destination) OVER (PARTITION BY messageable_id ORDER BY sent_at) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY messageable_id ORDER BY sent_at) AS dest_group
    FROM your_table_name
),
group_stats AS (
    SELECT 
        dest_group,
        messageable_id,
        COUNT(*) AS group_count,
        MIN(sent_at) AS earliest_sent_at
    FROM grouped_messages
    GROUP BY dest_group, messageable_id
)
SELECT 
    gm.*,
    -- 确定对比用的目标时间
    CASE 
        WHEN gm.destination = 'outgoing' AND gs.group_count >=3 THEN gs.earliest_sent_at
        ELSE LAG(gm.sent_at) OVER (PARTITION BY gm.messageable_id ORDER BY gm.sent_at)
    END AS target_sent_at,
    -- 计算响应时间（单位：秒，可根据需求调整DATEDIFF的参数）
    DATEDIFF(SECOND, 
        CASE 
            WHEN gm.destination = 'outgoing' AND gs.group_count >=3 THEN gs.earliest_sent_at
            ELSE LAG(gm.sent_at) OVER (PARTITION BY gm.messageable_id ORDER BY gm.sent_at)
        END, 
        gm.sent_at
    ) AS response_time_seconds
FROM grouped_messages gm
JOIN group_stats gs 
    ON gm.dest_group = gs.dest_group 
    AND gm.messageable_id = gs.messageable_id
ORDER BY gm.messageable_id, gm.sent_at;

逻辑说明 #

• 第一步的dest_group会给连续相同的destination记录分配同一个组号，比如连续3条outgoing会被归为同一组。
• 第二步统计每组的大小和最早发送时间，为后续的条件判断提供依据。
• 第三步通过CASE语句实现需求逻辑：当连续3条及以上outgoing时取组内最早时间，否则取上一条记录的发送时间，最后计算出响应时长。

你只需把your_table_name替换成实际表名，即可测试运行。

内容的提问来源于stack exchange，提问作者Elogene Karl Gallos