BigQuery中基于关联ID分区：处理重复手机号及空值的RID合并

场景说明 #

在BigQuery环境中存在用户重复注册问题：同一手机号（pn）对应多个注册ID（rid），业务规则要求同一pn应归属同一rid，但pn字段不可靠，存在空值。需实现分区逻辑，将同一pn（含关联空值的情况）的用户合并为同一rid。

示例实现代码 #

WITH a AS (
  SELECT 1 AS dt, 1 AS rid, 1 AS pn, 1 AS cnt
  UNION ALL 
  SELECT 2 AS dt, 1 AS rid, 1 AS pn, 1 AS cnt
  UNION ALL
  SELECT 2 AS dt, 2 AS rid, 1 AS pn, 1 AS cnt
  UNION ALL 
  SELECT 3 AS dt, 2 AS rid, 1 AS pn, 1 AS cnt
  UNION ALL 
  SELECT 3 AS dt, 2 AS rid, NULL AS pn, 1 AS cnt
)
SELECT dt, 
       COALESCE(MIN(rid) OVER (PARTITION BY pn), rid) AS rid, 
       pn, 
       SUM(cnt) AS cnt
FROM a 
GROUP BY dt, pn, rid

预期执行结果 #

逻辑说明 #

• 用CTE a 模拟包含重复注册、空值pn的业务数据
• 通过窗口函数 MIN(rid) OVER (PARTITION BY pn) 提取同一pn下的最小rid作为统一归属ID
• 用COALESCE处理pn为空的情况，保留原rid
• 最后按日期dt、手机号pn、注册IDrid分组，统计cnt的总和

内容的提问来源于stack exchange，提问作者Herman Wilén