hive sql笔试题

最新推荐文章于 2025-03-16 16:35:25 发布

转载最新推荐文章于 2025-03-16 16:35:25 发布 · 1k 阅读

8 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/qq_24206673/article/details/108282465

文章标签：

#hive #sql

来源于某多社招。

原文：https://blog.youkuaiyun.com/qq_24206673/article/details/108282465

题目：

有一张表记录了一场篮球赛的得分情况，主要有以下字段：队名（team）、队员名（name）、队员号（num）、得分（score）、得分时间秒级（score_time）。要求用sql/hive

连续三次为本队得分的球员；
为本队比分反超的球员，以及对应的完成反超的时刻

思路整理：

拆分问题(1)，连续三次得分转换为：将得分表按照升序排列，让每一行记录的上一行，且上上行记录都是同一球员。第一步，可以用lag()over()函数。第二步，因为每个时间的得分球员是一一对应的，类似于一个键值对，这样的话，把第一步得到的结果打标。最后选出3次都是同样的球员即可。

拆分问题(2)，计算每一个时间点的两队的总分情况，以及上一个时间点的两队得分情况，比较这两个时间点的得分是否有逆转。意即，上一次A队领先，下一次B队总分领先，反之亦然。

代码：

--建表
use test;
create table basketball_game_score_detail(
team  string,
number int,
score_time  string,
score  int,
name  string
);

--插入测试数据
insert into table basketball_game_score_detail values
('A',1,'2020/8/28 9:01:14',1,'A1')
,('A',5,'2020/8/28 9:02:28',1,'A5')
,('B',4,'2020/8/28 9:03:42',3,'B4')
,('A',4,'2020/8/28 9:04:55',3,'A4')
,('B',1,'2020/8/28 9:06:09',3,'B1')
,('A',3,'2020/8/28 9:07:23',3,'A3')
,('A',4,'2020/8/28 9:08:37',3,'A4')
,('B',1,'2020/8/28 9:09:51',2,'B1')
,('B',2,'2020/8/28 9:11:05',2,'B2')
,('B',4,'2020/8/28 9:12:18',1,'B4')
,('A',1,'2020/8/28 9:13:32',2,'A1')
,('A',1,'2020/8/28 9:14:46',1,'A1')
,('A',4,'2020/8/28 9:16:00',1,'A4')
,('B',3,'2020/8/28 9:17:14',3,'B3')
,('B',2,'2020/8/28 9:18:28',3,'B2')
,('A',2,'2020/8/28 9:19:42',3,'A2')
,('A',1,'2020/8/28 9:20:55',1,'A1')
,('B',3,'2020/8/28 9:22:09',2,'B3')
,('B',3,'2020/8/28 9:23:23',3,'B3')
,('A',5,'2020/8/28 9:24:37',2,'A5')
,('B',1,'2020/8/28 9:25:51',3,'B1')
,('B',2,'2020/8/28 9:27:05',1,'B2')
,('A',3,'2020/8/28 9:28:18',1,'A3')
,('B',4,'2020/8/28 9:29:32',1,'B4')
,('A',1,'2020/8/28 9:30:46',3,'A1')
,('B',1,'2020/8/28 9:32:00',1,'B1')
,('A',4,'2020/8/28 9:33:14',2,'A4')
,('B',1,'2020/8/28 9:34:28',1,'B1')
,('B',5,'2020/8/28 9:35:42',2,'B5')
,('A',1,'2020/8/28 9:36:55',1,'A1')
,('B',1,'2020/8/28 9:38:09',3,'B1')
,('A',1,'2020/8/28 9:39:23',3,'A1')
,('B',2,'2020/8/28 9:40:37',3,'B2')
,('A',3,'2020/8/28 9:41:51',3,'A3')
,('A',1,'2020/8/28 9:43:05',2,'A1')
,('B',3,'2020/8/28 9:44:18',3,'B3')
,('A',5,'2020/8/28 9:45:32',2,'A5')
,('B',5,'2020/8/28 9:46:46',3,'B5');

--第一问
----第一步，求出每个时间点的上一个时间点和上上个时间点
select * 
       ,lag(score_time,1)over(order by score_time asc) as m1
       ,lag(score_time,2)over(order by score_time asc) as m2
       from basketball_game_score_detail;
----第二步，对m1时间点和m2时间点打标上对应的球员名
select a.*,b.name,c.name
from 
(select * 
       ,lag(score_time,1)over(order by score_time asc) as m1
       ,lag(score_time,2)over(order by score_time asc) as m2
       from basketball_game_score_detail

)a left join basketball_game_score_detail b on a.m1=b.score_time
 left join basketball_game_score_detail c on a.m2=c.score_time;

----第三步，求出这三个时间点都是同一名球员的情况
select a.*,b.name,c.name
from 
(select * 
       ,lag(score_time,1)over(order by score_time asc) as m1
       ,lag(score_time,2)over(order by score_time asc) as m2
       from basketball_game_score_detail

)a left join basketball_game_score_detail b on a.m1=b.score_time
left join basketball_game_score_detail c on a.m2=c.score_time
where a.name=b.name and b.name=c.name;

--第二问
----第一步，添加两列，分别是每个时间点的得分，如果时间点1，A队得分那么B队就是0分，反之亦然。
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 
       from basketball_game_score_detai;
----第二步，对每个时间点各球队的分数汇总
select a.team
    ,a.number
    ,a.score_time
    ,a.score
    ,a.name
    ,sum(a_score)over(partition by team order by score_time asc) as a_score_sum
    ,sum(b_score)over(partition by team order by score_time asc) as b_score_sum
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 
       from basketball_game_score_detail
)a order by a.score_time asc;

----第三步，求出每个时间点的本次总分差值和上一次总分差值
select b.*
       ,a_score_sum-b_score_sum as diff_score
       ,lag(a_score_sum-b_score_sum,1)over(order by score_time asc) as last_score_diff
from 
(select a.team
    ,a.number
    ,a.score_time
    ,a.score
    ,a.name
    ,sum(a_score)over(partition by team order by score_time asc) as a_score_sum
    ,sum(b_score)over(partition by team order by score_time asc) as b_score_sum
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 
       from basketball_game_score_detail
)a order by a.score_time asc
)b;
----第四步，两个差值的乘积小于0，即意味着有逆转情况发生
select *
from 
(select b.*
       ,a_score_sum-b_score_sum as diff_score
       ,lag(a_score_sum-b_score_sum,1)over(order by score_time asc) as last_score_diff
from 
(select a.team
    ,a.number
    ,a.score_time
    ,a.score
    ,a.name
    ,sum(a_score)over(partition by team order by score_time asc) as a_score_sum
    ,sum(b_score)over(partition by team order by score_time asc) as b_score_sum
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 
       from basketball_game_score_detail
)a order by a.score_time asc
)b
)c
where diff_score*last_score_diff<0;