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。
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.