SQL 进阶1：如何正确统计高于公司平均薪资的部门？（从 GROUP BY 到窗口函数）

SQL 学习笔记部门薪资分析问题完整解析一、题目描述核心需求统计每个部门的平均薪资只显示平均薪资高于公司整体平均薪资的部门。这道题考查的重点是SQL 聚合函数和分组的应用子查询和相关子查询的理解聚合过滤条件HAVING子句的使用理解全局和局部两个不同维度的数据计算窗口函数vs传统聚合函数的选型能力二、建表与测试数据create table employees( emp_id int primary key, name varchar(50), dept varchar(50), salary decimal(10, 2) ); INSERT INTO employees VALUES (1,Alice,Engineering,12000), (2,Bob,Engineering,15000), (3,Carol,Marketing,8000), (4,Dave,Marketing,9000), (5,Eve,HR,7000), (6,Frank,HR,6500), (7,Grace,Engineering,18000), (8,Henry,Marketing,11000);使用这个表我们先看看数据概览Engineering 部门3 人薪资分别为 12000, 15000, 18000Marketing 部门3 人薪资分别为 8000, 9000, 11000HR 部门2 人薪资分别为 7000, 6500三、解题思路拆解第一步理解问题的三层含义计算部门维度的平均薪资我需要按部门分组计算每个部门的平均薪资计算全公司的平均薪资我需要计算所有员工薪资的整体平均值做跨维度的动态对比我需要比较部门平均是否大于全公司平均第二步逐步构建 SQL 语句很多初学者的第一反应是这样做-- 第一步统计各部门的平均薪资 SELECT dept, AVG(salary) AS avg_deptsalary FROM employees GROUP BY dept; -- 第二步计算公司整体平均薪资 SELECT AVG(salary) AS avg_allsalary FROM employees; -- 第三步比较两个结果 -- 需要在 HAVING 中用子查询来对比 SELECT dept, AVG(salary) AS avg_deptsalary FROM employees GROUP BY dept HAVING AVG(salary) (SELECT AVG(salary) FROM employees);上面这个思路看起来逻辑清晰它的核心逻辑是正确的。但在 SQL 语法的细节处理上仍有几个可以改进的地方——这正是本笔记的重点。四、核心知识点为什么不能用部门平均的平均这是很多初学者最容易犯的错误也是最值得深入理解的地方。问题的根源有人会说“我已经算出了各部门的平均薪资那我能不能再对这些平均值取一次平均”答案是绝对不行。极端案例演示假设你的公司有这样的数据小卖部部门1 个人月薪 2,000 元 → 部门平均 2,000研发部门99 个人每人月薪 12,000 元 → 部门平均 12,000现在用两种方法计算全公司平均错误方法部门平均的平均20001200027,000 元\frac{2000 12000}{2} 7,000 \text{ 元}2200012000​7,000元正确方法总薪资 / 总人数2000(12000×99)1001,190,00010011,900 元\frac{2000 (12000 \times 99)}{100} \frac{1,190,000}{100} 11,900 \text{ 元}1002000(12000×99)​1001,190,000​11,900元错误方法的危害你会发现错误方法的结果 7,000 元远低于真实的 11,900 元。只有 1 个人的小部门拉低了整体感官。这样的平均数毫无代表性。数学本质解释平均数的定义是平均数∑所有数据数据个数\text{平均数} \frac{\sum \text{所有数据}}{\text{数据个数}}平均数数据个数∑所有数据​关键词是**“有效个数”**分母而不是分组个数。正确做法总薪资总人数\frac{\text{总薪资}}{\text{总人数}}总人数总薪资​→ 每个人都被计入权重相等错误做法部门平均1部门平均2部门数\frac{\text{部门平均1} \text{部门平均2}}{\text{部门数}}部门数部门平均1部门平均2​→ 小部门和大部门的权重一样不科学一句话理解不要对聚合结果再聚合。聚合函数会丢失明细信息直接对聚合结果再做聚合就像在虚幻的数据上计算必然会得出错误的结论。五、正确解法一传统 GROUP BY HAVING 子查询方案最终代码SELECT dept, -- 对结果保留两位小数财务规范 ROUND(AVG(salary), 2) AS avg_salary FROM employees GROUP BY dept -- 在 HAVING 中用子查询对比该组平均与全公司平均 HAVING AVG(salary) (SELECT AVG(salary) FROM employees) -- 按薪资降序排列便于快速查看 ORDER BY avg_salary DESC;执行结果deptavg_salaryEngineering15000.00代码逐行解析第 1 行SELECT dept选择部门列作为结果的一个维度第 2-3 行ROUND(AVG(salary), 2) AS avg_salaryAVG(salary)计算每个组内的薪资平均值ROUND(..., 2)将结果四舍五入到 2 位小数这是财务数据的标准规范如果没有ROUND可能会出现15000.0000这样冗长的小数AS avg_salary给这个计算结果起别名便于后续引用第 4 行FROM employees GROUP BY deptGROUP BY dept按部门分组数据库会把相同 dept 的行聚合在一起第 5-7 行HAVING AVG(salary) (SELECT AVG(salary) FROM employees)HAVING对聚合后的分组进行过滤这很重要不能用 WHERE右边的子查询(SELECT AVG(salary) FROM employees)计算全公司平均比较条件当前分组的平均 全公司平均第 8 行ORDER BY avg_salary DESC结果按薪资从高到低排序这是一个最佳实践让数据更直观第一版代码的评价值得肯定的地方逻辑思路完全正确分三步明确地解决了问题正确使用了GROUP BY和HAVING正确使用了子查询来动态对比两个维度可以优化的地方没有对小数进行格式化处理ROUND函数没有对结果排序缺乏业务直观性在某些数据库中HAVING无法直接引用SELECT中的别名所以最好重复写AVG(salary)而不是用别名avg_deptsalary六、HAVING 与 WHERE 的区别为什么不能混用这是初学者最容易困惑的地方。执行顺序的本质区别SQL 的执行顺序是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BYWHERE 的角色在分组之前对原始数据行进行过滤处理对象单行数据每个员工的一条记录使用场景WHERE dept Engineering筛选出工程部的原始行HAVING 的角色在分组之后对聚合结果进行过滤处理对象聚合后的分组每个部门作为一个整体使用场景HAVING AVG(salary) 10000比较部门的平均值错误案例错误写法在 WHERE 中使用聚合条件SELECT dept, AVG(salary) FROM employees WHERE AVG(salary) (SELECT AVG(salary) FROM employees) -- 语法错误 GROUP BY dept;为什么会报错因为执行到WHERE时还没有进行GROUP BY聚合函数AVG()还找不到聚合的对象。正确写法在 HAVING 中使用聚合条件SELECT dept, AVG(salary) FROM employees GROUP BY dept HAVING AVG(salary) (SELECT AVG(salary) FROM employees); -- 正确一套对比表SQL 子句执行时机处理对象能使用聚合函数吗WHEREGROUP BY 之前单行不能HAVINGGROUP BY 之后分组能七、关键原理AVG 函数的数学本质AVG() 的底层计算当你写AVG(salary)时SQL 数据库实际上做的是AVG(salary)∑salary有效人数\text{AVG(salary)} \frac{\sum \text{salary}}{\text{有效人数}}AVG(salary)有效人数∑salary​这里的有效人数指的是非 NULL 的行数。一次全表扫描的过程1. 初始化累加器Sum 0和计数器Count 0 2. 逐行扫描 employees 表 - 见到 (1, Alice, Engineering, 12000)Sum 12000, Count - 见到 (2, Bob, Engineering, 15000)Sum 15000, Count - ... - 见到 (8, Henry, Marketing, 11000)Sum 11000, Count 3. 扫描完毕AVG Sum / Count这对 NULL 值的处理有什么启示如果某个员工的薪资为 NULL会发生什么SELECT AVG(salary) FROM employees; -- 正确 -- 相当于SUM(salary) / COUNT(非NULL的salary行数) SELECT SUM(salary) / COUNT(*) FROM employees; -- 可能错误 -- 这里的 COUNT(*) 计算的是所有行包括 NULL -- 结果会比真实平均值偏低最佳实践当你需要手动计算平均值时一定用COUNT(salary)而不是COUNT(*)。八、进阶解法窗口函数方案什么是窗口函数窗口函数是 SQL:2003 标准引入的高级特性它允许你在不减少行数的前提下进行聚合计算。最终代码SELECT DISTINCT dept, avg_deptsalary FROM ( SELECT dept, -- 计算每个部门的平均薪资局部窗口 AVG(salary) OVER(PARTITION BY dept) AS avg_deptsalary, -- 计算整个公司的平均薪资全局窗口 AVG(salary) OVER() AS avg_allsalary FROM employees ) temp -- 在外层 WHERE 中做对比过滤 WHERE avg_deptsalary avg_allsalary;执行结果deptavg_deptsalaryEngineering15000代码逐行解析第 2-10 行子查询常见的优化手段SELECT dept, AVG(salary) OVER(PARTITION BY dept) AS avg_deptsalary, AVG(salary) OVER() AS avg_allsalary FROM employees这个子查询为每一行数据都增加了两个新列员工名deptsalaryavg_deptsalaryavg_allsalaryAliceEngineering120001500010812.50BobEngineering150001500010812.50CarolMarketing80009333.3310812.50DaveMarketing90009333.3310812.50EveHR7000675010812.50FrankHR6500675010812.50GraceEngineering180001500010812.50HenryMarketing110009333.3310812.50看到了吗每一行都有了全公司平均值而且每个部门的行都正确计算出了部门平均值。第 11-13 行外层过滤WHERE avg_deptsalary avg_allsalary现在比较就很简单了Engineering15000 10812.50 保留Marketing9333.33 10812.50 删除HR6750 10812.50 删除第 1 行SELECT DISTINCTSELECT DISTINCT dept, avg_deptsalary FROM (...)因为子查询中每个部门可能有多行多个员工而我们只想看部门一次所以加DISTINCT去重。九、深度理解为什么 AVG(salary) OVER() 里的 OVER() 不能省略这是初学者最容易困惑的问题。为什么一定要写OVER()不能直接写AVG(salary)吗AVG()的双重身份在 SQL 中AVG()有两种完全不同的工作模式模式一普通聚合函数SELECT AVG(salary) -- 没有 OVER() FROM employees; -- 结果是一个单一数值10812.50模式二窗口函数SELECT AVG(salary) OVER() -- 有 OVER() FROM employees; -- 结果是 8 行每行都显示 10812.50如果去掉 OVER() 会怎样-- 错误写法会报语法错误 SELECT dept, AVG(salary) -- 没有 OVER()模糊对象 FROM employees GROUP BY dept;报错信息Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column...为什么会报错数据库的逻辑是这样的看到AVG(salary)它判断这是一个聚合函数看到子句中有GROUP BY dept它认为你想要分组汇总它要求如果用聚合函数SELECT 中的其他列也必须在GROUP BY中或者也必须是聚合函数但你的dept虽然在GROUP BY中聚合函数却没有明确对哪个分组进行计算加上 OVER() 后发生了什么-- 正确写法 SELECT dept, AVG(salary) OVER() -- 加了 OVER()明确说这是窗口函数 FROM employees;这时候数据库的理解完全变了看到AVG(salary) OVER()它识别这是一个窗口函数不是普通聚合函数窗口函数的特点是它计算聚合值但保留源表的所有行它对每一行都计算一遍AVG(salary)然后像影子一样跟在每行旁边因此SELECT dept, AVG(salary) OVER()是合理的因为原始的dept列仍然存在OVER() 和 OVER(PARTITION BY dept) 的区别写法含义范围OVER()不分区计算整个表的聚合值全局窗口OVER(PARTITION BY dept)按 dept 分区计算每个分区的聚合值局部窗口一句话总结OVER()不是可选装饰而是语义标记它告诉数据库我要用的是窗口函数模式而不是普通聚合模式。没有OVER()数据库会把你的意图理解错。十、两种解法的深度对比维度GROUP BY HAVING窗口函数核心逻辑分组 → 聚合 → 过滤不改行数 → 并行计算多个维度数据粒度只能看聚合后的组3 行既能看组也能看明细8 行代码清晰度需要子查询嵌套声明式逻辑一目了然执行效率可能多次扫表通常单次扫表兼容性MySQL 5.5 都支持需要 MySQL 8.02018年后扩展性差增加维度需要增加子查询强增加维度只需增加OVER子句场景选择建议选择 GROUP BY HAVING 的场景你需要兼容老版本数据库MySQL 5.7 及以下你只关心聚合结果不需要原始明细数据团队成员对窗口函数陌生需要代码易维护性选择窗口函数的场景你用的是现代数据库MySQL 8.0, PostgreSQL, SQL Server你需要在同一行同时看到个人数据和整体数据你需要多维度对比例如既要对比全公司又要对比同部门代码将长期维护窗口函数的可读性和可扩展性价值更大现实建议如果你没有特殊的兼容性约束强烈推荐使用窗口函数。它代表了现代 SQL 开发的方向也是高级数据分析师的标配技能。十一、易错点与学习收获最常见的 5 个错误错误 1用部门平均的平均来计算全公司平均-- 错误的思路 SELECT AVG(部门平均薪资) FROM (分组后的结果)为什么错丢失了人数这个权重信息。正确做法在分组前就计算全公司平均。错误 2在 WHERE 中使用聚合函数WHERE AVG(salary) 10000 -- 语法错误为什么错WHERE 在 GROUP BY 之前执行此时聚合函数还没有聚合对象。正确做法用 HAVING。错误 3忘记加 OVER()导致窗口函数变成普通聚合AVG(salary) -- 看起来像是要全局平均但实际被当作普通聚合函数正确做法添加OVER()。错误 4DISTINCT 用得不对SELECT dept, AVG(salary) FROM employees GROUP BY dept -- 这里自动去重不需要 DISTINCT但在窗口函数方案中SELECT DISTINCT dept, avg_deptsalary FROM (子查询) -- 这里需要 DISTINCT因为子查询有多行错误 5没有格式化小数SELECT AVG(salary) -- 可能显示 15000.0000 或 15000.0001最佳实践用ROUND(AVG(salary), 2)来规范财务数据。三个关键结论平均数是权值计算平均总和有效个数\text{平均} \frac{\text{总和}}{\text{有效个数}}平均有效个数总和​而不是组的平均的平均。WHERE 和 HAVING 的分治思想WHERE过滤原始数据HAVING过滤聚合数据OVER() 的本质是模式切换AVG(salary)普通聚合返回 1 行AVG(salary) OVER()窗口聚合返回原表行数十二、总结与后续学习路径这道题的学习价值学习价值理论知识理解平均数的数学本质规避常见陷阱SQL 语法掌握 GROUP BY、HAVING、子查询、窗口函数工程实践学会数据格式化、排序、去重等规范操作架构思维对比两种解决方案的权衡兼容性 vs 可读性推荐的进阶方向掌握更多窗口函数ROW_NUMBER(),RANK(),LAG(),LEAD()等多维度对比不仅对比全公司还要对比行业平均、历史平均等性能优化在大表上测试理解索引和执行计划对聚合性能的影响实战应用在真实数据集中应用这些技巧体会不同方案的优劣最后一句话从我能写出正确的 SQL到我知道为什么这样写是最优的这中间的差距就是从初级到高级的进阶之路。希望通过这道题你能深刻理解 SQL 的每一个子句背后的设计意图。