107，SQL训练之，力扣，615. 平均工资：部门与公司比较

最新推荐文章于 2024-10-27 22:09:06 发布

凡梦_leo

最新推荐文章于 2024-10-27 22:09:06 发布

阅读量642

点赞数 19

分类专栏： SQL实战训练文章标签： leetcode 算法职场和发展 pandas sql 数据库 linux

本文链接：https://blog.csdn.net/qq_55006020/article/details/142601155

版权

SQL实战训练专栏收录该内容

150 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

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