LeetCode_sql_day07(579. 查询员工的累计薪水，2173.最多连胜的次数)

描述：579. 查询员工的累计薪水

编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总 。
员工的 累计工资汇总 可以计算如下:
对于该员工工作的每个月，将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3 个月总工资和 。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为 0 。
不要 在摘要中包括员工 最近一个月 的 3 个月总工资和。
不要 包括雇员 没有工作 的任何一个月的 3 个月总工资和。
返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序。

输出：

数据准备：

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

分析：

①简化表：先去掉最近一月的各员工薪资信息，可以考虑用row_number()开窗函数根据id分类month降序排序 

select *,
       row_number() over (partition by id order by month desc)r1
      from employee

②过滤数据 将上述三行筛选掉，再通过sum()开窗函数计算前两个月和该月工资，最后进行排序整理与题目要求一致（这里使用range不是rows）

with t1 as(
select *,
       row_number() over (partition by id order by month desc)r1
      from employee
       )
select id,
       month,
       sum(salary) over(partition by id order by month range 2 preceding )Salary
from t1
where r1 != 1
order by id,month desc

代码：

with t1 as(
select *,
       row_number() over (partition by id order by month desc)r1
      from employee
       )
select id,
       month,
       sum(salary) over(partition by id order by month range 2 preceding )Salary
from t1
where r1 != 1
order by id,month desc
;

总结：

range与rows的区别：

在本题的基础上再添加一条数据

insert into Employee (id, month, salary) values ('1', '5', '30')

如图：

range版

rows版

描述：2173.最多连胜的次数

计算每个参赛选手最多的连胜数

输出：

数据准备：

drop database if exists db_1;

create database db_1;

use db_1;

Create table If Not Exists Matches (player_id int, match_day date, result ENUM('Win', 'Draw', 'Lose'));

Truncate table Matches;

insert into Matches (player_id, match_day, result) values ('1', '2022-01-17', 'Win');

insert into Matches (player_id, match_day, result) values ('1', '2022-01-18', 'Win');

insert into Matches (player_id, match_day, result) values ('1', '2022-01-25', 'Win');

insert into Matches (player_id, match_day, result) values ('1', '2022-01-31', 'Draw');

insert into Matches (player_id, match_day, result) values ('1', '2022-02-08', 'Win');

insert into Matches (player_id, match_day, result) values ('2', '2022-02-06', 'Lose');

insert into Matches (player_id, match_day, result) values ('2', '2022-02-08', 'Lose');

insert into Matches (player_id, match_day, result) values ('3', '2022-03-30', 'Win');

分析：

①对题目分析，如何判断是连胜，此处需要用到两个row_number()做差得到的结果

with t1 as(
select *,row_number() over(partition by player_id order by match_day)r1 from Matches)
,t2 as (
select *,row_number() over (partition by player_id order by match_day)r2 from t1 where result = 'Win')
select *,(r1-r2) r3 from t2

②观察上表就可以根据player_id 和r3进行分组，用count求出各参赛选手连胜场次

select player_id,count(r3)con from t3
group by player_id,r3

③max求各参赛选手最大连胜次数，同时右连接（select distinct player_id from Matches）表（该表有所有选手信息），完善代码，如果max为null那么记为0

select t5.player_id,ifnull(max(con),0)longest_streak from t4 right join (select distinct player_id from Matches)t5
on t5.player_id=t4.player_id
group by player_id

扩展题：求各参赛选手最大不输次数

根据上题构造两个排名，求差值 后面条件也可以用sum(if(result != 'Lose',1,0))
select player_id,
                   match_day,
                   result,
                   row_number() over (partition by player_id order by match_day)                                          as r1,
                   row_number() over (partition by player_id,if(result != 'Lose', 'not_Lose', 'Lose') order by match_day) as r2
            from matches

代码：

with t1 as (select *, row_number() over (partition by player_id order by match_day) r1 from Matches)
   , t2 as (select *, (r1 - row_number() over (partition by player_id order by match_day)) r3
            from t1
            where result = 'Win')
   , t3 as (select player_id, count(r3) con
            from t2
            group by player_id, r3)
select t5.player_id, ifnull(max(con), 0) longest_streak
from t3
         right join (select distinct player_id from Matches) t5
                    on t5.player_id = t3.player_id
group by player_id;
 
#扩展题：
with t1 as (select player_id,
                   match_day,
                   result,
                   row_number() over (partition by player_id order by match_day)                                          as r1,
                   row_number() over (partition by player_id,if(result != 'Lose', 'not_Lose', 'Lose') order by match_day) as r2
            from matches)
,t2 as(
select player_id,sum(if(result != 'Lose',1,0) )as cnt
 
from t1
group by player_id, r1-r2)
select player_id,max(cnt)max_cnt
from t2
group by player_id;

总结：

如何判断连胜是关键：根据一个不加条件的row_number() 和一个加了过滤Win的row_number()做差得出的结果进行分类