M5-001Easy窗口函数 · Window functions🔥 大厂高频出现 9 次
取每个品类销售额前 3 的商品(含并列)。用哪种排名函数?
Return the top three products by revenue in each category, including ties. Which ranking function do you use?
💡 考场高分一句话答案:
用 DENSE_RANK(并列不跳号,取前 3 个不同名次);若要“恰好 3 行”用 ROW_NUMBER。
DENSE_RANK, so ties share a rank without skipping and the top three ranks include all tied products. Use ROW_NUMBER if you need exactly three rows.
🧭 Playbook 核心提示 / Actionable Calibration:
注意窗口函数在 GROUP BY 之后执行,所以 ORDER BY SUM(revenue) 是合法的——这是很多人会写成子查询的地方。
📌 核心考察要点 (Key Points)
- RANK:并列跳号(1,1,3),前 3 名可能只包含 2 个商品
- DENSE_RANK:并列不跳号(1,1,2),前 3 个名次包含所有并列
- ROW_NUMBER:强制唯一,结果恰好 3 行但并列者的取舍是任意的
- 业务含义:报表“Top 3”通常用 DENSE_RANK;分页/去重用 ROW_NUMBER
- RANK: ties share a rank but skip the next (1,1,3), so 'top 3' may return only two products
- DENSE_RANK: ties share a rank without skipping (1,1,2), so 'top 3' includes every tie
- ROW_NUMBER: forces uniqueness — exactly three rows, but the choice among ties is arbitrary
- Business default: reports use DENSE_RANK; pagination and dedup use ROW_NUMBER
🔍 大厂面试官连环追问 (Follow-ups)
- 如果要“每个品类取前 3 行”而不是“前 3 名”,怎么改?
- 窗口函数与 GROUP BY 的执行顺序是什么?
🎯 关联大厂考题实战训练 (6 题)
嫌字多看太累?进入苏格拉底式对话推演实验室
零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。
⚡
想检验本题的肌肉记忆?进入 3 阶闪卡主动回忆盲测
隐藏答案遮罩,自测:审题锚点 ➔ 数理公式机理 ➔ 工业标杆代码与连环追问。3 分钟攻破认知盲区!