M5-007Hard会话与序列 · Sessions & sequences
如何把事件流切分成会话(30 分钟无操作即新会话)?
How do you split an event stream into sessions with a 30-minute inactivity rule?
💡 考场高分一句话答案:
用 LAG 取上一事件时间,标记间隔超过 1800 秒为新会话起点,再用累计求和生成 session_id。
LAG the previous event time, flag a new session when the gap exceeds 1800 seconds, then a running SUM of the flag produces the session id.
📌 核心考察要点 (Key Points)
- 第一步:LAG(ts) 按用户、时间排序
- 第二步:IF(间隔 > 1800 或首行, 1, 0) 标记新会话
- 第三步:SUM(flag) OVER (... ROWS UNBOUNDED PRECEDING) 生成递增 id
- 边界:同一秒多事件需加次级排序键保证确定性
- Step 1: LAG(ts) partitioned by user ordered by time
- Step 2: IF(gap > 1800 OR first row, 1, 0) as the new-session flag
- Step 3: SUM(flag) OVER (... ROWS UNBOUNDED PRECEDING) as the incrementing session id
- Edge case: multiple events in the same second need a secondary sort key for determinism
🔍 大厂面试官连环追问 (Follow-ups)
- 如何统计“每用户日均会话数”?
- 如果会话定义是“30 分钟无转化”,逻辑要怎么改?
嫌字多看太累?进入苏格拉底式对话推演实验室
零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.