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

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

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

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

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

数据准备

-- 创建分数表，并为列名增加注释
drop table t_score;

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

COMMENT ON COLUMN t_score.team_name IS '球队名称';
COMMENT ON COLUMN t_score.player_id IS '球员ID';
COMMENT ON COLUMN t_score.player_name IS '球员姓名';
COMMENT ON COLUMN t_score.score IS '得分';
COMMENT ON COLUMN t_score.score_time IS '得分时间';

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

commit;

-- 查询数据
SELECT * FROM t_score;

分析

第一步：求出 每个球队第几次进球 ，和 每个球队的某个成员第几次进球。

with t1 as(
    select
        team_name, player_id, player_name, score, score_time,
        row_number() over (partition by team_name order by score_time) 每个球队第几次进球,
        row_number() over (partition by team_name, player_name order by score_time) 每个球队的某个成员第几次进球
    from t_score
)
select
    *
from t1;

第二步：求取差值

with t1 as(
    select
        team_name, player_id, player_name, score, score_time,
        row_number() over (partition by team_name order by score_time) 每个球队第几次进球,
        row_number() over (partition by team_name, player_name order by score_time) 每个球队的某个成员第几次进球
    from t_score
)
select
    team_name,
    player_name,
    score_time,
    每个球队第几次进球 - 每个球队的某个成员第几次进球 as 差值
from t1;

在这里我们讨论这个差值意味着什么？

1 队 第1次 进球 1 队的 成员1 第一次进球 --------- 1 - 1 = 0

1 队 第2次 进球 1 队的 成员1 第二次进球 --------- 2 - 2 = 0

… …

现在应该明白 差值 意味着 在这个成员进球之前有几次其他成员进球，即为0

1 队 第1次 进球 1 队的 成员1 第一次进球 --------- 1 - 1 = 0

1 队 第2次 进球 1 队的 成员1 第二次进球 --------- 2 - 2 = 0

1 队 第3次 进球 1 队的 成员2 第一次进球 --------- 3 - 1 = 2

1 队 第4次 进球 1 队的 成员2 第二次进球 --------- 4 - 2 = 2

… …

现在应该更加清晰的描述了上面的结论，在 成员2 进球之前 有两次其他成员进球

1 队 第1次 进球 1 队的 成员1 第一次进球 --------- 1 - 1 = 0

1 队 第2次 进球 1 队的 成员1 第二次进球 --------- 2 - 2 = 0

1 队 第3次 进球 1 队的 成员2 第一次进球 --------- 3 - 1 = 2

1 队 第4次 进球 1 队的 成员2 第二次进球 --------- 4 - 2 = 2

1 队 第5次 进球 1 队的 成员1 第三次进球 --------- 5 - 3 = 2

… …

不用多说，这次 成员1 再次进球，打断了 成员2 的连续进球，即在 成员1 这次进球之前 有两次其他成员进球

现在我们明白了差值意味着什么，只要差值相等，则必然表示为同一个人进球，而非其他成员进球

第三步：求出连续进球的个数

with t1 as(
    select
        team_name, player_id, player_name, score, score_time,
        row_number() over (partition by team_name order by score_time) 每个球队第几次进球,
        row_number() over (partition by team_name, player_name order by score_time) 每个球队的某个成员第几次进球
    from t_score
), t2 as (
    select
        team_name,
        player_name,
        score_time,
        每个球队第几次进球 - 每个球队的某个成员第几次进球 as 差值
    from t1
)select t2.*, count(*) over(partition by player_name, 差值) 连续进球 from t2;

上面的代码中，我们根据球员和差值分组可以进一步求出连续进球的个数

现在我们只需过滤筛选去重即可

第四步：过滤筛选+去重 求出结果

with t1 as(
    select
        team_name, player_id, player_name, score, score_time,
        row_number() over (partition by team_name order by score_time) 每个球队第几次进球,
        row_number() over (partition by team_name, player_name order by score_time) 每个球队的某个成员第几次进球
    from t_score
), t2 as (
    select
        team_name,
        player_name,
        score_time,
        每个球队第几次进球 - 每个球队的某个成员第几次进球 as 差值
    from t1
), t3 as (
    select
        t2.*,
        count(*) over(partition by player_name, 差值) 连续进球
    from t2
)select distinct
    player_name
from t3 where 连续进球 >= 3;

总结

在这里我们使用了巧妙的方法来判断是否为同一个人进球