拼多多SQL面试题：连续3次为球队得分的球员名单

连续3次为球队得分的球员名单

1.数据准备

create table tb_score(
    team_name VARCHAR(50) COMMENT '球队名称',
    player_id INT COMMENT '球员ID',
    player_name VARCHAR(50) COMMENT '球员姓名',
    score INT COMMENT '得分',
    score_time DATETIME COMMENT '得分时间'

);

INSERT INTO tb_score (team_name, player_id, player_name, score, score_time) VALUES
('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 3, '2023-12-25 10:00:00'),
('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 3, '2023-12-25 10:15:00'),
('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 1, '2023-12-25 10:30:00'),
('洛杉矶湖人队', 3, '安东尼·戴维斯', 2, '2023-12-25 10:32:00'),
('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 3, '2023-12-25 10:45:00'),
('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 3, '2023-12-25 11:00:00'),
('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 2, '2023-12-25 11:15:00'),
('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 2, '2023-12-25 11:30:00'),
('金州勇士队', 30, '斯蒂芬·库里', 1, '2023-12-25 10:10:00'),
('金州勇士队', 30, '斯蒂芬·库里', 1, '2023-12-25 10:25:00'),
('金州勇士队', 30, '斯蒂芬·库里', 1, '2023-12-25 10:40:00'),
('金州勇士队', 11, '克莱·汤普森', 2, '2023-12-25 10:45:00'),
('金州勇士队', 30, '斯蒂芬·库里', 2, '2023-12-25 10:55:00'),
('金州勇士队', 30, '斯蒂芬·库里', 2, '2023-12-25 11:10:00'),
('金州勇士队', 30, '斯蒂芬·库里', 3, '2023-12-25 11:25:00'),
('金州勇士队', 30, '斯蒂芬·库里', 3, '2023-12-25 11:40:00'),
('金州勇士队', 30, '斯蒂芬·库里', 3, '2023-12-25 11:55:00');

select * from tb_score;

2.解题思路

• 开窗添加两列，第一列是大维度范围排名，第二列是小维度排名
• 观察开窗的列，变化的那一行，即是不连续的
• 根据需求，进行聚合计

3.解题代码

T1：开窗添加两列，第一列是大维度排名（队伍内的排名），第二列是小维度排名（队伍+个人的排名）。具体表现是窗的partition by 后面跟着的维度字段会增加

 select
    team_name,
    player_name,
    score_time,
    row_number() over (partition by team_name order by score_time) as rm1_team,
    row_number() over (partition by team_name,player_name  order by  score_time) as  rm2_team_player
from tb_score

结果如下：
可以观察到在大维度下，队伍内的排名是1-8，而个人的排名不是1-8，而是队伍的第4名的排名是安东尼·戴维森，这里是因为第4次进球是安东尼·戴维森，打断了詹姆斯的连续进球。

T2：但是这样的排名对于我们来说不好观察连续是否变化，也不好分出连续组，所以我们进行下一步：将两个列表排名相减

  -- 这里cast(是类型转换，因为有些版本开窗出来的数据类型没法相减)
  select team_name,player_name,cast( rm1_team as signed ) - cast(rm2_team_player as signed ) as  group_id  from t1

结果：相减得到的不是连续次数哦，是连续组的组号，可以看到詹姆斯连续进球有两组

T3：根据需求聚合，这里要求连续3次为球队得分的球员名单，那就是连续的条数>3

-- 每组的条数
  select team_name,player_name,group_id,count(1) as cnt from t2 group by team_name,player_name,group_id

-- 筛选出大于3的，可以按照需求选择去重
select  team_name,player_name,max(t3.cnt) from t3  group by team_name,player_name having max(t3.cnt) > 3

结果：

总结：没啥总结的，出错了再说吧