如何基于数组交集聚合Session？SQL多数组关联分组需求

这个需求本质是寻找连通分量——只要两个会话共享用户或交互，就归为同一组，最终合并所有关联的会话、用户和交互。以下是具体实现步骤（以支持数组和递归CTE的SQL引擎为例，如PostgreSQL、BigQuery）：

完整SQL代码 #

WITH session_agg AS (
    -- 第一步：按会话聚合用户和交互（你的原聚合逻辑）
    SELECT 
        Session_GUID AS session_id,
        ARRAY_AGG(DISTINCT User_GUID) AS users,
        ARRAY_AGG(DISTINCT Interaction_GUID) AS interactions
    FROM source_table
    GROUP BY Session_GUID
),
user_session_links AS (
    -- 拆解用户-会话关联，每行对应一个用户和所属会话
    SELECT session_id, unnest(users) AS user_id
    FROM session_agg
),
interaction_session_links AS (
    -- 拆解交互-会话关联，每行对应一个交互和所属会话
    SELECT session_id, unnest(interactions) AS interaction_id
    FROM session_agg
),
session_connections AS (
    -- 找出所有关联的会话对：共享用户 或 共享交互
    SELECT a.session_id AS session_a, b.session_id AS session_b
    FROM user_session_links a
    JOIN user_session_links b ON a.user_id = b.user_id AND a.session_id != b.session_id
    UNION
    SELECT a.session_id AS session_a, b.session_id AS session_b
    FROM interaction_session_links a
    JOIN interaction_session_links b ON a.interaction_id = b.interaction_id AND a.session_id != b.session_id
),
recursive_groups AS (
    -- 递归CTE：标记每个会话所属的连通组（根ID）
    SELECT session_id AS root_id, session_id AS session_id
    FROM session_agg
    UNION ALL
    SELECT r.root_id, s.session_b
    FROM recursive_groups r
    JOIN session_connections s ON r.session_id = s.session_a
    WHERE s.session_b NOT IN (SELECT session_id FROM recursive_groups WHERE root_id = r.root_id)
)
-- 最终聚合：按根ID合并同组的所有会话、用户、交互
SELECT
    ARRAY_AGG(DISTINCT rg.session_id) AS session_array,
    ARRAY_AGG(DISTINCT us.user_id) AS user_guid_array,
    ARRAY_AGG(DISTINCT is_.interaction_id) AS interaction_guid_array
FROM recursive_groups rg
JOIN session_agg sa ON rg.session_id = sa.session_id
JOIN user_session_links us ON rg.session_id = us.session_id
JOIN interaction_session_links is_ ON rg.session_id = is_.session_id
GROUP BY rg.root_id
ORDER BY rg.root_id;

逻辑说明 #

1. 会话聚合：复用你已有的ARRAY_AGG逻辑，得到每个会话的用户和交互集合。
2. 拆解关联：把数组拆成单行记录，方便后续找到共享用户/交互的会话对。
3. 建立会话关联：通过用户或交互的交集，找出所有需要合并的会话对。
4. 递归标记分组：用递归CTE遍历所有关联会话，给同一连通组的会话分配相同的根ID。
5. 最终聚合：按根ID合并同组的所有会话、用户和交互，得到目标结果。

适配说明 #

如果你的SQL引擎不支持UNNEST或递归CTE（如MySQL），可以调整为：

• 用CROSS JOIN配合字符串拆分替代UNNEST（针对数组类型）；
• 用迭代的方式逐步合并关联会话，直到没有新的合并发生。

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