615. 平均工资：部门与公司比较

时间序列的处理，以及窗口函数， case when的使用

select distinct * from
(select date_format(s.pay_date, '%Y-%m') as 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 'lower'
             when avg(amount) over (partition by e.department_id, s.pay_date) 
                > avg(amount) over (partition by s.pay_date) then 'higher'
             else 'same' end as comparison
from salary s left join employee e
on s.employee_id = e.employee_id) a
order by 1 desc