100，[非连续的累加和]SQL训练之，力扣，579. 查询员工的累计薪水 [sql连续问题的巅峰难度之一]

最新推荐文章于 2024-10-05 00:24:42 发布

凡梦_leo

最新推荐文章于 2024-10-05 00:24:42 发布

阅读量665

点赞数 8

分类专栏： SQL实战训练文章标签： sql leetcode 数据库大数据 python pandas mysql

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

版权

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

120 篇文章 0 订阅

订阅专栏

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

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表：Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| month       | int  |
| salary      | int  |
+-------------+------+
(id, month) 是该表的主键(具有唯一值的列的组合)。
表中的每一行表示 2020 年期间员工一个月的工资。

编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总 。

员工的 累计工资汇总 可以计算如下:

对于该员工工作的每个月，将该月和 前两个月 的工资加起来。这是他们当月的 3 个月总工资和。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为 0 。
不要在摘要中包括员工 最近一个月 的 3 个月总工资和。
不要包括雇员 没有工作 的任何一个月的 3 个月总工资和。

返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序。

结果格式如下所示。

示例 1

输入：
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)
+----+-------+--------+

三，建表语句

Create table If Not Exists Employee (id int, month int, salary int);
Truncate table Employee;
insert into Employee (id, month, salary) values ('1', '1', '20');
insert into Employee (id, month, salary) values ('2', '1', '20');
insert into Employee (id, month, salary) values ('1', '2', '30');
insert into Employee (id, month, salary) values ('2', '2', '30');
insert into Employee (id, month, salary) values ('3', '2', '40');
insert into Employee (id, month, salary) values ('1', '3', '40');
insert into Employee (id, month, salary) values ('3', '3', '60');
insert into Employee (id, month, salary) values ('1', '4', '60');
insert into Employee (id, month, salary) values ('3', '4', '70');
insert into Employee (id, month, salary) values ('1', '7', '90');
insert into Employee (id, month, salary) values ('1', '8', '90');

四，分析

更详细的解题思路可以参阅本人用 pandas方法解答的这题文档

题解:

表:员工表

字段:员工id,员工薪资的月份,员工的薪资

求员工3月的累计薪资,非连续的累计薪资,且最后一个月不纳入其中

第一步,通过sum 开窗 range指定连续的范围巧妙调整窗口大小成功拿到非连续的累计和

    select
    id, month, salary,
    sum(salary) over (partition by id order by month range 2 preceding) rn1,  #累计计算 以range2开窗
    row_number() over (partition by id order by month desc) ro
    from employee

第二步,简简单单的分组去掉tob1

五，SQL解答

with t1 as (
    select
    id, month, salary,
    sum(salary) over (partition by id order by month range 2 preceding) rn1,  #累计计算 以range2开窗
    row_number() over (partition by id order by month desc) ro
    from employee
)
select id,month,rn1 as salary  from t1 where ro !=1;