如何在Google BigQuery中基于多列识别关联记录并生成统一用户ID

问题场景 #

现有如下BigQuery表结构及数据：

WITH my_table AS (
  SELECT '2024-01-01' AS date, 1 AS session_id, 'a' AS client_id, NULL AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 2 AS session_id, 'a' AS client_id, 'z' AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 3 AS session_id, 'b' AS client_id, 'z' AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 4 AS session_id, 'c' AS client_id, 'y' AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 5 AS session_id, 'd' AS client_id, NULL AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 6 AS session_id, 'd' AS client_id, 'x' AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 7 AS session_id, 'e' AS client_id, 'x' AS customer_id
)
SELECT * FROM my_table

需要新增user_id列，将所有直接或间接共享client_id或customer_id的记录标记为同一用户（比如前3条记录共用一个user_id，后3条共用另一个）。

解决方案 #

这个问题属于连通分量识别问题，可通过递归CTE构建关联关系，最终为每个连通分量分配唯一ID。实现代码如下：

WITH my_table AS (
  SELECT '2024-01-01' AS date, 1 AS session_id, 'a' AS client_id, NULL AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 2 AS session_id, 'a' AS client_id, 'z' AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 3 AS session_id, 'b' AS client_id, 'z' AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 4 AS session_id, 'c' AS client_id, 'y' AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 5 AS session_id, 'd' AS client_id, NULL AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 6 AS session_id, 'd' AS client_id, 'x' AS customer_id
  UNION ALL
  SELECT '2024-01-01' AS date, 7 AS session_id, 'e' AS client_id, 'x' AS customer_id
),
-- 整理所有有效的关联ID对，覆盖单ID情况
id_pairs AS (
  SELECT 
    client_id AS id1, 
    customer_id AS id2
  FROM my_table
  WHERE client_id IS NOT NULL AND customer_id IS NOT NULL
  UNION ALL
  SELECT client_id AS id1, client_id AS id2
  FROM my_table
  WHERE client_id IS NOT NULL AND customer_id IS NULL
  UNION ALL
  SELECT customer_id AS id1, customer_id AS id2
  FROM my_table
  WHERE customer_id IS NOT NULL AND client_id IS NULL
),
-- 递归查找所有连通的ID
recursive_cte AS (
  SELECT 
    id1 AS root_id, 
    id1 AS connected_id
  FROM id_pairs
  UNION ALL
  SELECT 
    r.root_id, 
    p.id2 AS connected_id
  FROM recursive_cte r
  JOIN id_pairs p ON r.connected_id = p.id1
  WHERE p.id2 NOT IN (SELECT connected_id FROM recursive_cte WHERE root_id = r.root_id)
),
-- 为每个连通分量分配统一的最小root_id作为user_id
user_mapping AS (
  SELECT 
    connected_id AS user_identifier,
    MIN(root_id) AS user_id
  FROM recursive_cte
  GROUP BY connected_id
),
-- 关联回原表，处理全NULL的孤立记录
final_result AS (
  SELECT 
    t.*,
    COALESCE(
      um.user_id,
      CONCAT('isolated_', CAST(t.session_id AS STRING))
    ) AS user_id
  FROM my_table t
  LEFT JOIN user_mapping um 
    ON t.client_id = um.user_identifier 
    OR t.customer_id = um.user_identifier
)
SELECT * FROM final_result

代码说明 #

1. id_pairs：将client_id与customer_id的关联关系整理成对，同时为只有单一有效ID的记录创建自关联，确保所有有效ID都能进入连通分析。
2. recursive_cte：通过递归遍历，找出每个ID能关联到的所有其他ID，形成完整的连通分量集合。
3. user_mapping：为每个连通分量选取最小的ID作为统一user_id，保证同一组关联记录的标识唯一且一致。
4. final_result：将映射关系关联回原表，对client_id和customer_id全为NULL的孤立记录，用session_id生成唯一标识。

执行后，前3条记录的user_id为a，session_id5-7的user_id为d，session_id4的user_id为c，完全符合需求。

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