如何在Snowflake中实现每个ID一行的会话页面流转展示

要将每个会话ID的首、次访问页面合并到单行展示，你需要对会话ID进行分组，并用聚合函数提取对应位置的页面标题。原代码的问题在于仅通过IFF标记了首/次页面，但未做聚合，导致每个页面交互都单独成一行。

修改后的SQL如下：

SELECT
    id,
    MAX(first) AS first_page,
    MAX(second) AS second_page,
    -- 可选：将后续页面用逗号拼接展示
    STRING_AGG(CASE WHEN interaction_number > 2 THEN page_title END, ', ') AS subsequent_pages
FROM (
    SELECT
        CONCAT(user_pseudo_id, ga_session_id) AS id,
        page_title,
        ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) AS interaction_number,
        IFF(interaction_number = 1, page_title, '') AS first,
        IFF(interaction_number = 2, page_title, '') AS second
    FROM CDP_GA4_RAW.RAW_EVENTS_PIVOT
    WHERE CONCAT(user_pseudo_id, ga_session_id) = '1000002360.16826696111682669619'
    GROUP BY page_title, event_timestamp, user_pseudo_id, ga_session_id, ga_session_number
) AS interaction_details
GROUP BY id
ORDER BY id;

关键修改说明： #

• 外层通过GROUP BY id对每个会话分组，确保每个会话仅返回一行结果
• 使用MAX(first)和MAX(second)提取有效页面标题：只有对应交互顺序的行有非空值，聚合后会保留目标页面
• STRING_AGG部分为可选功能，可将后续所有访问页面拼接成字符串展示，按需保留
• 修正了原WHERE子句的字段引用问题：内层查询未定义id别名，需替换为完整的拼接表达式

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