怎么在SQL查询中同时计算总体平均值与各组平均值-SQL-PHP中文网

使用group by时需配合avg() over()获取总体平均值，因avg()聚合仅返回分组均值；窗口函数avg(salary) over()计算全局均值并重复至每行，而avg(salary) over(partition by department)与聚合结果相同故不必要。

怎么在sql查询中同时计算总体平均值与各组平均值

用 AVG() 配合窗口函数计算总体和分组平均值

直接在 GROUP BY 查询里加一个 AVG() 聚合函数，只能得到各组平均值；要同时拿到总体平均值，必须用窗口函数。核心思路是：在分组聚合的同时，用 AVG() OVER() 算出不随分组变化的全局均值。

AVG(salary) OVER() 不带 PARTITION BY，就对整个结果集（或当前 WHERE 后的数据）计算一次总体平均值，每行都重复这个值
如果用了 GROUP BY department，就得搭配 AVG(salary) OVER(PARTITION BY department) 才能得到各组平均值——但这时它和 AVG(salary) 聚合结果一样，没必要重复
真正实用的组合是：GROUP BY department + AVG(salary) OVER()，这样每组一行，附带全局均值作参照

示例：

SELECT 
  department,
  AVG(salary) AS avg_by_dept,
  AVG(salary) OVER() AS overall_avg
FROM employees
GROUP BY department;

GROUP BY 和窗口函数共存时的执行顺序陷阱

很多人写完发现报错：column "employees.salary" must appear in the GROUP BY clause or be used in an aggregate function。这是因为窗口函数虽然不改变行数，但数据库仍要求 SELECT 中所有非聚合、非窗口字段必须出现在 GROUP BY 中——而 salary 显然不在 GROUP BY 里，也不能直接出现在 SELECT 列表中（除非套进聚合或窗口）。

错误写法：SELECT department, salary, AVG(salary) OVER() ... GROUP BY department —— salary 单独出现，非法
正确做法：所有非分组、非聚合字段，要么删掉，要么包进聚合（如 MAX(salary)），要么包进窗口（如 AVG(salary) OVER(PARTITION BY department)）
注意：AVG(salary) OVER() 是合法的，因为它不依赖当前行的 salary 值，而是基于整个输入集计算

想保留明细行？别用 GROUP BY，改用窗口函数全量计算

如果目标不是“每组一行”，而是“每人一行，同时显示他所在部门的平均工资和公司整体平均工资”，那就根本不用 GROUP BY，全部交给窗口函数。

AVG(salary) OVER(PARTITION BY department) → 各组平均值，每行填入对应部门的均值
AVG(salary) OVER() → 总体平均值，所有行都一样
这种写法天然兼容明细数据，无需聚合，也不丢失原始字段

示例：

SELECT 
  name,
  department,
  salary,
  AVG(salary) OVER(PARTITION BY department) AS dept_avg,
  AVG(salary) OVER() AS overall_avg
FROM employees;

MySQL 8.0+、PostgreSQL、SQL Server 都支持，但 SQLite 不行

窗口函数是 SQL:2003 标准，但 SQLite 直到 3.25.0（2018 年）才开始支持，且部分发行版仍默认禁用。如果你执行 AVG() OVER() 报错 near "OVER": syntax error，大概率是 SQLite 版本太老或未启用窗口函数编译选项。

PostgreSQL / SQL Server / Oracle / MySQL 8.0+：开箱即用，放心写
SQLite：运行 SELECT sqlite_version(); 确认 ≥ 3.25.0，再查 PRAGMA compile_options; 是否含 ENABLE_WINDOW_FUNCTIONS
旧版 MySQL（5.7 及更早）：只能用自连接或子查询模拟，性能差、写法绕，不推荐

复杂点在于：窗口函数的 OVER() 子句看似简单，但一旦涉及 ORDER BY、ROWS BETWEEN 或多层嵌套，结果可能和直觉不符；而多数人只想要个静态总体均值，这时候最安全的就是空括号 OVER()——别加多余东西。