M5-002Easy去重与关联 · Dedup & joins🔥 大厂高频出现 2 次

一张用户表有重复记录(同一 user_id 多条),如何取每个用户最新的一条?

A user table has duplicate rows per user_id. How do you take the latest record per user?
💡 考场高分一句话答案:

用 ROW_NUMBER 按更新时间倒序编号,取 rn=1;BigQuery 可直接用 QUALIFY 省掉子查询。

ROW_NUMBER partitioned by the dedup key ordered by update time descending, then keep rn = 1. BigQuery can do it in one pass with QUALIFY.

📌 核心考察要点 (Key Points)

  • 核心:PARTITION BY 去重键,ORDER BY 时间 DESC,取第一行
  • 并列时间:加第二排序键(如 id)保证确定性
  • 替代:ARRAY_AGG(STRUCT(...) ORDER BY ts DESC LIMIT 1)[OFFSET(0)]
  • 注意:去重前先确认“重复”是同一实体的多次更新,还是真的不同实体
  • Core: PARTITION BY the dedup key, ORDER BY time DESC, keep the first row
  • Ties: add a secondary sort key such as id to make the result deterministic
  • Alternative: ARRAY_AGG(STRUCT(...) ORDER BY ts DESC LIMIT 1)[OFFSET(0)]
  • Caution: confirm the duplicates are updates to one entity, not genuinely different entities

🔍 大厂面试官连环追问 (Follow-ups)

  • 如果同一用户有多条“最新”记录(时间相同),怎么保证结果稳定?
  • 如何检测表里有多少重复?

🎯 关联大厂考题实战训练 (2 题)

嫌字多看太累?进入苏格拉底式对话推演实验室

零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。

💬 开启对话式破冰 ➔
⚡
想检验本题的肌肉记忆?进入 3 阶闪卡主动回忆盲测
隐藏答案遮罩,自测:审题锚点 ➔ 数理公式机理 ➔ 工业标杆代码与连环追问。3 分钟攻破认知盲区!
🎯 立即在闪卡中盲测此题➔