Snowflake中如何计算特定后续行子集的时间差

你可以通过LEAD()窗口函数结合条件筛选来精准获取符合要求的时间差，避免无效计算。以下是适配Snowflake的SQL代码：

WITH ranked_data AS (
    SELECT 
        DEVICE_SERIAL,
        VERSION,
        REASON_CODE,
        MESSAGE_CREATED_AT,
        -- 获取同一设备下，下一行的故障码和时间戳
        LEAD(REASON_CODE) OVER (PARTITION BY DEVICE_SERIAL ORDER BY MESSAGE_CREATED_AT) AS next_reason_code,
        LEAD(MESSAGE_CREATED_AT) OVER (PARTITION BY DEVICE_SERIAL ORDER BY MESSAGE_CREATED_AT) AS next_created_at
    FROM your_previous_query_result -- 替换成你的前置查询语句或表名
)
SELECT 
    DEVICE_SERIAL,
    VERSION,
    DATEDIFF(SECOND, MESSAGE_CREATED_AT, next_created_at) AS DELTA_SECONDS
FROM ranked_data
-- 筛选符合条件的行对：当前行故障码为1/5，下一行故障码为4
WHERE REASON_CODE IN (1, 5) 
  AND next_reason_code = 4;

代码说明 #

• 分区与排序：PARTITION BY DEVICE_SERIAL确保仅在同一设备内处理相邻行，ORDER BY MESSAGE_CREATED_AT保证行按时间顺序排列，这是获取正确相邻行的核心前提。
• LEAD()函数：分别提取下一行的故障码和时间戳，为后续条件判断和时间差计算提供数据。
• 精准筛选：通过WHERE子句直接过滤出符合要求的行对，只对有效数据进行计算，避免不必要的运算开销。
• 时间差计算：利用Snowflake内置的DATEDIFF()函数直接计算秒级时间差，完全匹配你的输出需求。

如果需要进一步分析不同VERSION下的时间差变化，可以基于上述结果做聚合统计，示例如下：

-- 按VERSION分组统计时间差的均值、极值与样本量
SELECT 
    VERSION,
    AVG(DELTA_SECONDS) AS avg_delta,
    MAX(DELTA_SECONDS) AS max_delta,
    MIN(DELTA_SECONDS) AS min_delta,
    COUNT(*) AS sample_count
FROM (
    WITH ranked_data AS (
        SELECT 
            DEVICE_SERIAL,
            VERSION,
            REASON_CODE,
            MESSAGE_CREATED_AT,
            LEAD(REASON_CODE) OVER (PARTITION BY DEVICE_SERIAL ORDER BY MESSAGE_CREATED_AT) AS next_reason_code,
            LEAD(MESSAGE_CREATED_AT) OVER (PARTITION BY DEVICE_SERIAL ORDER BY MESSAGE_CREATED_AT) AS next_created_at
        FROM your_previous_query_result
    )
    SELECT 
        DEVICE_SERIAL,
        VERSION,
        DATEDIFF(SECOND, MESSAGE_CREATED_AT, next_created_at) AS DELTA_SECONDS
    FROM ranked_data
    WHERE REASON_CODE IN (1, 5) 
      AND next_reason_code = 4
) AS valid_deltas
GROUP BY VERSION
ORDER BY VERSION;

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