HIVE sql经典50题

最新推荐文章于 2024-05-02 18:09:54 发布
最新推荐文章于 2024-05-02 18:09:54 发布
阅读量2.6k 收藏 40
点赞数 7
分类专栏： hive 文章标签： hive sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35561281/article/details/104565826
版权

表及数据

1表 学生表
create table student(
s_id int,
s_name string,
dt string,
sex string
)
row format delimited
fields terminated by '\t';


01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-01	女
06	吴兰	1992-03-01	女
07	郑竹	1989-07-01	女
08	王菊	1990-01-20	女
2表 课程表
create table course(
c_id string,
c_name string,
t_id string
)
row format delimited
fields terminated by '\t';


01	语文	02
02	数学	01
03	英语	03
3表 教师表
create table teacher(
t_id string,
t_name string
)
row format delimited
fields terminated by '\t';


01	张三
02	李四
03	王五
4表 成绩表
create table score(
s_id string,
c_id string,
score int
)
row format delimited
fields terminated by '\t';


01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数。

思路：1、查询01数学成绩

select s_id,score from score where c_id='01'; t1

           2、查询02语文成绩

select s_id,score from score where c_id='02'; t2

           3、查询数学01比语文02成绩高的人的id、语文成绩、数学成绩

select t1.s_id t1s_id,t1.score math,t2.score chinese
from t1 join t2 on t1.s_id=t2.s_id
where t1.score>t2.score; t3

           4、查询数学01比语文02成绩高的人的id、数学成绩、语文成绩及学生的信息。

select t1s_id,math,chinese,s_name,dt,sex from t3
left join student s1 on t3.t1s_id=s1.s_id;

最终SQL

select t1s_id,math,chinese,s_name,dt,sex 
from (select t1.s_id t1s_id,t1.score math,t2.score chinese
      from (select s_id,score from score where c_id='01')t1
      join (select s_id,score from score where c_id='02')t2 on t1.s_id=t2.s_id
where t1.score>t2.score)t3
left join student s1 on t3.t1s_id=s1.s_id;

 

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

思路：同1,仅仅改变大于号小于号

最终SQL

select t1s_id,math,chinese,s_name,dt,sex 
from (select t1.s_id t1s_id,t1.score math,t2.score chinese
    from (select s_id,score from score where c_id='01')t1
    join (select s_id,score from score where c_id='02')t2 on t1.s_id=t2.s_id
where t1.score<t2.score)t3
left join student s1 on t3.t1s_id=s1.s_id;

 

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

思路：1、所有同学的编号及平均成绩

select s_id,avg(score) avg_score from score group by s_id;t1

           2、平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select t1.s_id,s1.s_name,floor(t1.avg_score) avg_score 
from t1 left join student s1 on t1.s_id=s1.s_id
where avg_score>=60;

最终SQL

select t1.s_id,s1.s_name,floor(t1.avg_score) avg_score 
from (select s_id,avg(score) avg_score from score group by s_id)t1 
left join student s1 on t1.s_id=s1.s_id
where avg_score>=60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

思路：1、查询所有同学的编号，姓名

select s_id,s_name from student;t1

           2、所有同学的选课总数,课程总成绩

select s_id,count(c_id) count_course,sum(score) count_score from score
group by s_id;t2

           3、所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select t1.s_id,t1.s_name,t2.count_course,t2.count_score from t1
left join t2 on t1.s_id=t3.s_id;

最终SQL

select t1.s_id,t1.s_name,t2.count_course,t2.count_score 
from (select s_id,s_name from student)t1
left join (select s_id,count(c_id) count_course,sum(score) count_score from score 
           group by s_id)t2
on t1.s_id=t2.s_id;

 

6、查询"李"姓老师的数量

select count(t_name) count_li_teacher from teacher where t_name like '李%';

7、查询学过"张三"老师授课的同学的信息

思路：1、从教师表中查询张三的教师编号

select t_id from teacher where t_name='张三';t1

           2、从课程表里找到张三教的课程编号

select t1.t_id t1_t_id,c1.c_id c1_c_id 
from t1 
left join course c1 on t1.t_id=c1.t_id;t2

           3、从成绩表中找到课程编号所对应的学生编号

select t2.c1_c_id t2_t1_c_id,sc.s_id sc_s_id 
from t2 
left join score sc on t2.c1_c_id=sc.c_id;t3

           4、从学生表根据学生编号找出学生信息

select t3.sc_s_id,st.s_name,st.s_dt,st.s_sex 
from t3 
left join student st on t3.sc_s_id=st.s_id;

最终SQL

select t3.sc_s_id,st.s_name,st.dt,st.sex 
from (select t2.c1_c_id t2_t1_c_id,sc.s_id sc_s_id 
      from (select t1.t_id t1_t_id,c1.c_id c1_c_id 
            from (select t_id from teacher where t_name='张三')t1 
            left join course c1 on t1.t_id=c1.t_id)t2 
      left join score sc on t2.c1_c_id=sc.c_id)t3 
left join student st on t3.sc_s_id=st.s_id;

8、查询没学过"张三"老师授课的同学的信息

思路：1、从教师表中查询非张三的所有教师编号

select t_id from teacher where t_name not like '张三';t1

           2、从课程表里找到非张三教的课程编号

select t1.t_id t1_t_id,c1.c_id c1_c_id 
from t1 left join course c1 on t1.t_id=c1.t_id;t2

           3、从成绩表中找到课程编号所对应的学生编号

select distinct(sc.s_id) sc_s_id 
from t2 left join score sc on t2.c1_c_id=sc.c_id;t3

           4、从学生表根据学生编号找出学生信息

select t3.sc_s_id,st.s_name,st.dt,st.sex 
from t3 left join student st on t3.sc_s_id=st.s_id;

最终SQL

select t3.sc_s_id,st.s_name,st.dt,st.sex
from (select distinct(sc.s_id) sc_s_id 
        from (select t1.t_id t1_t_id,c1.c_id c1_c_id 
            from (select t_id from teacher where t_name not like '张三')t1 
            left join course c1 on t1.t_id=c1.t_id)t2 
        left join score sc on t2.c1_c_id=sc.c_id)t3 
left join student st on t3.sc_s_id=st.s_id;

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

思路：1、学过编号01的

select s_id 01_s_id from score where c_id='01'; t1

           2、学过编号02的

select s_id 02_s_id from score where c_id='02'; t2

           3、学过编号为"01"并且也学过编号为"02"

select 01_s_id 01_02_s_id 
from t1 join t2 on t1.01_s_id=t2.02_s_id;t3

           4、学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select 01_02_s_id,s1.s_name,s1.dt,s1.sex
from t3
left join student s1
on t3.01_02_s_id=s1.s_id;

最终SQL

select 01_02_s_id,s1.s_name,s1.dt,s1.sex
from (select 01_s_id 01_02_s_id 
        from (select s_id 01_s_id 
              from score where c_id='01')t1 
        join (select s_id 02_s_id 
              from score where c_id='02')t2 on t1.01_s_id=t2.02_s_id)t3
left join student s1
on t3.01_02_s_id=s1.s_id;

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

思路：1、学过编号01的

select s_id from score where c_id='01'; t1

           2、学过编号02的

select s_id from score where c_id='02'; t2

           3、学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select *
from t1
left join
student s1 on t1.s_id=s1.s_id
left join t2
on s1.s_id=t2.s_id
where t2.s_id is null;

最终SQL

select *
from (select s_id 
    from score where c_id='01')t1
left join
student s1 on t1.s_id=s1.s_id
left join (select s_id 
    from score where c_id='02')t2
on s1.s_id=t2.s_id
where t2.s_id is null;

11、查询没有学全所有课程的同学的信息

思路：1、查出所有课程总数

select count(c_id) count_t1_c_id from course;t1

           2、查出所有同学学习的课程总数

select s_id,count(c_id) count_c_id from score group by s_id;t2

           3、查出没有学全所有课程的同学的编号

select t2.s_id s_id 
from t2 join t1 
where t2.count_c_id < t1.count_t1_c_id;t3

           4、查询没有学全所有课程的同学的信息

select t3.s_id,s1.s_name,s1.dt,s1.sex
from t3 left join student s1 on t3.s_id = s1.s_id;

最终SQL

select t3.s_id,s1.s_name,s1.dt,s1.sex
from (select t2.s_id s_id 
      from (select s_id,count(c_id) count_c_id 
           from score group by s_id)t2 
           join (select count(c_id) count_t1_c_id 
      from course)t1 
where t2.count_c_id < t1.count_t1_c_id)t3 
left join student s1 on t3.s_id = s1.s_id;

此sql跑了6个job，应该会有更优化的查询。

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

                     注意点：------------------in中不允许有子查询---------------------

思路：1、学号01同学所学的课程号

select c_id from score where s_id='01';t1

           2、至少有一门课与学号为"01"的同学所学相同的同学id

select s1.s_id s1_s_id 
from score s1 where s1.c_id in t1 group by s1.s_id;t2

           3、至少有一门课与学号为"01"的同学所学相同的同学的信息

select s1_s_id,st.s_name,st.dt,st.sex 
from t2 left join student st on t2.s1_s_id=st.s_id;

最终SQL

select s1_s_id,st.s_name,st.dt,st.sex 
from (select s1.s_id s1_s_id 
      from score s1 
      where s1.c_id 
      in (select c_id from score where s_id='01') group by s1.s_id)t2 
left join student st on t2.s1_s_id=st.s_id;

有个不好，连01同学也查出来了

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

思路：1、01同学的所选课程以及课程数

select c_id,count(*) over(partition by s_id) sum_01_c_id 
from score where s_id='01';t1

           2、除01同学外所有同学的学号，课程号，课程总数

select s_id,c_id,count(c_id) over(partition by s_id) sum_c_id 
from score where s_id!='01';t2

           3、与01所学课程相同的学生id

select s_id
from t1 
left join t2 on t1.c_id=t2.c_id 
where t2.sum_c_id=t1.sum_01_c_id
group by s_id;t3

           4、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select t3.s_id,st.s_name,st.dt,st.sex
from t3 
left join student st on t3.s_id=st.s_id;

最终SQL

select t3.s_id,st.s_name,st.dt,st.sex
from (select s_id
	  from (select c_id,count(*) over(partition by s_id) sum_01_c_id 
	        from score where s_id='01')t1 
	  left join (select s_id,c_id,count(c_id) over(partition by s_id) sum_c_id 
	             from score where s_id!='01')t2 on t1.c_id=t2.c_id 
	 where t2.sum_c_id=t1.sum_01_c_id
	 group by s_id)t3 
left join student st on t3.s_id=st.s_id;

16、检索"01"课程分数小于60，按分数降序排列的学生信息

           1、查看01课程分数小于60的人的学号

select s_id,score from score where c_id=01 and score<60 order by score desc; t1

           2、查看01课程分数小于60的人的信息

select t1.s_id,t1.score,st.s_name,st.dt,st.sex 
from t1 left join student st on t1.s_id=st.s_id;

最终SQL

select t1.s_id,t1.score,st.s_name,st.dt,st.sex 
from (select s_id,score from score 
        where c_id=01 and score<60 order by score desc)t1 
left join student st on t1.s_id=st.s_id;

 

20、查询学生的总成绩并进行排名

           1、查询总成绩的排名及学生编号

select s_id,sum(score) sum_score from score 
group by s_id order by sum_score desc; t1

           2、学生的总成绩并进行排名

select t1.s_id,st.s_name,t1.sum_score 
from (select s_id,sum(score) sum_score 
        from score group by s_id order by sum_score desc)t1 
join student st on t1.s_id=st.s_id;

 

柳成萌
关注
  • 7
    点赞
  • 踩
  • 40
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Hive SQL50道练习题.lnk
02-19
Hive SQL50道练习题.lnk
hiveSQL常见面试题
AI课工场
12-30 3629
小编前几天去面试了一个大数据分析的岗位，学习了几个月信心满满的小编被无情的面试教育了，被教育的不是一些高深的算法理论，而是一些hiveSQL。 这些题小编真不是不会，场景都非常熟悉，但就是容易忘。今天小编整理了一点常用场景的hiveSQL，很可能就能在面试中帮到你。 场景一：分组求TopN 先看数据： 好像把小编的数学成绩暴露了，现在需要求每一门科目，成绩排名前两名的同学。 使用H...
HiveSQL练习题--基础查询
最新发布
weixin_44589713的博客
05-02 63
【代码】HiveSQL练习题--基础查询。
Hivesql10题
落幕7的博客
05-13 620
手写HQL10题
HIVEsql-刷题40题
ABC15732186271的博客
01-10 789
hive刷题
Hive面试题SQL测试题目1数据
08-31
文章Hive面试题SQL测试题目所需数据，包含建表语句 测试数据等等...................
Hive 入门练习题
鸭梨的博客
02-25 473
1.用一条SQL语句查询出每门课都大于80分的学生姓名 name kecheng fenshu 张三 语文 81 张三 数学 75 李四 语文 76 李四 数学 90 王五 语文 81 王五 数学 100 王五 英语 90 select name from student group by name having min(fenshu) > 80; 2. 学生表 如下: 自动编号 学号 姓名 课程编
Hive经典面试实操sql题
01-03
以下是一些基于Hive SQL的经典面试题及解析，旨在帮助你理解和准备相关的面试。 1. **数据加载与查询** ```sql create table t1(team string, year int) row format delimited fields terminated by ','; load ...
2024年最新，Hive SQL经典面试题，大数据SQL经典面试题
04-28
2024年最新，Hive SQL经典面试题，大数据SQL经典面试题. ## 1：连续登录n天的用户 ## 2：留存问题 ## 3：Top N问题 ## 4：Explode问题 ## 5：行转列
Hive SQL练习题库
04-24
Hive SQL练习题库，适合Hive初学者练习。
【硬刚Hive】MYSQL/HIVESQL笔试题（六）：HIVESQL（六）
微信搜：import_bigdata，大数据领域硬核原创作者
09-11 346
欢迎关注博客主页：微信搜：import_bigdata，大数据领域硬核原创作者_王知无(import_bigdata)_CSDN博客 欢迎点赞、收藏、留言 ，欢迎留言交流！本文由【王知无】原创，首发于 CSDN博客！本文首发CSDN论坛，未经过官方和本人允许，严禁转载！ 本文是对《【硬刚大数据之学习路线篇】从零到大数据专家的学习指南(全面升级版)》的Hive部分补充。 面试题目一 场景:一个日志表中记录了某个商户费率变化状态的所有信息，现在有个需求,要取出按照时间轴顺序，发生了状态变化的数据行.
Hive面试题SQL测试题目2数据
08-31
文章Hive面试题SQL测试题目所需数据，包含建表语句 测试数据等等...................
【Hive】Hive练习题50道
HR的博客
05-07 9058
数据展示 student表 score表 teacher表 course表 在hive中建表导入数据 首先要先在hdfs上为每个数据建一个文件名相同的文件夹，以上的4张表都是txt格式的，放入hdfs相对应的文件夹后，使用以下语句建表(因为数据量不大，就直接建内部表) create table if not exists student( id int, name string, birthday string, sex string ) row format delimited fields te
Hive_Sql经典50题
cmm6666的博客
08-29 590
【代码】Hive_Sql经典50题。
50道HIVE练习题-搞定了这50题hive sql，那可不嘚直接通关起飞！！！
北慕辰的博客，欢迎各位大佬光临！！
05-18 9011
大数据自学之hive通关宝典秘籍 超详细的练习过程，赶紧收藏吧！ 》》》》》》》》持续跟新中 现有这样几张表及对应数据，请建好表并插入数据 Student(Sid,Sname,Sage,Ssex)学生表 Sid：学号 Sname：学生姓名 Sbirth：学生生日 Ssex：学生性别 Course(Cid,Cname,T#)课程表 Cid：课程编号 Cname：课程名称 Tid：教师编号 SC(Sid,Cid,score)成绩表 Sid：学号 Cid：课程编号 score：成绩 Teacher(Tid,Tn
hive基础选择题
weixin_38861839的博客
05-21 1万+
1.下列关于Hive特点总结正确的选项是（） A Hive支持自由的扩展集群的规模，只需要重启服务即可 B Hive支持自定义函数，用户可以根据自己的需求去定义函数 C Hive SQL执行时，需要避免节点出现问题 D Hive 适合处理小批量数据 【B】 A. 错误，Hive一般不需要重启服务就可以扩展集群 B. 正确，用户可以上传自己定义的函数UDF，User Define Function jar包提交至Hive环境，注册后即可使用 C. 错误，Hive具有良好的容错性，节点出现问题，SQ
Hive SQL 面试题及解析过程
yisuoyanyv的博客
01-24 457
问题 问题：查询user_low_carbon表中每日流水记录，条件为： 用户在2017年，连续三天（或以上）的天数里， 每天减少碳排放（low_carbon）都超过100g的用户低碳流水。 需要查询返回满足以上条件的user_low_carbon表中的记录流水。 例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g： seq（key） user_id data_dt low_carbon xxxxx10 u_002 2017/1/2
Hive sql语句必练50题-入门到精通(1)
热门推荐
Thomson617的博客
10-20 2万+
hive学习之经典sql 50题 hive版 建表: create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by ‘\t’; create table course(c_id string,c_name string,t_i...
很有意思的hive SQL面试题1
zZsSzss的博客
10-16 276
题目描述 数据源是有a,b两列,然后根据b列的连续相同值,加一列序号 先准备测试数据 CREATE TABLE IF NOT EXISTS `test1`( yy int, nn int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; insert into table test1 values(2010,1); insert into table test1 values(2011,1); insert into table test1
hivesql优化面试题
07-27
Hive SQL优化面试题通常涉及以下几个方面： 1. 查询优化：了解如何通过索引、分区等技术来优化Hive SQL查询性能。可以提到使用合适的索引、分区和分桶来减少数据扫描量，提高查询效率。 2. 性能调优：理解常见的性能瓶颈和优化策略，例如避免全表扫描、减少数据倾斜、合理设置并行度等。可以提到使用合适的数据类型、避免不必要的数据转换、使用合适的连接方式等来提高性能。 3. EXPLAIN关键字的使用：掌握使用EXPLAIN关键字来分析查询执行计划，了解查询的执行顺序和涉及的操作，从而找到潜在的性能问题并进行优化。 4. 解答优化相关的题目：在面试中可能会遇到一些关于查询优化和性能调优的具体问题，例如如何优化某个特定的查询语句，如何处理大数据量的查询等。在回答时可以结合自己的实际经验和知识来给出合理的解决方案。 综上所述，Hive SQL优化面试题主要涉及查询优化、性能调优、使用EXPLAIN关键字分析查询计划以及解答具体的优化问题。掌握这些知识和技巧可以帮助提升在Hive SQL领域的技能和竞争力。 #### 引用[.reference_title] - *1* *3* [Hive SQL大厂面试题必考大全](https://blog.csdn.net/m0_47256162/article/details/131687792)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [Hive SQL面试题(附答案)](https://blog.csdn.net/a934079371/article/details/122227602)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值