用户ID不统一，手机号/设备号/账号怎么打通？——ID Mapping实战

-- 规则匹配：通过手机号打通设备ID和账号ID
 
-- Step 1: 构建ID关系表（从各数据源采集ID对应关系）
CREATE TABLE dwd_id_relation AS
SELECT
  phone_number,
  device_id,
  app_user_id,
  data_source,          -- 来自哪个系统：app_register/device_binddata
  event_time,           -- 关联发生的时间
  ROW_NUMBER() OVER(
    PARTITION BY phone_number
    ORDER BY event_time DESC
  ) AS rn               -- 取最新的关联关系
FROM (
  SELECT phone, device_id, user_id AS app_user_id,
    'app_register' AS data_source, register_time AS event_time
  FROM ods_app_register
  UNION ALL
  SELECT phone, device_id, NULL AS app_user_id,
    'device_bind' AS data_source, bind_time AS event_time
  FROM ods_device_binddata
) t;

-- Step 2: 生成OneID映射表
CREATE TABLE dim_oneid_mapping AS
SELECT
  COALESCE(app_user_id, phone_number, device_id) AS one_id,  -- 优先用账号ID
  phone_number,
  device_id,
  app_user_id,
  event_time AS last_binddata_time
FROM dwd_id_relation
WHERE rn = 1;                                                -- 取最新关联
 
-- Step 3: 用OneID关联标签
SELECT m.one_id, p.*
FROM ads_user_profile p
JOIN dim_oneid_mapping m ON p.device_id = m.device_id;

适用场景：ID类型少（2-3种）、用户量百万级、有明确的桥梁ID（手机号/邮箱）。

局限性：当关系链变长时（A设备→手机号→B设备→邮箱→C账号），简单JOIN处理不了多跳关联。

方案二：图计算——大规模ID打通的利器

当ID关系变得复杂时（多种ID类型、多跳关联），需要用图计算找连通分量。把每个ID看作图的节点，两个ID共现看作边，同一个连通分量内的所有ID属于同一个人。

# 基于Spark GraphX / GraphFrames的ID Mapping
from graphframes import GraphFrame
from pyspark.sql import functions as F
 
# 构建边：两个ID在同一条记录中出现，就建一条边
edges = spark.sql("""
  SELECT phone_number AS src, device_id AS dst FROM dwd_id_relation
  UNION ALL
  SELECT phone_number AS src, app_user_id AS dst FROM dwd_id_relation
  WHERE app_user_id IS NOT NULL
""")
 
# 构建顶点（所有出现过的ID）
vertices = edges.select(F.col('src').alias('id')).union(
  edges.select(F.col('dst').alias('id'))
).distinct()
 
# 创建图，跑连通分量算法
graph = GraphFrame(vertices, edges)
components = graph.connectedComponents()  # 同一分量 = 同一个人
 
# 结果：每个ID都有一个component编号，相同编号 = 同一个OneID
components.select('id', 'component').show()

适用场景：ID类型多（5种以上）、用户量千万到亿级、存在多跳间接关联。

注意事项：图计算资源消耗大，建议离线跑、T+1更新。如果图中出现超大连通分量（比如一个分量包含10万+个ID），大概率是脏数据导致的误关联，需要做裁剪。

两种方案对比

维度	规则匹配	图计算
实现复杂度	低（SQL即可）	中高（需要图计算框架）
关联能力	直接关联（1跳）	间接关联（多跳）
适用数据量	百万级	千万到亿级
准确率	高（规则明确）	中高（可能误关联）
计算成本	低	高（图计算资源消耗大）
维护难度	低	中（需要处理超大分量裁剪）
我的选择	出海硬件品牌用这个	用户量再大一个量级才需要

面试高频追问

追问一：ID Mapping会不会把两个不同的人误合并？怎么防止？

会的，这叫「误关联」。最常见的原因是公共设备——比如一台展示用的iPad，很多人都登录过，如果按设备ID关联，就会把这些人错误地合并到一起。防止方法：①设置关联阈值，一个设备ID关联超过N个手机号（比如5个），就标记为公共设备，排除关联；②引入时间窗口，只认最近30天内的关联关系，太久的关系可能已经失效；③图计算中做连通分量裁剪，超过阈值的大分量拆开重新分析。

追问二：用户换手机了，ID关系怎么更新？

这就是为什么ID Mapping不是一次性工作。我们的做法是：ID关系表保留历史版本（拉链表方式），每天增量更新。用户换手机后，新手机的设备ID会和账号ID产生新的关联，同时老手机的关联关系标记为失效。OneID保持不变（因为账号ID没变），只是关联的设备ID更新了。

追问三：出海业务的ID Mapping有什么特殊挑战？

出海业务最大的挑战是隐私合规。欧洲用户受GDPR约束，不能随意使用设备ID（IDFA/GAID）做关联。苹果iOS 14之后IDFA默认关闭，获取率从95%暴跌到30%以下。我们的应对策略是：①优先用账号体系做关联（不依赖设备ID）；②对欧洲用户使用概率性匹配（IP+UA+时间窗口），但准确率较低；③引导用户登录，提高账号ID覆盖率。

实战踩坑

我在某出海硬件品牌做ID Mapping时踩过一个大坑：有一批测试设备（约500台），被不同测试人员反复登录注册，导致这些设备ID关联了上千个手机号。在跑图计算时，这些测试设备形成了一个超大连通分量，把几百个不相关的真实用户错误地合并到了一起。

后来运营反馈说推送出了问题——某个用户收到了完全不相关的推荐内容——排查后发现就是这个原因。

-- 排查和清理公共设备的SQL
-- 找出关联手机号过多的设备（疑似公共设备/测试设备）
SELECT device_id, COUNT(DISTINCT phone_number) AS phone_cnt
FROM dwd_id_relation
GROUP BY device_id
HAVING COUNT(DISTINCT phone_number) > 5  -- 一个设备关联超过5个手机号
ORDER BY phone_cnt DESC;
 
-- 将这些设备加入黑名单，不参与ID Mapping
INSERT INTO dim_device_blacklist
SELECT device_id, phone_cnt, CURRENT_DATE AS add_date
FROM (
  SELECT device_id, COUNT(DISTINCT phone_number) AS phone_cnt
  FROM dwd_id_relation
  GROUP BY device_id
  HAVING COUNT(DISTINCT phone_number) > 5
) t;

教训：做ID Mapping之前，一定要先做数据清洗——排除公共设备、测试设备、异常账号。否则脏数据会污染整个ID关系图，排查成本极高。

总结

ID Mapping是用户画像的基础设施，核心是生成OneID。小规模用规则匹配（SQL JOIN），大规模用图计算（连通分量）。关键注意防止误关联（公共设备黑名单、阈值裁剪）和持续更新（拉链表维护历史关系）。面试时能讲清楚OneID的设计方案和踩坑经验，面试官会觉得你做得很深。

ID打通了，标签也算好了，但每天的标签加工任务怎么调度？数据延迟了怎么办？下一篇聊标签调度和SLA保障。

你们做过ID Mapping吗？用的规则匹配还是图计算？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长