部门平均工资和公司平均工资比较
LeetCode 615 · 多粒度聚合、窗口 AVG、CASE 比较
面试怎么回答
这是两种粒度的聚合比较。我会分别算「月份」的公司平均和「月份 + 部门」的部门平均，可以用两个窗口 AVG，也可以两个 GROUP BY 子查询按月份关联，然后 CASE WHEN 输出 higher / lower / same。要注意公司平均是对所有工资记录求平均，不是对部门平均再求平均。
先把这段讲顺；面试官追问时，再展开下面的原理和细节。
详细讲解
题意
工资表记录每个员工每次发薪的金额和日期，员工表记录员工所属部门。对每个发薪月份、每个部门，比较部门平均工资和全公司平均工资，输出 higher / lower / same。
表结构
- salary：id（主键）、employee_id、amount、pay_date
- employee：employee_id（主键）、department_id
思路
需要两种粒度的平均值：「月份」粒度的公司平均，和「月份 + 部门」粒度的部门平均。用两个窗口 AVG 分别按不同的分区计算，挂在同一行上，再用 CASE 比较。最后每个（月份, 部门）只保留一行。
参考解法
select
pay_month,
department_id,
(case when avgs>ts then 'higher'
when avgs<ts then 'lower'
else 'same' end) as comparison
from
(
select
date_format(pay_date,'%Y-%m')pay_month,
department_id,
avg(amount) over(partition by date_format(pay_date,'%Y-%m') )ts,
avg(amount) over(partition by date_format(pay_date,'%Y-%m'),department_id) avgs
from salary s
left join employee e
on s.employee_id = e.employee_id
)t1
group by pay_month, department_id另一种写法：先分别聚合再关联，不依赖窗口函数
SELECT d.pay_month, d.department_id,
CASE WHEN d.avg_amt > c.avg_amt THEN 'higher'
WHEN d.avg_amt < c.avg_amt THEN 'lower' ELSE 'same' END AS comparison
FROM (
SELECT DATE_FORMAT(s.pay_date, '%Y-%m') AS pay_month, e.department_id, AVG(s.amount) AS avg_amt
FROM salary s JOIN employee e ON s.employee_id = e.employee_id
GROUP BY pay_month, e.department_id
) d
JOIN (
SELECT DATE_FORMAT(pay_date, '%Y-%m') AS pay_month, AVG(amount) AS avg_amt
FROM salary GROUP BY pay_month
) c ON d.pay_month = c.pay_month易错点
- 原解法用 GROUP BY 去重但 SELECT 了非分组列，在 ONLY_FULL_GROUP_BY 模式下会报错，改成 SELECT DISTINCT 更规范。
- 「公司平均」要按工资记录平均，而不是先算部门平均再平均（部门人数不同时两者不等）。
- 月份要统一格式，比如 DATE_FORMAT(pay_date, '%Y-%m')。