SQL如何找出每组中超过平均值的数据_AVG窗口函数对比-SQL-PHP中文网

窗口函数avg()不能在where中直接使用，须通过子查询或cte先计算组内均值再筛选；其性能优于相关子查询，且主流数据库（除sqlite）均支持，但需注意null处理与类型陷阱。

sql如何找出每组中超过平均值的数据_avg窗口函数对比

用 `AVG()` 窗口函数直接对比组内均值

直接在 WHERE 里用 AVG() 会报错 —— 聚合函数不能出现在非分组查询的 WHERE 子句中。正确做法是先用窗口函数算出每组均值，再在外层筛选。比如查每个部门薪资高于本部门平均薪资的员工：

SELECT dept, name, salary
FROM (
  SELECT dept, name, salary,
         AVG(salary) OVER (PARTITION BY dept) AS avg_dept_salary
  FROM employees
) t
WHERE salary > avg_dept_salary;

关键点：窗口函数必须写在子查询或 CTE 里，不能和 WHERE 写在同一层级；PARTITION BY dept 定义“每组”，不加则全表算一个均值。

`AVG()` 窗口 vs 子查询计算均值的性能差异

窗口函数版本通常更快，尤其数据量大时。子查询方式（如 JOIN 或相关子查询）可能触发多次扫描：

窗口版：单次扫描，内存中维护每组累计和与计数
子查询版（如 (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept)）：对每一行都执行一次子查询，O(n²) 风险
MySQL 8.0+、PostgreSQL、SQL Server 2012+、Oracle 都支持 AVG() OVER，但 SQLite 目前不支持窗口函数

容易忽略的 NULL 和类型陷阱

AVG() 默认忽略 NULL 值，但如果你的字段允许空值，且业务上 NULL 代表“未发放薪资”，那均值结果可能偏高 —— 这不是 bug，是设计行为。另外注意：

AVG(int_col) 返回 DECIMAL 或 FLOAT，比较时别用 = 判等浮点结果
若想把 NULL 当 0 参与计算，得先 COALESCE(salary, 0)，但语义已改变
某些数据库（如旧版 PostgreSQL）对 OVER() 中排序敏感，不加 ORDER BY 一般没问题，但加了会影响帧定义（frame clause），进而影响 AVG() 结果范围

替代方案：用 `HAVING` 不行，但 CTE 更易读

有人试图用 GROUP BY dept HAVING salary > AVG(salary)，这语法非法 —— HAVING 只能过滤分组后的聚合结果，不能引用原始行字段。更清晰的写法是用 CTE：

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
)
SELECT e.dept, e.name, e.salary
FROM employees e
JOIN dept_avg d ON e.dept = d.dept
WHERE e.salary > d.avg_sal;

这种写法逻辑分离明确，调试时可单独查 dept_avg；但要注意：它按 GROUP BY 计算均值，和窗口版结果一致，只是执行计划可能不同 —— 某些场景下 CTE 会被物化，反而变慢。

真正复杂的地方在于业务定义：“平均值”是否包含试用期员工、“超过”是否允许等于、空值如何解释——这些没法靠 SQL 函数自动解决，得先对齐口径。