1 题目描述
表：Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| month | int |
| salary | int |
+-------------+------+
(id, month) 是该表的主键(具有唯一值的列的组合) 表中的每一行表示 2020 年期间员工一个月的工资
编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总
员工的 累计工资汇总 可以计算如下:
- 对于该员工工作的每个月，将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3 个月总工资****和 。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为
0 - 不要 在摘要中包括员工 最近一个月 的 3 个月总工资和
- 不要 包括雇员 没有工作 的任何一个月的 3 个月总工资和
返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序
2 测试用例
输入： Employee table:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 1 | 2 | 30 |
| 2 | 2 | 30 |
| 3 | 2 | 40 |
| 1 | 3 | 40 |
| 3 | 3 | 60 |
| 1 | 4 | 60 |
| 3 | 4 | 70 |
| 1 | 7 | 90 |
| 1 | 8 | 90 |
+----+-------+--------+
输出：
+----+-------+--------+
| id | month | Salary |
+----+-------+--------+
| 1 | 7 | 90 |
| 1 | 4 | 130 |
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
+----+-------+--------+
解释： 员工 “1” 有 5 条工资记录，不包括最近一个月的 “8”:
- 第 '7' 个月为 90
- 第 '4' 个月为 60
- 第 '3' 个月是 40
- 第 '2' 个月为 30
- 第 '1' 个月为 20 因此，该员工的累计工资汇总为:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 7 | 90 | (90 + 0 + 0)
| 1 | 4 | 130 | (60 + 40 + 30)
| 1 | 3 | 90 | (40 + 30 + 20)
| 1 | 2 | 50 | (30 + 20 + 0)
| 1 | 1 | 20 | (20 + 0 + 0)
+----+-------+--------+
请注意，'7' 月的 3 个月的总和是 90，因为他们没有在 '6' 月或 '5' 月工作
员工 '2' 只有一个工资记录('1' 月)，不包括最近的 '2' 月
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 2 | 1 | 20 | (20 + 0 + 0)
+----+-------+--------+
员工 '3' 有两个工资记录，不包括最近一个月的 '4' 月:
- 第 '3' 个月为 60
- 第 '2' 个月是 40 因此，该员工的累计工资汇总为:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 3 | 3 | 100 | (60 + 40 + 0)
| 3 | 2 | 40 | (40 + 0 + 0)
+----+-------+--------+
3 解题思路
计算当前和前两个月的 salary 之和, 能立马想到排名函数 rank() 和 窗口函数 lead(), lag()
3.1 解法 1: rank() + lead()
- 按照
id分组, month 倒序排序, 使用rank()进行排名, 使用lead()查询上一行和上两个数据的month, salary
select id,
month,
salary,
rank() over (partition by id order by month desc) as rn,
lead(month, 1) over (partition by id order by month desc) as pre_one_month,
lead(month, 2) over (partition by id order by month desc) as pre_two_month,
lead(salary, 1) over (partition by id order by month desc) as pre_one_salary,
lead(salary, 2) over (partition by id order by month desc) as pre_two_salary
from Employee
查询结果
+--+-----+------+--+-------------+-------------+--------------+--------------+
|id|month|salary|rn|pre_one_month|pre_two_month|pre_one_salary|pre_two_salary|
+--+-----+------+--+-------------+-------------+--------------+--------------+
|1 |8 |90 |1 |7 |4 |90 |60 |
|1 |7 |90 |2 |4 |3 |60 |40 |
|1 |4 |60 |3 |3 |2 |40 |30 |
|1 |3 |40 |4 |2 |1 |30 |20 |
|1 |2 |30 |5 |1 |null |20 |null |
|1 |1 |20 |6 |null |null |null |null |
|2 |2 |30 |1 |1 |null |20 |null |
|2 |1 |20 |2 |null |null |null |null |
|3 |4 |70 |1 |3 |2 |60 |40 |
|3 |3 |60 |2 |2 |null |40 |null |
|3 |2 |40 |3 |null |null |null |null |
+--+-----+------+--+-------------+-------------+--------------+--------------+
- 按照题目要求不统计员工最新一个月的情况,使用
rn != 1过滤数据, 然后统计当前月在内的近三个月的工资总和, 如果前两个月中没有工资记录, 则按0计算
select id,
month,
salary + if(month - 1 = pre_one_month, pre_one_salary, 0) +
if(month - 2 = pre_two_month, pre_two_salary, 0) as salary
from (select id,
month,
salary,
rank() over (partition by id order by month desc) as rn,
lead(month, 1) over (partition by id order by month desc) as pre_one_month,
lead(month, 2) over (partition by id order by month desc) as pre_two_month,
lead(salary, 1) over (partition by id order by month desc) as pre_one_salary,
lead(salary, 2) over (partition by id order by month desc) as pre_two_salary
from Employee) as e
where rn != 1;
解释:
if(month - 1 = pre_one_month, pre_one_salary, 0) + if(month - 2 = pre_two_month, pre_two_salary, 0) as salary 是使用 if 判断当前月份的前一个月和前两个月的工资记录是否存在, 如果不存在则按照 0 计算
查询结果
+--+-----+------+
|id|month|salary|
+--+-----+------+
|1 |7 |90 |
|1 |4 |130 |
|1 |3 |90 |
|1 |2 |50 |
|1 |1 |20 |
|2 |1 |20 |
|3 |3 |100 |
|3 |2 |40 |
+--+-----+------+
3.2 解法 2: rank() + sum() + rang
- 按照
id分组,对month倒序排序,计算每位员工的月份排名, 并计算近三个月的工资总和, 使用sum(salary) over (partition by id order by month asc range 2 preceding)能按照员工分组统计近三个月的工资总和(是逻辑上的三个月, 假如当前月是 7 月, 只会统计 5,6,7 三个月的工资总和, 假如 5,6 月没有记录, 也按照 0 进行统计)
select id,
month,
rank() over (partition by id order by month desc) as rk,
sum(salary) over (partition by id order by month asc range 2 preceding) as total
from Employee
查询结果如下
+--+-----+------+--+-----+
|id|month|salary|rn|total|
+--+-----+------+--+-----+
|1 |1 |20 |6 |20 |
|1 |2 |30 |5 |50 |
|1 |3 |40 |4 |90 |
|1 |4 |60 |3 |130 |
|1 |7 |90 |2 |90 |
|1 |8 |90 |1 |180 |
|2 |1 |20 |2 |20 |
|2 |2 |30 |1 |50 |
|3 |2 |40 |3 |40 |
|3 |3 |60 |2 |100 |
|3 |4 |70 |1 |170 |
+--+-----+------+--+-----+
- 题目要求不需要统计最新月份的工资数据, 使用
rn > 1过滤, 并对结果进行id升序,month降序排序
select e.id, e.month, e.total as salary
from (select id,
month,
salary,
rank() over (partition by id order by month desc) as rn,
sum(salary) over (partition by id order by month asc range 2 preceding) as total
from Employee) as e
where rn > 1
order by e.id asc, e.month desc;
查询结果
+--+-----+------+
|id|month|salary|
+--+-----+------+
|1 |7 |90 |
|1 |4 |130 |
|1 |3 |90 |
|1 |2 |50 |
|1 |1 |20 |
|2 |1 |20 |
|3 |3 |100 |
|3 |2 |40 |
+--+-----+------+
