BigQuery中LAG()函数未返回上一组会话ID问题排查

我在BigQuery中运行以下查询：

WITH mock AS (
        SELECT 
            TIMESTAMP '2024-04-01 22:26:25' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'session start' AS event_name,
            1712010385 AS ga_session_id,
            'example.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:26:28' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'view' AS event_name,
            1712010385 AS ga_session_id,
            'example.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:26:31' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'click' AS event_name,
            1712010385 AS ga_session_id,
            'example.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:26:50' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'click' AS event_name,
            1712010385 AS ga_session_id,
            'example.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:27:02' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'session start' AS event_name,
            2939469694 AS ga_session_id,
            'example2.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:27:14' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'view' AS event_name,
            2939469694 AS ga_session_id,
            'example2.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:27:50' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'click' AS event_name,
            2939469694 AS ga_session_id,
            'example2.com' AS hostname
    ),
    
    raw AS (
        SELECT 
            event_timestamp,
            user_pseudo_id,
            event_name,
            hostname,
            ga_session_id,
            MIN(event_timestamp) OVER (PARTITION BY user_pseudo_id, ga_session_id) AS session_start
        FROM mock
    )
    
    SELECT 
        *, 
        LAG(ga_session_id) OVER (PARTITION BY user_pseudo_id ORDER BY session_start) AS ga_session_id_fix
    FROM raw
    ORDER BY event_timestamp ASC;

得到如下输出：

为何最后两行的ga_session_id_fix列未返回第一个会话的ID？

原因分析 #

当前查询中使用的LAG(ga_session_id) OVER (PARTITION BY user_pseudo_id ORDER BY session_start)是逐行取上一行的ga_session_id值，而非按会话分组取上一个会话的ID：

• 第5行属于第二个会话的第一行，它的上一行是第一个会话的最后一行（第4行），所以返回第一个会话的ID；
• 第6行的上一行是第5行（同属第二个会话），所以返回当前会话的ID；
• 第7行同理，上一行是第6行，返回当前会话的ID。

如果需求是让同一个会话的所有行都关联前一个会话的ID，需要先按会话维度计算前一个会话ID，再关联回原始事件数据。

修正后的查询 #

WITH mock AS (
        SELECT 
            TIMESTAMP '2024-04-01 22:26:25' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'session start' AS event_name,
            1712010385 AS ga_session_id,
            'example.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:26:28' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'view' AS event_name,
            1712010385 AS ga_session_id,
            'example.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:26:31' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'click' AS event_name,
            1712010385 AS ga_session_id,
            'example.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:26:50' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'click' AS event_name,
            1712010385 AS ga_session_id,
            'example.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:27:02' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'session start' AS event_name,
            2939469694 AS ga_session_id,
            'example2.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:27:14' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'view' AS event_name,
            2939469694 AS ga_session_id,
            'example2.com' AS hostname
        UNION ALL
        SELECT 
            TIMESTAMP '2024-04-01 22:27:50' AS event_timestamp,
            'h6p2ylt679me5pcz' AS user_pseudo_id,
            'click' AS event_name,
            2939469694 AS ga_session_id,
            'example2.com' AS hostname
    ),
    
    raw AS (
        SELECT 
            event_timestamp,
            user_pseudo_id,
            event_name,
            hostname,
            ga_session_id,
            MIN(event_timestamp) OVER (PARTITION BY user_pseudo_id, ga_session_id) AS session_start
        FROM mock
    ),
    
    session_level AS (
        SELECT 
            user_pseudo_id,
            ga_session_id,
            session_start,
            LAG(ga_session_id) OVER (PARTITION BY user_pseudo_id ORDER BY session_start) AS prev_session_id
        FROM raw
        GROUP BY user_pseudo_id, ga_session_id, session_start
    )
    
    SELECT 
        r.*,
        s.prev_session_id AS ga_session_id_fix
    FROM raw r
    LEFT JOIN session_level s 
        ON r.user_pseudo_id = s.user_pseudo_id 
        AND r.ga_session_id = s.ga_session_id
    ORDER BY r.event_timestamp ASC;

修正后的输出 #

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