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.
🎯 Drill This Card Now➔