LeetCode面试SQL-平均工资-部门与公司比较

数据仓库晨曦

发布于 2024-10-10 16:15:03

12100

代码可运行

文章被收录于专栏：数据仓库技术数据仓库技术

运行总次数：0

代码可运行

一、题目

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

表: t4_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 字段是表 t4_employee 中 employee_id 字段的外键。

+--------------+----------------+
| employee_id  | department_id  |
+--------------+----------------+
| 1            | 1              |
| 2            | 2              |
| 3            | 2              |
+--------------+----------------+

对于如上样例数据，结果为：

+------------+----------------+-------------+
| pay_month  | department_id  | comparison  |
+------------+----------------+-------------+
| 2017-02    | 1              | same        |
| 2017-02    | 2              | same        |
| 2017-03    | 1              | higher      |
| 2017-03    | 2              | lower       |
+------------+----------------+-------------+

解释

在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33...

由于部门 '1' 里只有一个 employee_id 为 '1' 的员工，所以部门 '1' 的平均工资就是此人的工资 9000 。因为 9000 > 8333.33 ，所以比较结果是 'higher'。

第二个部门的平均工资为 employee_id 为 '2' 和 '3' 两个人的平均工资，为 (6000+10000)/2=8000 。因为 8000 < 8333.33 ，所以比较结果是 'lower' 。

在二月用同样的公式求平均工资并比较，比较结果为 'same' ，因为部门 '1' 和部门 '2' 的平均工资与公司的平均工资相同，都是 7000 。

二、分析

本题相对较容易，考察表的关联，然后考察聚合函数开窗，叠加条件判断case when。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️

三、SQL

1.在薪资表中关联出雇员的部门

薪资表关联雇员表，计算出每个雇员所属部门，调整pay_date为pay_month

执行SQL

select t1.id,
       t1.employee_id,
       t1.amount,
       date_format(to_date(t1.pay_date), 'yyyy-MM') as pay_month,
       t2.department_id
from t4_salary t1
         join t4_employee t2
              on t1.employee_id = t2.employee_id

SQL结果

+-----+--------------+---------+------------+----------------+
| id  | employee_id  | amount  | pay_month  | department_id  |
+-----+--------------+---------+------------+----------------+
| 1   | 1            | 9000    | 2017-03    | 1              |
| 2   | 2            | 6000    | 2017-03    | 2              |
| 3   | 3            | 10000   | 2017-03    | 2              |
| 4   | 1            | 7000    | 2017-02    | 1              |
| 5   | 2            | 6000    | 2017-02    | 2              |
| 6   | 3            | 8000    | 2017-02    | 2              |
+-----+--------------+---------+------------+----------------+
+-----------+-----------+-------------+-------------+-----+

2.开窗计算出部门和公司当月的平均薪资

使用聚合函数开窗，计算出公司和部门每个月的平均薪水。因为我们想要的是部门+月份粒度的数据，所以需要进行去重处理。 执行SQL

with t as (select t1.id,
                  t1.employee_id,
                  t1.amount,
                  date_format(to_date(t1.pay_date), 'yyyy-MM') as pay_month,
                  t2.department_id
           from t4_salary t1
                    join t4_employee t2
                         on t1.employee_id = t2.employee_id)
select pay_month,
       department_id,
       department_avg_amount,
       company_avg_amount
from (select pay_month,
             department_id,
             avg(amount) over (partition by pay_month,department_id) as department_avg_amount,
             avg(amount) over (partition by pay_month)               as company_avg_amount
      from t) tt
group by pay_month,
         department_id,
         department_avg_amount,
         company_avg_amount

SQL结果

+------------+----------------+------------------------+---------------------+
| pay_month  | department_id  | department_avg_amount  | company_avg_amount  |
+------------+----------------+------------------------+---------------------+
| 2017-02    | 1              | 7000.0                 | 7000.0              |
| 2017-02    | 2              | 7000.0                 | 7000.0              |
| 2017-03    | 1              | 9000.0                 | 8333.333333333334   |
| 2017-03    | 2              | 8000.0                 | 8333.333333333334   |
+------------+----------------+------------------------+---------------------+

3.比较部门和公司的平均薪水，得出最终比较结果

通过比较department_avg_amount和company_avg_amount的大小，得出部门和工资平均薪水的大小

执行SQL

with t as (select t1.id,
                  t1.employee_id,
                  t1.amount,
                  date_format(to_date(t1.pay_date), 'yyyy-MM') as pay_month,
                  t2.department_id
           from t4_salary t1
                    join t4_employee t2
                         on t1.employee_id = t2.employee_id)
select pay_month,
       department_id,
       max(case
               when department_avg_amount > company_avg_amount then 'higher'
               when department_avg_amount = company_avg_amount then 'same'
               when department_avg_amount < company_avg_amount then 'lower'
           end) as comparison

from (select pay_month,
             department_id,
             avg(amount) over (partition by pay_month,department_id) as department_avg_amount,
             avg(amount) over (partition by pay_month)               as company_avg_amount
      from t) tt
group by pay_month,
         department_id

SQL结果

+------------+----------------+-------------+
| pay_month  | department_id  | comparison  |
+------------+----------------+-------------+
| 2017-02    | 1              | same        |
| 2017-02    | 2              | same        |
| 2017-03    | 1              | higher      |
| 2017-03    | 2              | lower       |
+------------+----------------+-------------+

四、建表语句和数据插入

--建表语句
CREATE TABLE t4_salary(
id bigint,
employee_id bigint,
amount bigint,
pay_date string
) COMMENT '薪资表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;

CREATE TABLE t4_employee(
employee_id bigint,
department_id bigint
) COMMENT '雇员表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;

-- 插入数据
insert into t4_salary(id,employee_id,amount,pay_date)
values
(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');

insert into t4_employee(employee_id,department_id)
values
(1,1),
(2,2),
(3,2);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-10-10，如有侵权请联系 cloudcommunity@tencent.com 删除

数据