来道面试题

面试题目

有一场篮球赛，参赛双方是A队和B队，场边记录员记录下了每次得分的详细信息：
team：队名
number：球衣号,
name：球员姓名,
score_time：得分时间，
score：当次得分

问（用sql表达）：
1）输出每一次的比分的反超时刻，以及对应的完成反超的球员姓名
2）输出连续三次或以上得分的球员姓名，以及那一拨连续得分的数值

答案

with detail as (
select '18:30:00' as game_time , 'Lakers' as team_name, 'kebo' as player , 2 as score
union all select '18:31:05' as game_time , 'Lakers' as team_name , 'kebo' as player , 2 as score
union all select '18:31:21' as game_time , '76ers' as team_name , 'Iversion' as player , 2 as score
union all select '18:31:31' as game_time , '76ers' as team_name , 'JR' as player , 2 as score
union all select '18:31:41' as game_time , 'Lakers' as team_name , 'Fisher' as player , 2 as score
union all select '18:31:51' as game_time , 'Lakers' as team_name , 'Fisher' as player , 2 as score
union all select '18:32:00' as game_time , '76ers' as team_name , 'Iversion' as player , 3 as score
union all select '18:32:10' as game_time , '76ers' as team_name , 'Iversion' as player , 3 as score
), team_score as (
select game_time
      ,team_name
	  ,player
	  ,score
from detail 
), d_team_score as (
select game_time
      ,team_name
	  ,player
	  ,score
	  ,sum(case when team_name = 'Lakers' then score else 0 end ) over(order by game_time) as lakers_team_score 
	  ,sum(case when team_name = '76ers' then score else 0 end ) over(order by game_time) as ssers_team_score 
from team_score
), dd_team_score as (
select game_time
      ,team_name
	  ,player
	  ,score
	  ,lakers_team_score
	  ,ssers_team_score
	  ,lakers_team_score - ssers_team_score as D_value
from d_team_score
), ddd_team_score as (
select game_time
      ,team_name
	  ,player
	  ,score
	  ,lakers_team_score
	  ,ssers_team_score
	  ,D_value
	  ,lag(D_value,1,0) over(order by game_time) as pre_D_value
from dd_team_score
)
select game_time
      ,team_name
	  ,player
	  ,score
	  ,lakers_team_score
	  ,ssers_team_score
	  ,D_value
	  ,pre_D_value
from ddd_team_score
where D_value*pre_D_value < 0 

with detail as (
select '18:30:00' as game_time , 'Lakers' as team_name, 'kebo' as player , 2 as score
union all select '18:31:05' as game_time , 'Lakers' as team_name , 'kebo' as player , 2 as score
union all select '18:31:21' as game_time , '76ers' as team_name , 'Iversion' as player , 2 as score
union all select '18:31:31' as game_time , '76ers' as team_name , 'JR' as player , 2 as score
union all select '18:31:41' as game_time , 'Lakers' as team_name , 'Fisher' as player , 2 as score
union all select '18:31:51' as game_time , 'Lakers' as team_name , 'Fisher' as player , 2 as score
union all select '18:32:00' as game_time , '76ers' as team_name , 'Iversion' as player , 3 as score
union all select '18:32:10' as game_time , '76ers' as team_name , 'Iversion' as player , 3 as score
union all select '18:33:05' as game_time , 'Lakers' as team_name , 'kebo' as player , 2 as score
union all select '18:33:06' as game_time , 'Lakers' as team_name , 'kebo' as player , 2 as score
union all select '18:33:08' as game_time , 'Lakers' as team_name , 'kebo' as player , 2 as score
union all select '18:34:10' as game_time , '76ers' as team_name , 'Iversion' as player , 1 as score
union all select '18:35:31' as game_time , '76ers' as team_name , 'JR' as player , 2 as score
), d_detail as (
select game_time
      ,team_name
	  ,player
	  ,score
	  ,ROW_NUMBER() over(partition by team_name order by game_time) - ROW_NUMBER() over(partition by player order by game_time) as D_value
from detail
) , rs as (
select game_time
      ,team_name
	  ,player
	  ,score
	  ,D_value 
	  ,sum(1) over(partition by player ,D_value ) as score_time 
	  ,sum(score) over(partition by player ,D_value ) as score_sum 
from d_detail 
)
select *
from rs 
 where score_time>=3
 order by player