OneID与用户ID打通

-- 从登录日志中提取ID关系对
-- 核心逻辑：找到两个ID同时出现的事件
CREATE TABLE tmp_id_pairs AS
SELECT DISTINCT
    user_id,
    device_id,
    'login' AS relation_source,          -- 关系来源
    MIN(event_time) AS first_bindding_time -- 首次关联时间
FROM dwd_user_login_detail_di
WHERE user_id IS NOT NULL
  AND device_id IS NOT NULL
  AND dt >= DATE_SUB(CURRENT_DATE, 90)    -- 只取最近90天的关系
GROUP BY user_id, device_id;

-- 同理提取 user_id ↔ phone 的关系
INSERT INTO tmp_id_pairs
SELECT DISTINCT
    user_id,
    phone AS device_id,   -- 复用字段
    'bindphone' AS relation_source,
    MIN(bindding_time) AS first_bindding_time
FROM dwd_user_bindphone_log
WHERE user_id IS NOT NULL AND phone IS NOT NULL
GROUP BY user_id, phone;

-- 为每个设备找到关联的user_id，取最早的那个作为one_id
CREATE TABLE dim_id_mapping AS
SELECT
    device_id,
    user_id,
    FIRST_VALUE(user_id) OVER(
        PARTITION BY device_id
        ORDER BY first_bindding_time ASC
    ) AS one_id
FROM tmp_id_pairs;

设备A → user_id_1 → 手机号 138xxxx
设备B → user_id_2 → 手机号 138xxxx（同一个手机号！）

直接关联：设备A-user_id_1, 设备B-user_id_2
间接关联：通过手机号发现 user_id_1 和 user_id_2 是同一个人
→ 设备A、设备B、user_id_1、user_id_2 都应该映射到同一个one_id

这种间接关联用简单的SQL处理很麻烦，但用图算法就很自然：把所有ID当作图的节点，ID关系对当作边，求连通分量——同一个连通分量里的所有ID就是同一个自然人。

实现步骤：
1. 构建ID关系图
   - 节点：所有出现过的ID值（device_id、user_id、phone、openid...）
   - 边：每对有关联关系的ID之间连一条边

2. 求连通分量
   - 方法A：Spark GraphX 的 ConnectedComponents 算法
   - 方法B：自实现 Union-Find（并查集）算法
   - 方法C：SQL迭代（适合ID关系简单、规模不大的场景）

3. 分配OneID
   - 每个连通分量分配一个统一ID
   - 通常取分量内最早出现的user_id作为one_id

Spark GraphX实现的核心代码思路（伪代码）：

// 构建ID对作为图的边
val edges = idPairs.map(row => Edge(row.id1_hash, row.id2_hash, 1))
val graph = Graph.fromEdges(edges, defaultValue = 0)

// 求连通分量
val cc = graph.connectedComponents()

// cc.vertices 包含 (节点ID, 所属连通分量ID)
// 同一个连通分量的所有ID就是同一个自然人

优缺点：

• 优点：覆盖率高，能发现间接关联，比确定性匹配多打通10-20%的用户
• 缺点：有误关联风险——如果公共设备（网吧电脑、家庭共享iPad）也参与打通，可能把不同的人连到一起

方案三：概率性匹配（特定场景补充）

通过设备指纹、IP地址、行为特征等弱信号推测是否是同一人。

判断条件（多个弱信号叠加）：
- 相同IP地址
- 相同设备型号 + 操作系统版本
- 相似的使用时间段
- 相似的内容偏好
→ 多个信号综合打分，超过阈值判定为同一人

这种方法准确率较低（80%左右），一般只在以下场景使用：

• 广告归因中的"模糊归因"（精确归因不到时的兜底）
• 用户增长分析中的辅助参考
• 不直接用于核心指标计算

实际项目中，绝大多数公司用的是方案一为主 + 方案二辅助的组合。概率性匹配因为准确率低，业务方不太信任，只在广告归因等特定场景使用。我们的做法也是这样。

---

OneID落地的关键设计

ID映射表结构

CREATE TABLE dim_id_mapping (
    one_id          BIGINT    COMMENT '统一用户ID',
    id_type         STRING    COMMENT 'ID类型：user_id/device_id/phone/openid/click_id',
    id_value        STRING    COMMENT 'ID原始值',
    relation_source STRING    COMMENT '关系来源：login/bindphone/attribution/graph',
    confidence      STRING    COMMENT '置信度：high(确定性匹配)/medium(图算法)/low(概率性)',
    first_bindding_time TIMESTAMP COMMENT '首次关联时间',
    last_active_time    TIMESTAMP COMMENT '最近活跃时间',
    is_valid        INT       COMMENT '是否有效：1=有效，0=已过期',
    dt              STRING    COMMENT '分区日期'
) COMMENT '统一用户ID映射表';

使用方式

所有下游分析统一通过one_id关联：

-- 用OneID统一计算真实DAU
SELECT COUNT(DISTINCT m.one_id) AS real_dau
FROM dwd_user_event_detail_di e
JOIN dim_id_mapping m
  ON e.device_id = m.id_value
  AND m.id_type = 'device_id'
  AND m.is_valid = 1
WHERE e.dt = '2025-01-15';

-- 广告归因：从广告点击到App付费的完整链路
SELECT
    ad.campaign_name,
    COUNT(DISTINCT m.one_id) AS attributed_users,
    SUM(p.pay_amount) AS attributed_revenue
FROM dwd_ad_click_detail_di ad
JOIN dim_id_mapping m1 ON ad.click_id = m1.id_value AND m1.id_type = 'click_id'
JOIN dim_id_mapping m2 ON m1.one_id = m2.one_id AND m2.id_type = 'user_id'
JOIN dwd_trade_pay_detail_di p ON m2.id_value = p.user_id
WHERE ad.dt >= '2025-01-08'
  AND p.dt >= '2025-01-08'
GROUP BY ad.campaign_name;

---

必须注意的三个坑

坑一：公共设备问题

一台网吧电脑或家庭共享iPad，可能被多个人使用。如果不加限制，一个device_id关联了50个user_id，这些用户全被合并成了"一个人"——DAU从50变成1，数据严重失真。

解决方案： 设置阈值规则。

-- 标记公共设备：一个设备关联超过10个用户就排除
WITH device_user_cnt AS (
    SELECT device_id, COUNT(DISTINCT user_id) AS user_cnt
    FROM tmp_id_pairs
    GROUP BY device_id
)
-- 排除公共设备
SELECT * FROM tmp_id_pairs
WHERE device_id NOT IN (
    SELECT device_id FROM device_user_cnt WHERE user_cnt > 10
);

我们的实际经验：阈值设为10效果比较好。太低会误杀家庭共享设备（一家3-4口人共用iPad），太高会让真正的公共设备漏网。

坑二：ID漂移

用户换手机了。旧手机的device_id和当前user_id的关系还在，但旧手机可能已经给了别人。如果旧手机的新主人也装了你的App，两个不同的人就通过这个device_id被关联在一起了。

解决方案： 设置关系有效期。

-- ID关系超过180天未活跃就标记为过期
UPDATE dim_id_mapping
SET is_valid = 0
WHERE DATEDIFF(CURRENT_DATE, last_active_time) > 180;

坑三：OneID合并冲突

两个原本独立的one_id，因为新发现的关系需要合并。比如用户A和用户B一直被认为是两个人，突然发现他们绑定了同一个手机号——需要把两个one_id合并为一个。

这时候所有引用了旧one_id的下游表都需要更新，这是一个很重的操作。

解决方案： 维护一张one_id合并历史表，下游表在计算时先查合并表做映射。

---

面试怎么答？

高分回答

「我们用的是确定性匹配为主的OneID方案。核心是一张ID映射表dim_id_mapping，存储user_id、device_id、phone、广告click_id等多种ID之间的映射关系。关系来源主要是登录事件和绑定事件——用户登录时同时上报了device_id和user_id，我们就自动建立映射。统一ID取最早注册的user_id。」

「实际落地遇到过两个问题：一是公共设备——一个iPad关联了50多个user_id，我们加了阈值规则，一个设备关联超过10个用户就标记为公共设备排除；二是ID过期——用户换手机后旧设备的映射关系需要失效，我们设了180天的有效期。当前方案的准确率在95%以上，覆盖了80%的活跃用户。」

追问防线

追问1：OneID的更新频率是什么？

我们是T+1天级更新。每天凌晨跑一个调度任务，从前一天的登录日志和绑定事件中提取新的ID关系对，增量更新到dim_id_mapping。

如果业务对实时性要求高（比如实时广告归因），可以用Flink做实时ID关系采集，新关系产生时立即写入。但大部分业务场景T+1已经够了。

追问2：隐私合规怎么处理？

这是出海业务的敏感点。手机号、设备ID这些属于个人隐私数据（PII），GDPR/CCPA等法规对其使用有严格要求。

我们的做法：一是ID值在存储时做哈希脱敏，dim_id_mapping里存的不是原始手机号而是哈希值；二是用户申请数据删除时，要能定位到所有关联ID并一并清除；三是ID映射关系只在数仓内部使用，不对外暴露。

追问3：如果不做OneID，有没有替代方案？

最简单的替代方案是强制登录——App的所有功能都需要登录才能使用，这样就不存在"未登录用户"的问题，device_id和user_id天然绑定。

但这在很多场景下不可行——比如用户首次打开App就要看到推荐内容，不可能先让人注册。所以OneID在大部分多端、多渠道的产品中都是必须的。

---

总结

• OneID的目标：多端多渠道ID映射为统一用户标识，解决DAU虚高、归因断链、画像残缺等问题
• 三种方案：确定性匹配（主力，准确率高）→ 图算法（补充，发现间接关联）→ 概率性匹配（特定场景兜底）
• 核心表：dim_id_mapping，存ID类型+ID值+one_id+置信度+有效期
• 三个必踩的坑：公共设备（设阈值排除）、ID漂移（设有效期）、合并冲突（维护合并历史）
• 面试关键：说出你用的具体方案、遇到的问题、解决的办法和效果数据