M5-008Medium窗口函数 · Window functions🔥 大厂高频出现 5 次
如何计算中位数和 p95?BigQuery 里有哪些写法?
How do you compute a median and a p95 in BigQuery?
💡 考场高分一句话答案:
近似用 APPROX_QUANTILES(x, 100)[OFFSET(50)];精确用 PERCENTILE_CONT(x, 0.5) OVER (),注意它是窗口函数。
Approximate with APPROX_QUANTILES(x, 100)[OFFSET(50)]; exact with PERCENTILE_CONT(x, 0.5) OVER (), which is a window function.
📌 核心考察要点 (Key Points)
- 近似:APPROX_QUANTILES 基于 sketch,速度快、内存小,误差可控
- 精确:PERCENTILE_CONT 是窗口函数,需要 OVER (),不能直接当聚合
- 分组精确:PERCENTILE_CONT(x, 0.5) OVER (PARTITION BY grp) 再 DISTINCT
- 取舍:亿级数据用近似;报表核对用精确
- Approximate: APPROX_QUANTILES uses a sketch — fast, memory-light, bounded error
- Exact: PERCENTILE_CONT requires OVER (), it is not an aggregate
- Grouped exact: PERCENTILE_CONT(x, 0.5) OVER (PARTITION BY grp) then DISTINCT
- Trade-off: approximate for billions of rows, exact for reconciliation reports
🔍 大厂面试官连环追问 (Follow-ups)
- 如何计算“去掉最高 1% 后的均值”?
- 中位数在偶数样本时怎么定义?
🎯 关联大厂考题实战训练 (5 题)
嫌字多看太累?进入苏格拉底式对话推演实验室
零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.