M5-016Medium窗口函数 · Window functions🔥 大厂高频出现 1 次

如何计算每个类目的销售额占比?

How do you compute each category's share of total revenue?
💡 考场高分一句话答案:

用 SUM() OVER () 求全局总额作分母,或用子查询/CROSS JOIN 拿到总额。

Use SUM() OVER () for the global total as the denominator, or a subquery or CROSS JOIN to fetch the total.

📌 核心考察要点 (Key Points)

  • 窗口方式:SUM(rev) OVER () 在聚合后执行,得到全局总额
  • 分组占比:SUM(rev) OVER (PARTITION BY region) 得区域内占比
  • 除零:SAFE_DIVIDE
  • 注意:窗口函数在 GROUP BY 之后,所以可以直接用聚合结果
  • Window approach: SUM(SUM(revenue)) OVER () after GROUP BY gives the global total
  • Group share: SUM(SUM(revenue)) OVER (PARTITION BY region) gives the within-region share
  • Division: SAFE_DIVIDE
  • Sanity check: shares should sum to 100%; if not, you have overlaps or missing categories

🔍 大厂面试官连环追问 (Follow-ups)

  • 如何计算“Top 10 商品贡献的收入占比”?
  • 占比之和不为 100% 可能是什么原因?

🎯 关联大厂考题实战训练 (1 题)

嫌字多看太累?进入苏格拉底式对话推演实验室

零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 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➔