要求:给如下两个表,写一个查询语句,求出在每一个工资发放日,每个部门的平均工资与公司的平均工资的比较结果 (高 / 低 / 相同)。
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表:
| employee_id | department_id |
|-------------|---------------|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
Result Table:
| pay_month | department_id | comparison |
|-----------|---------------|-------------|
| 2017-03 | 1 | higher |
| 2017-03 | 2 | lower |
| 2017-02 | 1 | same |
| 2017-02 | 2 | same |
分析:
1.通过窗口聚合函数avg() over(),通过日期分组求出每个月公司的平均工资,通过日期和部门分组求出每个部门每月的平均工资
2.通过比较公司平均工资和部门平均工资得出比较结果
SQL语句:
SELECT DISTINCT pay_month,department_id,CASE WHEN v1>v2 THEN 'higher'
WHEN v1=v2 THEN 'same' ELSE 'lower' END comparison
FROM
(
SELECT SUBSTR(a.pay_date,1,7) AS pay_month, b.department_id,
AVG(a.amount) over(PARTITION BY a.pay_date,b.department_id) AS v1,
AVG(a.amount) over(PARTITION BY a.pay_date) AS v2
FROM salary a
JOIN employee b
ON a.employee_id=b.employee_id
)c;