SQL中每个分组前N名的平均值如何统计？

必须先用 ROW_NUMBER() 为每组行生成序号，再在外层筛选 rn ≤ N；直接在 GROUP BY 后用 LIMIT/TOP 无效，因 SQL 不支持分组内限行。

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

直接在 GROUP BY 后用 LIMIT 或 TOP 是无效的——SQL 不支持分组内限行。必须先标记每组内的行序号，再过滤。核心是：先用 ROW_NUMBER()（或 RANK()）按排序生成序号，再在外层筛选 rn ，最后对结果求平均。

常见错误是把 ROW_NUMBER() 放在聚合之后，或者误用 GROUP BY + ORDER BY 试图控制“前N”，这完全不起作用。

• ROW_NUMBER() 保证严格递增序号（即使值相同也不同序），适合“取确切N条”
• 排序字段必须明确，比如 ORDER BY score DESC；漏写 ORDER BY 会报错或结果不可控
• 分区键（PARTITION BY）必须与你要的“每个分组”一致，比如按 department 分组就写 PARTITION BY department

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

这些主流数据库都支持标准窗口函数，语法无差异。示例：统计每个部门薪资最高的3人平均薪资：

SELECT department, AVG(salary) AS avg_top3_salaryFROM (  SELECT department, salary,         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn  FROM employees) rankedWHERE rn <= 3GROUP BY department;

注意：AVG() 是对子查询过滤后的结果计算，不是对原始全表分组后取平均。如果某部门只有2人，AVG() 就只算这2人，不会补空或报错。

• MySQL 5.7 或更早版本不支持窗口函数，必须用自关联或变量模拟，复杂且易错
• Oracle 用户可直接用，但注意 ROW_NUMBER() 和 RANK() 对并列值处理不同：并列时前者跳号（1,2,2,4），后者连续（1,2,2,3），选哪个取决于业务是否允许“并列第2名都算进前3”

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

如果排序字段含 NULL，默认排在最前（ORDER BY ... DESC 时）或最后（ASC），可能意外挤占前N位置。显式控制用 NULLS LAST（PostgreSQL/Oracle）或 IS NULL 排序条件（MySQL）。

• 想排除 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, AVG(salary)FROM rankedWHERE rn <= 3GROUP BY department;

CTE 不改变执行计划，但避免了嵌套括号，调试时也方便单独查 ranked 结果验证序号是否符合预期。别在 CTE 里写 ORDER BY 试图“提前排序”——窗口函数的排序已决定顺序，额外 ORDER BY 无意义还可能被优化器忽略。

真正容易被忽略的是：窗口函数里的 ORDER BY 必须和业务语义一致；比如按时间倒序取最新3条，就不能错写成正序。一旦排序逻辑偏差，前N就全错了，而这种错误往往没有报错，只悄悄返回错误结果。