Snowflake基于Partitioning的特定Event记录统计SQL查询求助

业务记录结构 #

查询需求 #

• 仅获取login_name为null且login_type='event'的记录
• 按login_id、page_id进行分区，根据login_timestamp排序，统计该分区内login_type='page'记录之前和之后的符合条件的event记录数量

示例输出 #

可以通过窗口函数结合子查询实现需求，Snowflake SQL语句如下：

WITH page_events AS (
    -- 提取各分区内page记录的时间戳
    SELECT 
        login_id,
        page_id,
        login_timestamp AS page_timestamp
    FROM your_table_name
    WHERE login_type = 'page'
),
qualified_events AS (
    -- 筛选符合条件的event记录
    SELECT 
        login_id,
        login_type,
        login_name,
        login_timestamp,
        page_id
    FROM your_table_name
    WHERE login_type = 'event' AND login_name IS NULL
),
event_counts AS (
    -- 计算page记录前后的符合条件event数量
    SELECT 
        qe.login_id,
        qe.login_type,
        qe.login_name,
        qe.page_id,
        -- 统计page记录之前的符合条件event数
        SUM(CASE WHEN qe.login_timestamp < pe.page_timestamp THEN 1 ELSE 0 END) OVER (PARTITION BY qe.login_id, qe.page_id) AS count_of_event_before_page,
        -- 统计page记录之后的符合条件event数
        SUM(CASE WHEN qe.login_timestamp > pe.page_timestamp THEN 1 ELSE 0 END) OVER (PARTITION BY qe.login_id, qe.page_id) AS count_of_event_afer_page
    FROM qualified_events qe
    JOIN page_events pe 
        ON qe.login_id = pe.login_id AND qe.page_id = pe.page_id
)
-- 去重得到最终结果
SELECT DISTINCT
    login_id,
    login_type,
    login_name,
    count_of_event_before_page,
    count_of_event_afer_page,
    page_id
FROM event_counts
ORDER BY login_id, page_id;

思路说明 #

1. page_events CTE：单独提取所有login_type='page'的记录，获取每个login_id+page_id分区对应的页面时间戳。
2. qualified_events CTE：筛选出需求指定的目标event记录（login_name为null且login_type='event'）。
3. event_counts CTE：将目标event记录与对应分区的page记录关联，通过窗口函数SUM()结合CASE逻辑，分别统计每个分区内page时间戳前后的目标event数量。
4. 最终查询：用DISTINCT去重，得到每个分区唯一的统计结果，与示例输出一致。

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