615. 平均工资：部门与公司比较（难度：困难）

题目描述

（通过次数9,124 | 提交次数21,758，通过率67.40%）

给如下两个表，写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果 （高 / 低 / 相同）。
表：salary
| id | employee_id | amount | pay_date   |
|----|-------------|--------|------------|
| 1  | 1           | 9000   | 2017-03-31 |
| 2  | 2           | 6000   | 2017-03-31 |
| 3  | 3           | 10000  | 2017-03-31 |
| 4  | 1           | 7000   | 2017-02-28 |
| 5  | 2           | 6000   | 2017-02-28 |
| 6  | 3           | 8000   | 2017-02-28 |
employee_id字段是表employee中employee_id字段的外键。
| employee_id | department_id |
|-------------|---------------|
| 1           | 1             |
| 2           | 2             |
| 3           | 2             |
对于如上样例数据，结果为：
| pay_month | department_id | comparison  |
|-----------|---------------|-------------|
| 2017-03   | 1             | higher      |
| 2017-03   | 2             | lower       |
| 2017-02   | 1             | same        |
| 2017-02   | 2             | same        |
解释：
在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33...
由于部门 '1' 里只有一个 employee_id为 '1' 的员工，所以部门 '1' 的平均工资就是此人的工资9000 。因为 9000 > 8333.33 ，所以比较结果是 'higher'。
第二个部门的平均工资为employee_id为 '2' 和 '3' 两个人的平均工资，为 (6000+10000)/2=8000 。因为 8000 < 8333.33 ，所以比较结果是 'lower' 。
在二月用同样的公式求平均工资并比较，比较结果为 'same' ，因为部门 '1' 和部门 '2' 的平均工资与公司的平均工资相同，都是 7000 。
来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/average-salary-departments-vs-company

//测试数据
Create table If Not Exists Salary (id int, employee_id int, amount int, pay_date date);
Create table If Not Exists Employee (employee_id int, department_id int);

insert into Salary (id, employee_id, amount, pay_date) values ('1', '1', '9000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('2', '2', '6000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('3', '3', '10000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('4', '1', '7000', '2017/02/28');
insert into Salary (id, employee_id, amount, pay_date) values ('5', '2', '6000', '2017/02/28');
insert into Salary (id, employee_id, amount, pay_date) values ('6', '3', '8000', '2017/02/28');

insert into Employee (employee_id, department_id) values ('1', '1');
insert into Employee (employee_id, department_id) values ('2', '2');
insert into Employee (employee_id, department_id) values ('3', '2');

解题思路

官方标记，这是一道困难题。而且，如果你先翻到最下面偷瞄了眼参考SQL，大概也会有这样的错觉。
实际上，这是一道非常简单的题目。只是用到的知识点比较多，解题步骤相对也繁琐一些。
源表Salary中记录了所有员工每个月的工资。
源表Employee中记录了所有员工所属的部门。
题目要求：计算出，所有月份所有部门的平均工资与全公司平均工资的比较结果。
那么，我们可以通过以下步骤来实现：
第一步：计算出全公司每个月的平均工资；
因为发薪日期具体到某一天，因此需要将日期转换为月份，使用DATE_FORMAT日期函数可以实现。
然后使用GROUP BY + AVG直接按月份分组统计即可。
这里需要对平均工资保留2位小数，可以使用ROUND函数实现。
第二步：计算出每个部门每个月的平均工资；
与第一步类似，只是需要先关联出每个员工所属的部门。在分组统计时，使用月份+部门ID分组即可。
第三步：对比第一步和第二步的结果；
使用INNER JOIN关联出每个月每个部门的平均工资和全公司平均工资，使用CASE WHEN+条件比较，得出结果并返回。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

with
tmp1 as (
    select
        date_format(a.pay_date,'%Y-%m') pay_month,
        round(avg(a.amount),2) avg_company 
    from salary a
    group by date_format(a.pay_date,'%Y-%m')
),
tmp2 as (
    select
        date_format(a.pay_date,'%Y-%m') pay_month,
        b.department_id,
        round(avg(a.amount),2) avg_department
    from salary a
    inner join employee b
    on a.employee_id = b.employee_id
    group by date_format(a.pay_date,'%Y-%m'),
             b.department_id
)
select
    a.pay_month,
    a.department_id,
    case when a.avg_department > b.avg_company then 'higher'
         when a.avg_department = b.avg_company then 'same'
         else 'lower' end comparison
from tmp2 a
inner join tmp1 b
on a.pay_month = b.pay_month;