SQL中如何统计每个分组前N名平均值的详细方法

先说一个核心结论：要实现“每个分组内取前N行再聚合”，靠 GROUP BY 加 LIMIT 或 TOP 是行不通的——SQL 压根不支持分组内直接限行。正确的做法是先用窗口函数 ROW_NUMBER() 给每组内的行标上序号，然后在外面过滤掉序号大于 N 的行，最后对剩下的结果做聚合。

用窗口函数 ROW_NUMBER() 筛出每组前N行再聚合

直接在 GROUP BY 后面接 LIMIT 或 TOP 纯属无效操作——SQL 的语法层面就没有按组取前N的快捷方式。真正的流程是：先用 ROW_NUMBER()（或者 RANK()，看需求）按你指定的排序字段生成序号，然后在最外层查询里用 WHERE rn <= N 过滤，最后对过滤后的结果求平均。

常见坑是：有人把 ROW_NUMBER() 写在聚合查询里面，或者试图用 GROUP BY 配合 ORDER BY 来控制“前N”，结果都不对。

• ROW_NUMBER() 保证序号严格递增，即使值相同也不重复（1,2,3,4...），适合“要恰好N条”的场景。
• 排序字段必须明确，比如 ORDER BY score DESC；如果不写 ORDER BY，数据库会报错或者给个不可控的随机顺序。
• 分区键 PARTITION BY 必须跟你想分的组一致——比如按部门分组就写 PARTITION BY department。

PostgreSQL / MySQL 8.0+ / SQL Server 写法一致

这些主流数据库对窗口函数的支持都遵循标准，语法几乎一模一样。来看一个实际例子：统计每个部门薪资最高的3个人的平均薪资：

SELECT department, A VG(salary) AS a vg_top3_salary
FROM (
  SELECT department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3
GROUP BY department;

注意这里的 A VG() 是对子查询过滤后的结果计算，而不是对原始全表分组后取平均。如果某个部门只有2个人，那 A VG() 就只算这2人，不会自动补0或者报错。

• MySQL 5.7 及更早版本不支持窗口函数，想实现类似功能得用自关联或用户变量，不仅复杂还容易出错。
• Oracle 用户可以直接用，但要留意 ROW_NUMBER() 和 RANK() 的区别：遇到并列值，前者会跳号（1,2,2,4），后者连续（1,2,2,3）。选哪个取决于业务——如果“并列第2名也算进前3”，那就用 RANK()；如果“哪怕并列也只要前3行”，坚持 ROW_NUMBER()。

遇到 NULL 或重复值时怎么处理？

如果排序字段里有 NULL，默认情况下，降序时 NULL 会排在最前面，升序时排在最后面，这很可能挤掉你真正想要的前N条数据。想控制的话，PostgreSQL/Oracle 可以写 NULLS LAST，MySQL 则可以用 IS NULL 条件来调整排序规则。

• 想彻底排除 NULL 参与排名？在子查询里加个 WHERE salary IS NOT NULL 就行。
• 如果需要保留并列且“最多取N个”，用 RANK()；如果要求“恰好N个（哪怕并列也只取前N行）”，坚持用 ROW_NUMBER()。
• 性能方面，窗口函数本身开销不大，但如果表非常大，而且没有在 PARTITION BY + ORDER BY 涉及的字段上建索引，排序阶段可能会拖慢速度。

替代方案：CTE 比子查询更易读

逻辑一样的情况下，用 CTE（公共表表达式）能让代码更容易维护，尤其是当你需要复用排名结果或者叠加多层过滤时：

WITH ranked AS (
  SELECT department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department, A VG(salary)
FROM ranked
WHERE rn <= 3
GROUP BY department;

CTE 不会改变执行计划，但避免了嵌套括号的阅读障碍，调试时还能单独查一下 ranked 的结果，验证序号是不是符合预期。别在 CTE 里加 ORDER BY 试图“提前排序”——窗口函数里的排序已经决定了顺序，多余的 ORDER BY 没意义，还可能被优化器忽略。

最容易翻车的地方其实是：窗口函数里的 ORDER BY 必须和业务语义完全一致。比如你想按时间倒序取最新3条，结果写成了正序，那前N就全错了。这种错误不会报错，只是悄悄返回错误结果，所以核查排序逻辑一定不能马虎。