- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
Salary
+-------------+------+ | 列名 | 类型 | +-------------+------+ | id | int | | employee_id | int | | amount | int | | pay_date | date | +-------------+------+ 在 SQL 中,id 是该表的主键列。 该表的每一行表示一个员工一个月的薪资。 employee_id 是来自 Employee 表的外键(reference 列)。表:
Employee
+---------------+------+ | 列名 | 类型 | +---------------+------+ | employee_id | int | | department_id | int | +---------------+------+ 在 SQL 中,employee_id 是该表的主键列。 该表的每一行表示一个员工所属的部门。找出各个部门员工的平均薪资与公司平均薪资之间的比较结果(更高 / 更低 / 相同)。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1:
输入: 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 表: +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2 | +-------------+---------------+ 输出: +-----------+---------------+------------+ | pay_month | department_id | comparison | +-----------+---------------+------------+ | 2017-02 | 1 | same | | 2017-03 | 1 | higher | | 2017-02 | 2 | same | | 2017-03 | 2 | lower | +-----------+---------------+------------+ 解释: 在三月,公司的平均工资是 (9000+6000+10000)/3 = 8333.33... 部门 '1' 的平均薪资是 9000,因为该部门只有一个员工,其员工号为 '1'。因为 9000 > 8333.33,所以比较结果为 'higher' 部门 '2' 的平均薪资是(6000 + 10000)/ 2 = 8000,该平均薪资是员工号 '2' 和 '3' 的薪资的平均值。因为 8000 < 8333.33,比较结果为 'lower'。 根据同样的公式,对于二月份的平均薪资比较,结果为 'same',因为部门 '1' 和 '2' 都与公司的平均薪资相同,即为 7000。
三,建表语句
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')
四,分析
题解:
表:薪资表
字段:薪资id,员工id,部门id,日期
表 员工表
字段: 员工id,部门id
求每个月的部门平均薪资和每个月公式的平均薪资 比较
代码实现表格分析的逻辑:
第一步 拼接2个表;
第二步:扩展一个日期列,截取年和月;
第三步: 分别以日期分组 计算工资每个月的平均薪资 和以日期与部门分组 计算公式每个部门 每个月的平均薪;
第四步,映射指定的列, 部门,日期,部门平均薪资列,公式平均薪资列,然后去重;
最后一步:用每个月的部门平均薪资和 每个月公式的平均薪资比较 ;最后修改列名。
五,SQL解答
with t1 as (
select
s.employee_id,e.department_id,amount,pay_date
from salary s join employee e on s.employee_id = e.employee_id
)
,t2 as (
select
employee_id, department_id, amount, pay_date,
date_format(pay_date,'%Y-%m') as date1
from t1
),t3 as (
select
employee_id, department_id, amount, pay_date, date1,
avg(amount) over(partition by date1) gongsi_avg,
avg(amount) over(partition by date1,department_id) dep_avg
from t2
),t4 as (
select distinct department_id,date1,gongsi_avg,dep_avg
from t3
)
select
date1 as pay_month,
department_id,
case
when dep_avg > gongsi_avg then 'higher'
when dep_avg = gongsi_avg then 'same'
when dep_avg < gongsi_avg then 'lower'
end as comparison
from t4;
六,验证
七,知识点总结
- 内连接的运用
- 日期函数的运用 从年-月-日 总截取 年-月
- 平均数开窗的运用 分别以不同的列开窗
- 去重的运用
- case when语法的运用 转换必备
- 求每个月 公式平均薪资 和每个月每个部门的平均薪资 比较情况~
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用