91学AI - 大数据工程师

考察点

TopN 是 SQL 面试出镜率最高的题型，面试官想看你的模板熟不熟，更想看你会不会主动确认「并列算不算、要明细还是只要 id」。追问方向：为什么不用 order by limit、rank 和 dense_rank 在 TopN 里的语义差别、TopN 后再关联明细怎么写。

参考答案

标准模板：窗口编号 + 外层过滤

「每个部门工资最高的 3 个人」的标准写法：

select dept, emp_name, salary
from (
  select dept, emp_name, salary,
         row_number() over (partition by dept order by salary desc) as rn
  from emp
) t
where rn <= 3;

两步：内层用窗口函数给组内每行编号，外层按编号过滤。这个模板覆盖 80% 的 TopN 题，变化的只有三个旋钮：partition by 的分组列、order by 的排序列和方向、过滤条件的 N。

变体一：全局 TopN 用不用窗口函数

全局 TopN（全公司工资前 10）其实不需要窗口函数，order by salary desc limit 10 就行。但要注意两点：

Hive 的 order by 是全局排序，只有一个 reducer，大表上慢。如果只要 TopN 不要全排序结果，数据量又特别大，可以先在每个分区里 sort by ... limit N 再归并，不过面试场景写 order by limit 即可。
order by 有并列时 limit 是物理截断，第 10、11 名工资相同只会留一个。业务要「前十名及其并列」就得换回 rank 写法。

变体二：并列语义的三个选择

TopN 的「N」到底怎么算，是面试必问的口径问题：

需求	函数	效果
严格 N 行，并列随机取	row_number	恰好 N 行，并列时取谁不确定
前 N 名，并列都算	rank	可能超过 N 行（1、2、2 时 rn<=2 出 3 行）
前 N 个名次段，并列都算	dense_rank	恰好 N 个不同名次值

面试时主动说出这个区分是加分项，说明你在真实业务里被「为什么 Top10 查出来 11 个人」追杀过。

变体三：TopN 只要明细还是要回表

一种常见业务是「取每个用户最近一次下单的完整订单信息」。如果订单表字段全，窗口函数一次搞定：

select user_id, order_id, amount, create_time
from (
  select *, row_number() over (partition by user_id order by create_time desc) rn
  from orders
) t where rn = 1;

但如果明细在另一张大表、TopN 只是为了筛 id，更高效的写法是先窗口筛出 id 列表再 join 明细，减少参与排序的列宽。还有一种面试官爱听的古法写法：不用窗口函数，用 group by 拿极值再回 join：

select o.*
from orders o
join (select user_id, max(create_time) as mt from orders group by user_id) m
on o.user_id = m.user_id and o.create_time = m.mt;

这个写法在并列极值时会出多条，而且老系统（MySQL 5.7 之前、Hive 0.13 之前没有窗口函数）里是唯一选择。缺点是 join 多一次 shuffle，现代引擎里窗口函数通常更优，但理解它能应对「不用窗口函数怎么写」这种刻意为难的追问。

变体四：TopN 占比分析

「每个品类销量 Top3 商品的销量占品类总销量的比例」这类题，套路是 TopN 和组内聚合叠加：

select category,
       sum(case when rn <= 3 then sales else 0 end) / sum(sales) as top3_ratio
from (
  select category, product_id, sales,
         row_number() over (partition by category order by sales desc) as rn
  from product_sales
) t
group by category;

窗口编号之后不一定要过滤，放在 case 里打标再聚合，是很多占比类、分层类题目的通用手法。

可能的追问

窗口函数 TopN 和 order by limit 性能差多少？ 全局 TopN 用 order by limit 更省（引擎有 TopK 优化，不用全排序物化）；组内 TopN 必须窗口函数，代价是一次 partition shuffle + 组内排序，partition key 倾斜时注意数据倾斜。
取每组最大一条记录，有更快的方式吗？ 数据量极大且只要极值字段时，group by max 比窗口省一次排序；要整行明细还是窗口函数或 max 回 join。
TopN 结果要稳定（并列时排序确定）怎么办？ order by 里加 tiebreaker，比如 order by salary desc, emp_id，保证相同工资时按工号排，结果可复现。生产报表必须加，不然每天跑出来的 TopN 可能不一样。

TopN 与组内排序类 SQL 怎么写？row_number、rank 实战套路

考察点