AB球队得分流水表，得到连续三次得分的队员名字 和每次赶超对手的球员名字（pdd）
在复盘时发现有类似原题，这是我在面试中遇到的最难的题
问题：两支篮球队进行了激烈的篮球比赛，比分交替上升。比赛结束后，你有一张两队得分分数的明细表，记录了球队team，球员号码number，球员姓名name, 得分分数score 以及得分时间scoretime(datetime)。现在球队要对比赛中表现突出的球员做出嘉奖，所以请你用sql统计出
1)连续三次（及以上）为球队得分的球员名单
-- 从两队的整体作为时间连续考虑：
select distinct name
from
(
select *
,row_number()over(order by score_time asc) as rank1
,row_number()over(partition by `team`,`number` order by score_time asc ) rank2
,(row_number()over(order by score_time asc) - row_number()over(partition by `team`,`number` order by score_time asc )) as diff
from basketball_game_score_detail
)t
group by diff,name
having count(1) >=3 ;
-- 从自身所在的队的作为时间连续考虑：
# 我的求解思路：
select distinct name
from
(
select *
,row_number()over(partition by team order by score_time asc) as rank1
,row_number()over(partition by team,`number` order by score_time asc ) rank2
,(row_number()over(partition by team order by score_time asc) -
row_number()over(partition by `team`,`number` order by score_time asc )) as diff
from basketball_game_score_detail
)t
group by diff,name
having count(1) >=3 ;
# 另外一位作者的求解思路也是可以的：
select distinct a.name ,a.team from
(
select *
,lead(name,1) over(partition by team order by score_time) as ld1
,lead(name,2) over(partition by team order by score_time) as ld2
,lag(name,1) over(partition by team order by score_time) as lg1
,lag(name,2) over(partition by team order by score_time) as lg2
from basketball_game_score_detail
) a
where (a.name = a.ld1 and a.name = a.ld2) -- case1：比较当前记录与下两个记录
or (a.name = a.ld1 and a.name = a.lg1) -- case2：比较当前记录与上一个记录和下一个记录
or (a.name = a.lg1 and a.name = a.lg2);-- case3：比较当前记录与上两个记录
-- where a.name =a.ld1 or a.name=a.lg1 ; 如果求相邻3个以下
2)比赛中帮助各自球队反超比分的球员姓名以及对应时间。
select team
,`number`
,score_time
,name
,A_score_acum
,B_score_acum
,score_gap
,last_score_gap
,score_gap * last_score_gap as product
from
(
select *
,(A_score_acum - B_score_acum) as score_gap
,lag(A_score_acum-B_score_acum,1)over(order by score_time) as last_score_gap
from
(
select *
,case when team='A' then score else 0 end as A_score
,case when team='B' then score else 0 end as B_score
,sum(case when team='A' then score else 0 end)over(order by score_time) as A_score_acum
,sum(case when team='B' then score else 0 end)over(order by score_time) as B_score_acum
from basketball_game_score_detail
) t1
)t2
where score_gap != 0 # 换成 (A_score_acum - B_score_acum) <> 0 亦可
and score_gap * last_score_gap <= 0; # 必须要包含0
实验数据：
CREATE TABLE basketball_game_score_detail(
team VARCHAR(40) NOT NULL ,
number VARCHAR(100) NOT NULL,
score_time datetime NOT NULL,
score int NOT NULL,
name varchar(100) NOT NULL
);
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:01:14',1,'A1');
insert into basketball_game_score_detail values('A',5,'2020/8/28 9:02:28',1,'A5');
insert into basketball_game_score_detail values('B',4,'2020/8/28 9:03:42',3,'B4');
insert into basketball_game_score_detail values('A',4,'2020/8/28 9:04:55',3,'A4');
insert into basketball_game_score_detail values('B',1,'2020/8/28 9:06:09',3,'B1');
insert into basketball_game_score_detail values('A',3,'2020/8/28 9:07:23',3,'A3');
insert into basketball_game_score_detail values('A',4,'2020/8/28 9:08:37',3,'A4');
insert into basketball_game_score_detail values('B',1,'2020/8/28 9:09:51',2,'B1');
insert into basketball_game_score_detail values('B',2,'2020/8/28 9:11:05',2,'B2');
insert into basketball_game_score_detail values('B',4,'2020/8/28 9:12:18',1,'B4');
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:13:32',2,'A1');
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:14:46',1,'A1');
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:14:59',1,'A1');
insert into basketball_game_score_detail values('A',4,'2020/8/28 9:16:00',1,'A4');
insert into basketball_game_score_detail values('B',3,'2020/8/28 9:17:14',3,'B3');
insert into basketball_game_score_detail values('B',2,'2020/8/28 9:18:28',3,'B2');
insert into basketball_game_score_detail values('A',2,'2020/8/28 9:19:42',3,'A2');
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:20:55',1,'A1');
insert into basketball_game_score_detail values('B',3,'2020/8/28 9:22:09',2,'B3');
insert into basketball_game_score_detail values('B',3,'2020/8/28 9:23:23',3,'B3');
insert into basketball_game_score_detail values('A',5,'2020/8/28 9:24:37',2,'A5');
insert into basketball_game_score_detail values('B',1,'2020/8/28 9:25:51',3,'B1');
insert into basketball_game_score_detail values('B',2,'2020/8/28 9:27:05',1,'B2');
insert into basketball_game_score_detail values('A',3,'2020/8/28 9:28:18',1,'A3');
insert into basketball_game_score_detail values('B',4,'2020/8/28 9:29:32',1,'B4');
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:30:46',3,'A1');
insert into basketball_game_score_detail values('B',1,'2020/8/28 9:32:00',1,'B1');
insert into basketball_game_score_detail values('A',4,'2020/8/28 9:33:14',2,'A4');
insert into basketball_game_score_detail values('B',1,'2020/8/28 9:34:28',1,'B1');
insert into basketball_game_score_detail values('B',5,'2020/8/28 9:35:42',2,'B5');
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:36:55',1,'A1');
insert into basketball_game_score_detail values('B',1,'2020/8/28 9:38:09',3,'B1');
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:39:23',3,'A1');
insert into basketball_game_score_detail values('B',2,'2020/8/28 9:40:37',3,'B2');
insert into basketball_game_score_detail values('A',3,'2020/8/28 9:41:51',3,'A3');
insert into basketball_game_score_detail values('A',1,'2020/8/28 9:43:05',2,'A1');
insert into basketball_game_score_detail values('B',3,'2020/8/28 9:44:18',3,'B3');
insert into basketball_game_score_detail values('A',5,'2020/8/28 9:45:32',2,'A5');
insert into basketball_game_score_detail values('B',5,'2020/8/28 9:46:46',3,'B5');
https://mp.weixin.qq.com/s/PfOFMGJeomIfEMT2Atg6Xw（挑战完毕）
扫一扫
1027
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
