在Snowflake中统计各用户首次事件24小时内的事件数量

我在使用Snowflake处理一个需求，需要统计每个user_id首次事件发生后24小时内的事件数量。以下是简化后的数据库表片段（仅保留日期部分）：

第一步：获取每个user_id的最小事件时间 #

我先执行以下SQL获取每个user_id的首次事件时间：

SELECT user_id, client_event_time,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY client_event_time) row_number,
       MIN(client_event_time) OVER (PARTITION BY user_id) MinEventTime
FROM Data
ORDER BY user_id, client_event_time;

得到结果：

第二步：尝试统计24小时内事件数（出现错误） #

接着我用以下SQL尝试计算事件时间与首次事件时间的差值，统计差值≤24小时的事件数：

with NewTable as (
        (SELECT user_id,client_event_time, event_type,
        row_number() over (partition by user_id order by CLIENT_EVENT_TIME) row_number,
        MIN(client_event_time) OVER (PARTITION BY user_id) MinEventTime
        FROM Data
        ORDER BY user_id, client_event_time))
    
SELECT user_id,  
        COUNT(case when timestampdiff(hh, client_event_time, MinEventTime) <= 24  then 1 else 0 end) AS duration
FROM    NEWTABLE
GROUP BY user_id

但得到的结果不符合预期：

期望结果 #

我期望的正确结果是：

问题分析与解决方案 #

错误原因：

1. TIMESTAMPDIFF参数顺序错误：Snowflake中TIMESTAMPDIFF(时间单位, 起始时间, 结束时间)，你写的timestampdiff(hh, client_event_time, MinEventTime)是计算从当前事件时间到首次事件时间的差值，而首次事件时间更早，所以差值为负数，负数必然≤24，导致所有行都被计数。
2. COUNT函数的使用问题：COUNT会统计所有非NULL值，即使CASE返回0也会被计数，应该改用SUM（统计1的数量），或者让CASE在不满足条件时返回NULL（COUNT会忽略NULL）。

正确SQL：

WITH user_event_details AS (
    SELECT 
        user_id,
        client_event_time,
        MIN(client_event_time) OVER (PARTITION BY user_id) AS first_event_time
    FROM Data
)
SELECT 
    user_id,
    SUM(CASE 
            WHEN TIMESTAMPDIFF(HOUR, first_event_time, client_event_time) <= 24 
            THEN 1 
            ELSE 0 
        END) AS duration
FROM user_event_details
GROUP BY user_id;

或者用COUNT简化写法：

WITH user_event_details AS (
    SELECT 
        user_id,
        client_event_time,
        MIN(client_event_time) OVER (PARTITION BY user_id) AS first_event_time
    FROM Data
)
SELECT 
    user_id,
    COUNT(CASE 
            WHEN TIMESTAMPDIFF(HOUR, first_event_time, client_event_time) <= 24 
            THEN 1 
        END) AS duration
FROM user_event_details
GROUP BY user_id;

验证结果：

执行上述SQL后，会得到期望的结果：

• user_id 1的首次事件是2022-07-28，2022-07-29与首次事件的间隔为24小时，符合条件，共2个事件；
• user_id 2的首次事件是2022-07-29，2022-07-30与首次事件的间隔为24小时，符合条件，共2个事件。

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