【LeetCode-SQL】579. 查询员工的累计薪水

一、题目

Employee 表保存了一年内的薪水信息。

请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。

结果请按 Id 升序，然后按 Month 降序显示。

示例：
输入：

| 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     |

输出：

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |

解释：

员工 ‘1’ 除去最近一个月（月份 ‘4’），有三个月的薪水记录：月份 ‘3’ 薪水为 40，月份 ‘2’ 薪水为 30，月份 ‘1’ 薪水为 20。

所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |

员工 ‘2’ 除去最近的一个月（月份 ‘2’）的话，只有月份 ‘1’ 这一个月的薪水记录。

| Id | Month | Salary |
|----|-------|--------|
| 2  | 1     | 20     |

员工 ‘3’ 除去最近一个月（月份 ‘4’）后有两个月，分别为：月份 ‘3’ 薪水为 60 和 月份 ‘2’ 薪水为 40。所以各月的累计情况如下：

| Id | Month | Salary |
|----|-------|--------|
| 3  | 3     | 100    |
| 3  | 2     | 40     |

二、解决

1、sum() over()

思路：

按id分组后，降序累加最近3个月工资。

代码-版本1：

select
	id, month,
	sum(salary) over(partition by id
									    order by month desc
										range between 0 preceding and 2 following
	) salary
from employee where (id, month) not in (
 select id, max(month) month from employee group by id
);

代码-版本2：

select
	id,	month,
	sum(salary) over(partition by id order by month range 2 preceding) salary
from (
	select
		id, month, salary,
		row_number() over(partition by id order by month desc) rk
	from employee
) e1 where rk >= 2
order by id, month desc;

学习：

1、滑动窗口

语法： rows n preceding between n following ，precding 在前， following 在后，否则会报错， current row 前后都可以

rows：行号为基准
range：以 order by 为基准
n preceding: 前 n 行
n following：后 n 行
current row：当前行
unbounded preceding：窗口第一行
unbounded following：窗口最后一行

2、窗口区间

n preceding 当前行的前 n 行
当 n = 1

如果当前行在边界（第一行），前面一行会忽略
如果当前行在第二行，会和第一行的 salary 计算
如果当前行在第三行，会和第二行的 salary 计算
以此类推
select
	id, month, salary,
	sum(salary) over(partition by id order by month range 1 preceding) w_salary
from employee

2、join

思路：

e1.month >= e2.month and e1.month < e2.month + 3 怎么理解呢，最近三个月就加 3 吗？这就要配合 sum(e2.salary) 来理解了。

# 结合代码理解
select
	e1.id, e1.month,
	any_value(e1.salary),
	any_value(e2.id),
	any_value(e2.month),
	any_value(e2.salary),
	sum(e2.salary) salary,
	group_concat(e2.salary),
from employee e1, employee e2
where e1.id = e2.id
and e1.month > e2.month
group by e1.id, e1.month
order by e1.id, e1.month desc;

代码：

select
	e1.id id, e1.month month,
	sum(e2.salary) salary
from employee e1, employee e2
where e1.id = e2.id
and e1.month >= e2.month
and e1.month < e2.month + 3
and (e1.id, e1.month) not in (
	select id, max(month) month from employee group by id
)
group by e1.id, e1.month
order by e1.id, e1.month desc;

三、参考

1、三种方法详细讲解（连接，查两表，窗口函数）——查询员工的累计薪水
2、窗口函数定义 #40
3、579. Find Cumulative Salary of an Employee
4、自定义前N月内累计薪水 题解法