hive sql笔试题
2021/11/1 19:10:04
本文主要是介绍hive sql笔试题,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
来源于某多社招。
原文:https://blog.csdn.net/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;
这篇关于hive sql笔试题的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-26结对编程到底难不难?答案在这里
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt
- 2024-06-05做软件测试需要懂代码吗?
- 2024-06-0514-ShardingSphere的分布式主键实现
- 2024-06-03为什么以及如何要进行架构设计权衡?
- 2024-05-31全网首发第二弹!软考2024年5月《软件设计师》真题+解析+答案!(11-20题)
- 2024-05-31全网首发!软考2024年5月《软件设计师》真题+解析+答案!(21-30题)