描述

两支篮球队进行了激烈的比赛，比分交替上升。比赛结束后，你有一个两队分数的明细表（名称为“分数表”）。

表中记录了球队、球员号码、球员姓名、得分分数及得分时间。现在球队要对比赛中表现突出的球员进行奖励。

问题：请你写一个SQL语句，统计出连续3次为球队得分的球员名单。

输入: 球员得分表						输出
TEAM_NAME	PLAYER_ID	PLAYER_NAME	SCORE	SCORE_TIME		TEAM_NAME	PLAYER_NAME
洛杉矶湖人队	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

 数据准备

CREATE TABLE t_score (
    team_name VARCHAR(50),
    player_id INT,
    player_name VARCHAR(50),
    score INT,
    score_time TIMESTAMP
);




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

分析

连续问题可以构造等差数列

①先跟据球队分组求出一个各队员按照时间顺序的一个排名

SELECT *,
                   row_number() over (partition by team_name order by score_time)           r1,
                   row_number() over (partition by team_name,player_id order by score_time) r2
            FROM t_score

②通过差值可以判断出是否连续

 with t1 as (SELECT *,
                    row_number() over (partition by team_name order by score_time)           r1,
                    row_number() over (partition by team_name,player_id order by score_time) r2
             FROM t_score)
#     , t2 as (
    select team_name, player_id, player_name, score_time, r1 - r2 r3
             from t1

③此时差值相同的个数就是连续得分次数 

通过对r3列求和去重 找出count(r3) >= 3 的数据

select team_name, player_id, player_name
from t2
group by team_name, player_id, player_name
having count(r3) >= 3

代码

 with t1 as (SELECT *,
                    row_number() over (partition by team_name order by score_time)           r1,
                    row_number() over (partition by team_name,player_id order by score_time) r2
             FROM t_score)
    , t2 as (select team_name, player_id, player_name, score_time, r1 - r2 r3
             from t1)
 select team_name, player_id, player_name
 from t2
 group by team_name, player_id, player_name
 having count(r3) >= 3
;

总结

①对数据进行去重可以使用

• distinct
• group by 分组
• 窗口函数对去重列 排序（row_number）和编号再筛选
• 子查询 in  、 exists
• 创建一个临时表或派生表，其中只包含唯一的记录，然后从这个临时表中查询数据