如何在Snowflake中计算同一ContactID下时间字段的毫秒级差值？

现有一张包含ContactID、Name、Value字段的表，每个ContactID对应多条不同Name的记录，示例数据如下：

需要计算每个ContactID下camp_Start与camp_End、Tkt_Start与Tkt_End的毫秒级时间差，分别标记为Camp和Tkt，期望输出如下：

尝试了以下SQL语句，但出现SQL compilation error: ambiguous column name 'NAME'的歧义错误：

SELECT a.ContactID, b.Name, (a.value - b.value) Duration
FROM "PROD"."MART"."KINETIC_NICE_COMPLETED_CONTACTS_CUSTOM_DATA" a
JOIN "PROD"."MART"."KINETIC_NICE_COMPLETED_CONTACTS_CUSTOM_DATA" b
ON a.ContactID = b.contactid AND a.name = b.name
Where ( name LIKE 'gv_%end' OR name LIKE 'gv_%start' )
AND contactID = '460576582924'
AND  DF_CREATE_DT >= '03/08/2023'
AND  DF_CREATE_DT < '03/09/2023';

请问在Snowflake中是否有简便的实现方法？

错误原因 #

你遇到的歧义错误是因为WHERE子句中的name、contactID、DF_CREATE_DT未指定表别名（a或b），数据库无法确定字段来源。同时JOIN条件a.name = b.name逻辑错误——要关联的是同个ContactID下的Start和End记录，而非同名记录。

简便实现方法：条件聚合 #

在Snowflake中，无需自连接，用条件聚合即可直接计算时间差，逻辑更简洁：

WITH time_data AS (
    SELECT 
        ContactID,
        -- 提取类型前缀（Camp/Tkt）
        LEFT(Name, CHARINDEX('_', Name) - 1) AS Type,
        -- 将Value转换为带微秒的时间戳
        CASE 
            WHEN Name LIKE '%_Start' THEN TO_TIMESTAMP(Value, 'YYYY-MM-DD HH24:MI:SS:FF5') 
            WHEN Name LIKE '%_End' THEN TO_TIMESTAMP(Value, 'YYYY-MM-DD HH24:MI:SS:FF5')
        END AS TimeStampVal
    FROM "PROD"."MART"."KINETIC_NICE_COMPLETED_CONTACTS_CUSTOM_DATA"
    WHERE 
        (Name LIKE 'camp_%' OR Name LIKE 'Tkt_%')
        AND ContactID = '460576582924'
        AND DF_CREATE_DT >= '2023-03-08'
        AND DF_CREATE_DT < '2023-03-09'
)
SELECT 
    ContactID,
    Type AS Name,
    -- 计算毫秒差并转为秒级小数
    TIMESTAMPDIFF(MILLISECOND, MIN(CASE WHEN Name LIKE '%_Start' THEN TimeStampVal END), MAX(CASE WHEN Name LIKE '%_End' THEN TimeStampVal END)) / 1000 AS Value
FROM time_data
GROUP BY ContactID, Type
-- 过滤缺失Start或End的无效记录
HAVING MIN(CASE WHEN Name LIKE '%_Start' THEN TimeStampVal END) IS NOT NULL 
   AND MAX(CASE WHEN Name LIKE '%_End' THEN TimeStampVal END) IS NOT NULL;

备选方法：PIVOT转列计算 #

如果偏好更直观的列结构，可先用Snowflake的PIVOT功能将Start/End转为列，再计算差值：

WITH pivoted_data AS (
    SELECT 
        ContactID,
        "camp_Start",
        "camp_End",
        "Tkt_Start",
        "Tkt_End"
    FROM "PROD"."MART"."KINETIC_NICE_COMPLETED_CONTACTS_CUSTOM_DATA"
    PIVOT(
        MAX(TO_TIMESTAMP(Value, 'YYYY-MM-DD HH24:MI:SS:FF5')) FOR Name IN ('camp_Start', 'camp_End', 'Tkt_Start', 'Tkt_End')
    ) AS p
    WHERE 
        ContactID = '460576582924'
        AND DF_CREATE_DT >= '2023-03-08'
        AND DF_CREATE_DT < '2023-03-09'
)
-- 展开为期望的行格式
SELECT 
    ContactID,
    'Camp' AS Name,
    TIMESTAMPDIFF(MILLISECOND, "camp_Start", "camp_End") / 1000 AS Value
FROM pivoted_data
WHERE "camp_Start" IS NOT NULL AND "camp_End" IS NOT NULL
UNION ALL
SELECT 
    ContactID,
    'Tkt' AS Name,
    TIMESTAMPDIFF(MILLISECOND, "Tkt_Start", "Tkt_End") / 1000 AS Value
FROM pivoted_data
WHERE "Tkt_Start" IS NOT NULL AND "Tkt_End" IS NOT NULL;

关键说明 #

• 首先需用TO_TIMESTAMP将Value转换为Snowflake支持的时间戳类型，指定匹配的格式（示例中为YYYY-MM-DD HH24:MI:SS:FF5，对应5位小数的毫秒）。
• 用TIMESTAMPDIFF(MILLISECOND, start, end)计算毫秒差，除以1000后得到与期望输出一致的秒级小数结果。
• 两种方法均过滤了缺失Start或End的记录，避免无效计算。

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