M5-013Medium去重与关联 · Dedup & joins
如何找出“浏览过但从未购买”的用户?
How do you find users who viewed but never purchased?
💡 考场高分一句话答案:
用 LEFT JOIN 或 NOT EXISTS 做反连接;注意 NULL 处理,NOT IN 遇到 NULL 会返回空结果。
A left anti-join or NOT EXISTS. Beware NOT IN, which returns nothing when the subquery contains NULL.
📌 核心考察要点 (Key Points)
- LEFT JOIN + IS NULL:最通用
- NOT EXISTS:语义清晰,通常性能更好
- NOT IN 的坑:子查询含 NULL 时整体返回空
- 性能:大表用 EXCEPT DISTINCT 或 anti-join 时注意分区裁剪
- LEFT JOIN plus IS NULL: most portable
- NOT EXISTS: clearer intent and usually faster
- NOT IN trap: any NULL in the subquery makes the whole predicate unknown, so the result is empty
- Performance: watch partition pruning when anti-joining large tables
🔍 大厂面试官连环追问 (Follow-ups)
- 为什么 NOT IN 遇到 NULL 会返回空?
- 如何统计“浏览后 7 天内未购买”的用户?
嫌字多看太累?进入苏格拉底式对话推演实验室
零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.