leetcode困难之615.平均工资

给如下两个表，写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果 （高 / 低 / 相同）。

表： 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
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解答

注意，考虑到不是所有的员工当月发薪都是同一天，所以如果日期转换成月份是到最后才改，要去重

法一

窗口函数也是可以直接互相比较的

select distinct * 
from (
    select substr(s.pay_date,1,7) pay_month, e.department_id,
        case when avg(amount) over(partition by e.department_id,s.pay_date)>
                avg(amount) over(partition by s.pay_date) then 'higher'
            when avg(amount) over(partition by e.department_id,s.pay_date)<
                avg(amount) over(partition by s.pay_date) then 'lower'
            else 'same'
        end as comparison
    from salary s left join employee e using(employee_id)
    ) t 
order by 1 desc

法二

select distinct substr(t1.pay_date,1,7) pay_month,department_id,
    case when t1.ag_sal_dep>t2.ag_sal then 'higher'
        when t1.ag_sal_dep<t2.ag_sal then 'lower'
        else 'same'
    end comparison
from
    (select pay_date,department_id,avg(amount) ag_sal_dep
    from employee e left join salary s using(employee_id)
    group by pay_date,department_id
    ) t1
left join 
    (select pay_date,avg(amount) ag_sal
    from salary
    group by pay_date
    ) t2 using(pay_date)
order by  substr(t1.pay_date,1,7) desc,department_id