M5-017Hard会话与序列 · Sessions & sequences
如何取每个用户最近的两个事件?
How do you get each user's two most recent events?
💡 考场高分一句话答案:
用 ROW_NUMBER 编号后取 rn<=2,再按用户聚合成两列;或用 ARRAY_AGG 排序后取前两个元素。
ROW_NUMBER ordered by time descending, keep rn ≤ 2, then pivot into columns with conditional aggregation; or ARRAY_AGG with a limit.
📌 核心考察要点 (Key Points)
- 方式一:ROW_NUMBER 后 WHERE rn <= 2,再 PIVOT 成两列
- 方式二:ARRAY_AGG(STRUCT(...) ORDER BY ts DESC LIMIT 2)
- 用途:分析“最后一次行为 → 流失”、事件序列模式
- 注意:同一时间戳的多个事件需要次级排序键
- Approach one: ROW_NUMBER then WHERE rn <= 2, then MAX(IF(rn = 1, ...)) to pivot
- Approach two: ARRAY_AGG(STRUCT(...) ORDER BY ts DESC LIMIT 2)
- Use: last action before churn, event-sequence patterns
- Note: events sharing a timestamp need a secondary sort key
🔍 大厂面试官连环追问 (Follow-ups)
- 如何统计“最后事件是 X 的用户数”?
- ARRAY_AGG 方式怎么写?
嫌字多看太累?进入苏格拉底式对话推演实验室
零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.