M5-020Hard去重与关联 · Dedup & joins

如何把“事件时间”关联到“当时生效的配置版本”(as-of join)?

How do you join an event to the configuration version that was live at the time (as-of join)?
💡 考场高分一句话答案:

对每个事件找时间上最近的、早于事件时间的配置记录;用窗口函数或相关子查询实现。

For each event, find the most recent configuration row whose timestamp is not later than the event. Use a window fill or a correlated subquery.

📌 核心考察要点 (Key Points)

  • 定义:as-of join = 按时间找“不晚于当前时刻的最近一条”
  • 窗口写法:把配置表与事件表 UNION 后按时间排序,用 LAST_VALUE IGNORE NULLS 向下填充
  • 相关子查询:(SELECT cfg FROM config c WHERE c.ts <= e.ts ORDER BY c.ts DESC LIMIT 1)
  • 性能:大表用窗口填充更高效;小配置表用子查询更简单
  • Definition: as-of join = the latest row at or before the event time
  • Window approach: UNION config and events, sort by time, then LAST_VALUE(version IGNORE NULLS) to fill downward
  • Subquery approach: a correlated ORDER BY ts DESC LIMIT 1 is simplest for a small config table
  • Performance: window fill scales better on large event tables

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

  • as-of join 与普通 join 的区别?
  • 如何处理配置在事件之后才生效的情况?

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

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

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