SQL怎样统计各组内超过平均值的记录_AVG OVER过滤技巧-SQL-PHP中文网

因为sql执行顺序中where在select（窗口函数计算阶段）之前，窗口函数尚未生成结果，故where中引用avg() over()会报错；正确做法是用cte或子查询先计算再过滤。

sql怎样统计各组内超过平均值的记录_avg over过滤技巧

不能直接在 WHERE 中用 AVG() OVER() 做过滤——它还没算出来，数据库会报错。

WHERE 里写 AVG() OVER() 为什么报错

SQL 执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，而窗口函数在 SELECT 阶段才计算。所以当你在 WHERE 里写 salary > AVG(salary) OVER(PARTITION BY dept)，数据库根本还没生成这个值，自然无法比较。

常见错误现象：ERROR: window functions are not allowed in WHERE（PostgreSQL）、Invalid use of window function（MySQL 8.0+）或类似提示。

别试图绕过：加括号、子查询嵌套一层、用别名都无效——语法层就禁止
正确思路是“先算再筛”：把窗口结果放到子查询或 CTE 里，再在外层 WHERE 过滤
CTE 写法最清晰，也方便后续追加差值列、四舍五入等处理

用 CTE 实现“每组内超均值”的标准写法

这是目前兼容性最好、语义最直白的方案，所有主流引擎（MySQL 8.0+、PostgreSQL、SQL Server、Oracle）都支持：

WITH dept_stats AS (
  SELECT id, name, dept, salary,
         AVG(salary) OVER (PARTITION BY dept) AS avg_dept_salary
  FROM employees
)
SELECT id, name, dept, salary, ROUND(avg_dept_salary, 2) AS avg_dept_salary
FROM dept_stats
WHERE salary > avg_dept_salary;

关键点：

PARTITION BY dept 必须显式写出，漏掉就变成全表平均
别在 SELECT 里对窗口函数套 ROUND() 后又在 WHERE 引用——ROUND(AVG() OVER(), 2) 是表达式，不是列名，外层仍不可见
如果部门只有 1 人，AVG() 就等于他本人薪资，salary > avg_dept_salary 永远为假，这类边界要心里有数
想看“高出多少”，直接加 salary - avg_dept_salary 列即可，不用额外窗口

GROUP BY + JOIN 看似直观，其实更绕

这种写法也能跑通，但逻辑冗余、维护成本高：

SELECT e.id, e.name, e.dept, e.salary, ROUND(d.avg_sal, 2) AS avg_dept_salary
FROM employees e
JOIN (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
) d ON e.dept = d.dept
WHERE e.salary > d.avg_sal;

问题在于：