SQL练习00010

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

表： 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

--建表语句
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)
Truncate table salary
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')
Truncate table employee
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')



--答案
select
  pay_month,
  department_id,
  case
    when avg_d>avg_p then 'higher'
    when avg_d<avg_p then 'lower'
    else 'same'
  end as comparison
from
  (select
    substr(pay_date,1,7) as pay_month,
    department_id,
    avg(amount) over(partition by pay_date) as avg_p,
    avg(amount) over(partition by pay_date,department_id) as avg_d
  from
    salary s join
    employee e on 
    s.employee_id=e.employee_id
  ) 
  a group by 
  pay_month,department_id order by 
  pay_month desc,department_id