大数据真题讲解系列——拼多多数据分析面试题_大数据分析测试题

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

问题：

两支篮球队进行了激烈的比赛，比分交替上升。比赛结束后，你有一个两队分数的明细表（名称为“分数表”）。表中记录了球队、球员号码、球员姓名、得分分数及得分时间。现在球队要对比赛中表现突出的球员进行奖励。

1-需求：

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

(1)输入展示：

(2)结果展示：

2-数据支持：

-- 创建分数表，并为列名增加注释

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

-- 查询数据

SELECT * FROM t_score;

3-需求实现：

方法一：lead( )方法实现

1. LAG 函数：与 LEAD 函数相反，用于获取当前行前的指定行的值。
2. LEAD 函数：与 LEAD 函数相反，用于获取当前行后的指定行的值。

(1)实现原理：

        每行数据向上移动一行并作为新的一行rn1，同理向上移动两行作为新的一行rn2，当移动参数值 = rn1 = rn2时，所取的参数值便为所求。

(2)完整代码：

with t1 as (
select
       t_score.*,
       lead(player_id,1) over (partition by team_name order by score_time) rn1,
       lead(player_id,2) over (partition by team_name order by score_time) rn2
from t_score)
select distinct player_id,player_name,team_name from t1 where t1.player_id=t1.rn1 and t1.rn1=t1.rn2;

方法二：等差数列方法

        对于第一种方法适合小间距取值，当取值过大时候如100甚至1000行连续值时，这个方法显然不可以使用了，那么此时我们便可以使用等差数列发轻松解决问题

(1)本题方法原理：

1.由于本题目表中并无可以作为参考值的数字序列，那么我们便利用rownum方法生成序号；

2.拥有参考值之后我们便可以进行排序，将每个队与每个队分离。

3.分离完之后我们便可以通过ROW_NUMBER()进行等差数列的生成；

4.生成等差数列后，将等差数列与rownum生成的参考值进行求差计算，并作为新的一列：‘差值’;

5.通过求和窗口函数count（），将差值按照队伍、与队员进行分组 并按照大小进行排序

6.对总和进行过滤，得到所求结果

(2)完整代码：

select distinct
       tb3.player_id,
       tb3.player_name,
       tb3.team_name
from
    (
        select
               tb2.*,
               count(tb2.差值) over (partition by tb2.team_name,tb2.player_name order by tb2.差值) 总和
        from
        (
                select
                       p1.rn,
                       p1.player_id,
                       p1.player_name,
                       p1.team_name,
                       p1.rn-p1.等差数列 差值
                from
                (
                    with tb1 as (select rownum rn, ts.* from T_SCORE ts)
                    select
                           tb1.* ,
                           row_number() over (partition by tb1.team_name order by tb1.player_id) 等差数列
                    from tb1
                )p1
        )tb2
    )tb3
where tb3.总和>=3;