当前位置：首页 > news >正文

大数据框架之Hive：第7章综合案例练习（初级）

news 文章来源：https://blog.csdn.net/yiluohan0307/article/details/129431358 2025/5/9 8:58:58

第7章综合案例练习（初级）

一环境准备

1.1 建表语句

hive>
-- 创建学生表
DROP TABLE IF EXISTS student;
create table if not exists student_info(stu_id string COMMENT '学生id',stu_name string COMMENT '学生姓名',birthday string COMMENT '出生日期',sex string COMMENT '性别'
) 
row format delimited fields terminated by ',' 
stored as textfile;-- 创建课程表
DROP TABLE IF EXISTS course;
create table if not exists course_info(course_id string COMMENT '课程id',course_name string COMMENT '课程名',tea_id string COMMENT '任课老师id'
) 
row format delimited fields terminated by ',' 
stored as textfile;-- 创建老师表
DROP TABLE IF EXISTS teacher;
create table if not exists teacher_info(tea_id string COMMENT '老师id',tea_name string COMMENT '学生姓名'
) 
row format delimited fields terminated by ',' 
stored as textfile;-- 创建分数表
DROP TABLE IF EXISTS score;
create table if not exists score_info(stu_id string COMMENT '学生id',course_id string COMMENT '课程id',score int COMMENT '成绩'
) 
row format delimited fields terminated by ',' 
stored as textfile;

1.2 数据准备

（1）创建/opt/module/data目录

[atguigu@hadoop102 module]$ mkdir data

（2）将如下4个文件放到/opt/module/data目录下

（3）数据样式说明

[atguigu@hadoop102 data]$ vim student_info.txt001,彭于晏,1995-05-16,男
002,胡歌,1994-03-20,男
003,周杰伦,1995-04-30,男
004,刘德华,1998-08-28,男
005,唐国强,1993-09-10,男
006,陈道明,1992-11-12,男
007,陈坤,1999-04-09,男
008,吴京,1994-02-06,男
009,郭德纲,1992-12-05,男
010,于谦,1998-08-23,男
011,潘长江,1995-05-27,男
012,杨紫,1996-12-21,女
013,蒋欣,1997-11-08,女
014,赵丽颖,1990-01-09,女
015,刘亦菲,1993-01-14,女
016,周冬雨,1990-06-18,女
017,范冰冰,1992-07-04,女
018,李冰冰,1993-09-24,女
019,邓紫棋,1994-08-31,女
020,宋丹丹,1991-03-01,女[atguigu@hadoop102 data]$ vim course_info.txt01,语文,1003
02,数学,1001
03,英语,1004
04,体育,1002
05,音乐,1002[atguigu@hadoop102 data]$ vim teacher_info.txt1001,张高数
1002,李体音
1003,王子文
1004,刘丽英[atguigu@hadoop102 data]$ vim score_info.txt001,01,94
002,01,74
004,01,85
005,01,64
006,01,71
007,01,48
008,01,56
009,01,75
010,01,84
011,01,61
012,01,44
013,01,47
014,01,81
015,01,90
016,01,71
017,01,58
018,01,38
019,01,46
020,01,89
001,02,63
002,02,84
004,02,93
005,02,44
006,02,90
007,02,55
008,02,34
009,02,78
010,02,68
011,02,49
012,02,74
013,02,35
014,02,39
015,02,48
016,02,89
017,02,34
018,02,58
019,02,39
020,02,59
001,03,79
002,03,87
004,03,89
005,03,99
006,03,59
007,03,70
008,03,39
009,03,60
010,03,47
011,03,70
012,03,62
013,03,93
014,03,32
015,03,84
016,03,71
017,03,55
018,03,49
019,03,93
020,03,81
001,04,54
002,04,100
004,04,59
005,04,85
007,04,63
009,04,79
010,04,34
013,04,69
014,04,40
016,04,94
017,04,34
020,04,50
005,05,85
007,05,63
009,05,79
015,05,59
018,05,87

1.3 插入数据

（1）插入数据

hive>
load data local inpath '/opt/module/data/student_info.txt' into table student_info;load data local inpath '/opt/module/data/course_info.txt' into table course_info;load data local inpath '/opt/module/data/teacher_info.txt' into table teacher_info;load data local inpath '/opt/module/data/score_info.txt' into table score_info;

（2）验证插入数据情况

hive>
select * from student_info limit 5;
select * from course_info limit 5;
select * from teacher_info limit 5;
select * from score_info limit 5;

二简单查询

2.1 查找特定条件

2.1.1 查询姓名中带“冰”的学生名单

code

hive> 
select*
from student_info
where stu_name like "%冰%";

结果

stu_id  stu_name    birthday  sex
017          范冰冰            1992-07-04      女
018          李冰冰            1993-09-24      女

2.1.2 查询姓“王”老师的个数

code

hive> 
select count(*)  wang_count
from teacher_info
where tea_name like '王%';

结果

wang_count
1

2.1.3 检索课程编号为“04”且分数小于60的学生的课程信息，结果按分数降序排列

code

hive> 
selectstu_id,course_id,score
from score_info
where course_id ='04' and score<60
order by score desc;

结果

stu_id  course_id   score
004     04        59
001     04        54
020     04        50
014     04        40
017     04        34
010     04        34

2.1.4 查询数学成绩不及格的学生和其对应的成绩，按照学号升序排序

code

hive> 
selects.stu_id,s.stu_name,t1.score
from student_info s
join (select*from score_infowhere course_id=(select course_id from course_info where course_name='数学') and score < 60) t1 on s.stu_id = t1.stu_id
order by s.stu_id;

selects.stu_id,st.stu_name,s.score
from score_info s
join student_info st
on s.stu_id=st.stu_id
join course_info c
on c.course_id=s.course_id
where c.course_name = '数学' and s.score < 60
order by stu_id;

结果

s.stu_id  s.stu_name   t1.score
005     唐国强      44
007     陈坤        55
008     吴京        34
011     潘长江      49
013     蒋欣        35
014     赵丽颖      39
015     刘亦菲      48
017     范冰冰      34
018     李冰冰      58
019     邓紫棋      39
020     宋丹丹      59

三汇总分析

3.1 汇总分析

3.1.1 查询编号为“02”的课程的总成绩

code

hive>
selectcourse_id,sum(score) score_sum
from score_info
where course_id='02'
group by course_id;

结果

course_id    score_sum  
02          1133

3.1.2 查询参加考试的学生个数

思路：对成绩表中的学号做去重并count

code

hive>
selectcount(distinct stu_id) stu_num
from score_info;

结果

stu_num                                                                             
19

3.2 分组

3.2.1 查询各科成绩最高和最低的分，以如下的形式显示：课程号，最高分，最低分

思路：按照学科分组并使用max和min。

code

hive> 
selectcourse_id,max(score) max_score,min(score) min_score
from score_info
group by course_id;

结果

course_id  max_score  min_score 
01        94          38 
02        93          34 
03        99          32 
04        100         34 
05        87          59

3.2.2 查询每门课程有多少学生参加了考试（有考试成绩）

code

hive> 
selectcourse_id,count(stu_id) stu_num
from score_info
group by course_id;

结果

course_id    stu_num
01          19
02          19
03          19
04          12
05          5

3.2.3 查询男生、女生人数

code

hive>
selectsex,count(stu_id) count
from student_info
group by sex;

结果

sex     count
女      9
男      11

3.3 分组结果的条件

3.3.1 查询平均成绩大于60分的学生的学号和平均成绩

1）思路分析

（1）平均成绩：展开来说就是计算每个学生的平均成绩

（2）这里涉及到“每个”就是要分组了

（3）平均成绩大于60分，就是对分组结果指定条件

（4）首先要分组求出每个学生的平均成绩，筛选高于60分的，并反查出这批学生，统计出这些学生总的平均成绩。

2）Hql实操

code

hive> 
selectstu_id,avg(score) score_avg
from score_info
group by stu_id
having score_avg > 60;

结果

stu_id  score_avg
001     72.5
002     86.25
004     81.5
005     75.4
006     73.33333333333333
009     74.2
013     61.0
015     70.25
016     81.25
020     69.75

3.3.2 查询至少选修四门课程的学生学号

1）思路分析

（1）需要先计算出每个学生选修的课程数据，需要按学号分组

（2）至少选修两门课程：也就是每个学生选修课程数目>=4，对分组结果指定条件

2）Hql实操

code

hive> 
selectstu_id,count(course_id) course_count
from score_info
group by stu_id
having course_count >=4;

结果

stu_id	course_num
001		4
002		4
004		4
005		5
007		5
009		5
010		4
013		4
014		4
015		4
016		4
017		4
018		4
020		4

3.3.3 [课堂讲解]查询同姓（假设每个学生姓名的第一个字为姓）的学生名单并统计同姓人数大于2的姓

思路：先提取出每个学生的姓并分组，如果分组的count>=2则为同姓

code

hive>
selectt1.first_name,count(*) count_first_name
from (selectstu_id,stu_name,substr(stu_name,0,1) first_namefrom student_info
) t1
group by t1.first_name
having count_first_name >= 2;

结果

t1.first_name   count_first_name
刘      2
周      2
陈      2

3.3.4 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

思路：按照课程号分组并求组内的平均值

code

hive>
selectcourse_id,avg(score) score_avg
from score_info
group by course_id
order by score_avg asc, course_id desc;

结果

course_id   score_avg
02         59.63157894736842
04         63.416666666666664
01         67.15789473684211
03         69.42105263157895
05         74.6

3.3.5 统计参加考试人数大于等于15的学科

按课程分组并统计组内人数，过滤条件大于等于15

code

hive>
selectcourse_id,count(stu_id) stu_count
from score_info
group by course_id
having stu_count >= 15;

结果

course_id   stu_count
01         19
02         19
03         19

3.4 查询结果排序&分组指定条件

3.4.1 查询学生的总成绩并按照总成绩降序排序

思路：分组、sum、排序

code

hive>
selectstu_id,sum(score) sum_score
from score_info
group by stu_id
order by sum_score desc;

结果

stu_id	sum_score
005	377
009	371
002	345
004	326
016	325
007	299
001	290
015	281
020	279
013	244
010	233
018	232
006	220
014	192
017	181
012	180
011	180
019	178
008	129

3.4.2 [课堂讲解]按照如下格式显示学生的语文、数学、英语三科成绩，没有成绩的输出为0，按照学生的有效平均成绩降序显示

学生id 语文数学英语有效课程数有效平均成绩

code

hive>
selectsi.stu_id,sum(if(ci.course_name='语文',score,0))  `语文`,sum(if(ci.course_name='数学',score,0))  `数学`,sum(if(ci.course_name='英语',score,0))  `英语`,count(*)  `有效课程数`,avg(si.score)  `平均成绩`
fromscore_info si
joincourse_info ci
onsi.course_id=ci.course_id
group bysi.stu_id
order by`平均成绩` desc

selectsc.stu_id `学生id`,sum(case when ci.course_name='语文' and sc.score > 0 then score else 0 end) `语文`,sum(case when ci.course_name='数学' and sc.score > 0 then score else 0 end) `数学`,sum(case when ci.course_name='英语' and sc.score > 0 then score else 0 end) `英语`,count(ci.course_id) `有效课程数`,avg(sc.score) `平均成绩`
from score_info sc
join course_info ci
on sc.course_id=ci.course_id
group by sc.stu_id
order by `平均成绩` desc;

结果

学生id  语文    数学    英语   有效课程数      平均成绩
002     74      84      87      4              86.25
004     85      93      89      4              81.5
016     71      89      71      4              81.25
005     64      44      99      5              75.4
009     75      78      60      5              74.2
006     71      90      59      3              73.33333333333333
001     94      63      79      4              72.5
015     90      48      84      4              70.25
020     89      59      81      4              69.75
013     47      35      93      4              61.0
012     44      74      62      3              60.0
011     61      49      70      3              60.0
007     48      55      70      5              59.8
019     46      39      93      3              59.333333333333336
010     84      68      47      4              58.25
018     38      58      49      4              58.0
014     81      39      32      4              48.0
017     58      34      55      4              45.25
008     56      34      39      3              43.0

3.4.3 查询一共参加三门课程且其中一门为语文课程的学生的id和姓名

code

hive>
selectt2.stu_id,s.stu_name
from (select t1.stu_idfrom (select stu_id,course_idfrom score_infowhere stu_id in (select stu_idfrom score_infowhere course_id = "01")) t1group by t1.stu_idhaving count(t1.course_id) = 3) t2
join student_info s on t2.stu_id = s.stu_id;

结果

t2.stu_id       s.stu_name
006          陈道明
008          吴京
011          潘长江
012          杨紫
019          邓紫棋

四复杂查询

4.1 子查询

4.1.1 [课堂讲解]查询所有课程成绩均小于60分的学生的学号、姓名

code

hive>
select s.stu_id,s.stu_name
from (select stu_id,sum(if(score >= 60, 1, 0)) flagfrom score_infogroup by stu_idhaving flag = 0) t1join student_info s on s.stu_id = t1.stu_id;

结果

s.stu_id  s.stu_name
008          吴京
017          范冰冰

4.1.2 查询没有学全所有课的学生的学号、姓名

解释：没有学全所有课，也就是该学生选修的课程数 < 总的课程数

code

hive>
select s.stu_id,s.stu_name
from student_info s
left join score_info sc on s.stu_id = sc.stu_id
group by s.stu_id, s.stu_name
having count(course_id) < (select count(course_id) from course_info);

结果

s.stu_id  s.stu_name
001     彭于晏
002     胡歌
003     周杰伦
004     刘德华
006     陈道明
008     吴京
010     于谦
011     潘长江
012     杨紫
013     蒋欣
014     赵丽颖
015     刘亦菲
016     周冬雨
017     范冰冰
018     李冰冰
019     邓紫棋
020     宋丹丹

4.1.3 查询出只选修了三门课程的全部学生的学号和姓名

解释：学生选修的课程数 = 3

code

hive>
selects.stu_id,s.stu_name
from student_info s
join (selectstu_id,count(course_id) course_countfrom score_infogroup by stu_idhaving course_count =3) t1
on s.stu_id = t1.stu_id;

结果

s.stu_id  s.stu_name
006     陈道明
008     吴京
011     潘长江
012     杨紫
019     邓紫棋

五多表查询

5.1 表联结

5.1.1 [课堂讲解]查询有两门以上的课程不及格的同学的学号及其平均成绩

① 先找出有两门以上不及格的学生名单，按照学生分组，过滤组内成绩低于60的并进行count，count>=2。

② 接着做出一张表查询学生的平均成绩并和上一个子查询中的学生学号进行连接

code

hive>
selectt1.stu_id,t2.avg_score
from (selectstu_id,sum(if(score < 60,1,0)) flagefrom score_infogroup by stu_idhaving flage >= 2
) t1
join (selectstu_id,avg(score) avg_scorefrom score_infogroup by stu_id
) t2 on t1.stu_id = t2.stu_id;

结果

t1.stu_id       t2.avg_score
007           59.8
008           43.0
010           58.25
013           61.0
014           48.0
015           70.25
017           45.25
018           58.0
019           59.333333333333336
020           69.75

5.1.2 查询所有学生的学号、姓名、选课数、总成绩

code

hive>
selects.stu_id,s.stu_name,count(sc.course_id) count_course,sum(sc.score) sum_score
from student_info s
left join score_info sc on s.stu_id = sc.stu_id
group by s.stu_id,s.stu_name;

结果

stu_id      stu_name    course_count    course_sum
001           彭于晏              4              290
002           胡歌                4              345
003	           周杰伦              0              0
004           刘德华              4              326
005           唐国强              5              377
006           陈道明              3              220
007           陈坤                5              299
008           吴京                3              129
009           郭德纲              5              371
010           于谦                4              233
011           潘长江              3              180
012           杨紫                3              180
013           蒋欣                4              244
014           赵丽颖              4              192
015           刘亦菲              4              281
016           周冬雨              4              325
017           范冰冰              4              181
018           李冰冰              4              232
019           邓紫棋              3              178
020           宋丹丹              4              279

5.1.3 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

code

hive>
select s.stu_id,s.stu_name,avg(sc.score) avg_score
from score_info sc
left join student_info s on s.stu_id = sc.stu_id
group by s.stu_id, s.stu_name
having avg_score > 85

结果

stu_id        stu_name      avg_score
002           胡歌            86.25

5.1.4 查询学生的选课情况：学号，姓名，课程号，课程名称

code

hive>
selects.stu_id,s.stu_name,c.course_id,c.course_name
from score_info sc
join course_info c on sc.course_id = c.course_id
join student_info s on sc.stu_id = s.stu_id;

结果

s.stu_id	s.stu_name	c.course_id	c.course_name
001	彭于晏	01	语文
002	胡歌	01	语文
004	刘德华	01	语文
005	唐国强	01	语文
006	陈道明	01	语文
007	陈坤	01	语文
008	吴京	01	语文
009	郭德纲	01	语文
010	于谦	01	语文
011	潘长江	01	语文
012	杨紫	01	语文
013	蒋欣	01	语文
014	赵丽颖	01	语文
015	刘亦菲	01	语文
016	周冬雨	01	语文
017	范冰冰	01	语文
018	李冰冰	01	语文
019	邓紫棋	01	语文
020	宋丹丹	01	语文
001	彭于晏	02	数学
002	胡歌	02	数学
004	刘德华	02	数学
005	唐国强	02	数学
006	陈道明	02	数学
007	陈坤	02	数学
008	吴京	02	数学
009	郭德纲	02	数学
010	于谦	02	数学
011	潘长江	02	数学
012	杨紫	02	数学
013	蒋欣	02	数学
014	赵丽颖	02	数学
015	刘亦菲	02	数学
016	周冬雨	02	数学
017	范冰冰	02	数学
018	李冰冰	02	数学
019	邓紫棋	02	数学
020	宋丹丹	02	数学
001	彭于晏	03	英语
002	胡歌	03	英语
004	刘德华	03	英语
005	唐国强	03	英语
006	陈道明	03	英语
007	陈坤	03	英语
008	吴京	03	英语
009	郭德纲	03	英语
010	于谦	03	英语
011	潘长江	03	英语
012	杨紫	03	英语
013	蒋欣	03	英语
014	赵丽颖	03	英语
015	刘亦菲	03	英语
016	周冬雨	03	英语
017	范冰冰	03	英语
018	李冰冰	03	英语
019	邓紫棋	03	英语
020	宋丹丹	03	英语
001	彭于晏	04	体育
002	胡歌	04	体育
004	刘德华	04	体育
005	唐国强	04	体育
007	陈坤	04	体育
009	郭德纲	04	体育
010	于谦	04	体育
013	蒋欣	04	体育
014	赵丽颖	04	体育
016	周冬雨	04	体育
017	范冰冰	04	体育
020	宋丹丹	04	体育
005	唐国强	05	音乐
007	陈坤	05	音乐
009	郭德纲	05	音乐
015	刘亦菲	05	音乐
018	李冰冰	05	音乐
Time taken: 20.878 seconds, Fetched: 74 row(s)

5.1.5 查询出每门课程的及格人数和不及格人数

code

hive> 
selectc.course_id,c.course_name,t1.`及格人数`,t1.`不及格人数`
from course_info c
join (selectcourse_id,sum(if(score >= 60,1,0)) as `及格人数`,sum(if(score < 60,1,0)) as `不及格人数`from score_infogroup by course_id) t1 on c.course_id = t1.course_id;

结果

c.course_id     c.course_name   t1.及格人数     t1.不及格人数
01            语文            12            7
02            数学            8             11
03            英语            13            6
04            体育            6             6
05            音乐            4             1
Time taken: 10.746 seconds, Fetched: 5 row(s)

5.1.6 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息

code

hive>
selects.stu_id,s.stu_name,t1.score,t1.course_id,c.course_name
from student_info s
join (selectstu_id,score,course_idfrom score_infowhere score > 80 and course_id = '03') t1
on s.stu_id = t1.stu_id
join course_info c on c.course_id = t1.course_id;

结果

s.stu_id        s.stu_name      t1.score       t1.course_id    c.course_name
002           胡歌            87             03             英语
004           刘德华          89             03             英语
005           唐国强          99             03             英语
013           蒋欣            93             03             英语
015           刘亦菲          84             03             英语
019           邓紫棋          93             03             英语
020           宋丹丹          81             03             英语
Time taken: 9.064 seconds, Fetched: 7 row(s)

5.2 多表连接

5.2.1 课程编号为"01"且课程分数小于60，按分数降序排列的学生信息

code

hive>
selects.stu_id,s.stu_name,s.birthday,s.sex,t1.score
from student_info s
join (selectstu_id,course_id,scorefrom score_infowhere score < 60 and course_id = '01') t1
on s.stu_id=t1.stu_id
order by t1.score desc;

结果

s.stu_id        s.stu_name      s.birthday      s.sex   t1.score
017           范冰冰        1992-07-04        女      58
008           吴京          1994-02-06        男      56
007           陈坤          1999-04-09        男      48
013           蒋欣          1997-11-08        女      47
019           邓紫棋        1994-08-31        女      46
012           杨紫          1996-12-21        女      44
018           李冰冰        1993-09-24        女      38
Time taken: 8.936 seconds, Fetched: 7 row(s)

5.2.2 查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列

code

hive>
selects.stu_id,s.stu_name,c.course_name,s2.score
from student_info s
join (selectstu_id,sum(if(score >= 70,0,1)) flagefrom score_infogroup by stu_idhaving flage =0) t1
on s.stu_id = t1.stu_id
left join score_info s2 on s.stu_id = s2.stu_id
left join course_info c on s2.course_id = c.course_id;

结果

s.stu_id      s.stu_name    c.course_name  s2.course
002     胡歌    语文    74
002     胡歌    数学    84
002     胡歌    英语    87
002     胡歌    体育    100
016     周冬雨  语文    71
016     周冬雨  数学    89
016     周冬雨  英语    71
016     周冬雨  体育    94
Time taken: 27.166 seconds, Fetched: 8 row(s)

5.2.3 查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩

code

hive>
selectsc1.stu_id,sc1.course_id,sc1.score
from score_info sc1 
join score_info sc2 on sc1.stu_id = sc2.stu_id
and sc1.course_id <> sc2.course_id
and sc1.score = sc2.score;

结果

sc1.stu_id   sc1.course_id    sc1.score
016       03            71
017       04            34
016       01            71
005       05            85
007       05            63
009       05            79
017       02            34
005       04            85
007       04            63
009       04            79
Time taken: 8.881 seconds, Fetched: 10 row(s)

5.2.4 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

知识点：多表连接 + 条件

code

hive>
selects1.stu_id
from
(selectsc1.stu_id,sc1.course_id,sc1.scorefrom  score_info sc1where sc1.course_id ='01'
) s1
join
(selectsc2.stu_id,sc2.course_id,scorefrom score_info sc2where sc2.course_id ="02"
)s2
on s1.stu_id=s2.stu_id
where s1.score > s2.score;

结果

5.2.5 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

code

hive>
selectt1.stu_id as `学号`,s.stu_name as `姓名`
from
(selectstu_idfrom score_info sc1where sc1.course_id='01'and stu_id in (selectstu_idfrom score_info sc2where sc2.course_id='02')
)t1
join student_info s
on t1.stu_id = s.stu_id;

结果

学号    姓名
001     彭于晏
002     胡歌
004     刘德华
005     唐国强
006     陈道明
007     陈坤
008     吴京
009     郭德纲
010     于谦
011     潘长江
012     杨紫
013     蒋欣
014     赵丽颖
015     刘亦菲
016     周冬雨
017     范冰冰
018     李冰冰
019     邓紫棋
020     宋丹丹
Time taken: 10.161 seconds, Fetched: 19 row(s)

5.2.6 [课堂讲解]查询学过“李体音”老师所教的所有课的同学的学号、姓名

code

hive>
selectt1.stu_id,si.stu_name
from
(selectstu_idfrom score_info siwhere course_id in(selectcourse_idfrom course_info cjoin teacher_info ton c.tea_id = t.tea_idwhere tea_name='李体音'      --李体音教的所有课程)group by stu_idhaving count(*)=2       --学习所有课程的学生
)t1
left join student_info si
on t1.stu_id=si.stu_id;

结果

s.stu_id    s.stu_name
005       唐国强
007       陈坤
009       郭德纲
Time taken: 27.16 seconds, Fetched: 3 row(s)

5.2.7 [课堂讲解]查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名

code

hive>
selectt1.stu_id,si.stu_name
from
(selectstu_idfrom score_info siwhere course_id in(selectcourse_idfrom course_info cjoin teacher_info ton c.tea_id = t.tea_idwhere tea_name='李体音')group by stu_id
)t1
left join student_info si
on t1.stu_id=si.stu_id;

结果

s.stu_id    s.stu_name
001       彭于晏
002       胡歌
004       刘德华
005       唐国强
007       陈坤
009       郭德纲
010       于谦
013       蒋欣
014       赵丽颖
015       刘亦菲
016       周冬雨
017       范冰冰
018       李冰冰
020       宋丹丹
Time taken: 9.391 seconds, Fetched: 14 row(s)

5.2.8 [课堂讲解]查询没学过"李体音"老师讲授的任一门课程的学生姓名

code

hive>
selectstu_id,stu_name
from student_info
where stu_id not in
(selectstu_idfrom score_info siwhere course_id in(selectcourse_idfrom course_info cjoin teacher_info ton c.tea_id = t.tea_idwhere tea_name='李体音')group by stu_id
);

结果

stu_id  stu_name
003     周杰伦
006     陈道明
008     吴京
011     潘长江
012     杨紫
019     邓紫棋
Time taken: 36.559 seconds, Fetched: 6 row(s)

5.2.9 [课堂讲解]查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名

code

hive>
selectsi.stu_id,si.stu_name
from score_info sc
join student_info si
on sc.stu_id = si.stu_id
where sc.course_id in
(selectcourse_idfrom score_infowhere stu_id='001'    --001的课程
) and sc.stu_id <> '001'  --排除001学生
group by si.stu_id,si.stu_name;

结果

s1.stu_id     s2.stu_name
002          胡歌
004          刘德华
005          唐国强
006          陈道明
007          陈坤
008          吴京
009          郭德纲
010          于谦
011          潘长江
012          杨紫
013          蒋欣
014          赵丽颖
015          刘亦菲
016          周冬雨
017          范冰冰
018          李冰冰
019          邓紫棋
020          宋丹丹
Time taken: 8.97 seconds, Fetched: 18 row(s)

5.2.10 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

code

hive>
selectsi.stu_name,ci.course_name,sc.score,t1.avg_score
from score_info sc
join student_info si
on sc.stu_id=si.stu_id
join course_info ci
on sc.course_id=ci.course_id
join
(selectstu_id,avg(score) avg_scorefrom score_infogroup by stu_id
)t1
on sc.stu_id=t1.stu_id
order by t1.avg_score desc;

结果

t2.stu_name  t2.course_name  t2.score       t1.avg_score
胡歌    体育    100     86.25
胡歌    数学    84      86.25
胡歌    英语    87      86.25
胡歌    语文    74      86.25
刘德华  体育    59      81.5
刘德华  语文    85      81.5
刘德华  英语    89      81.5
刘德华  数学    93      81.5
周冬雨  英语    71      81.25
周冬雨  数学    89      81.25
周冬雨  体育    94      81.25
周冬雨  语文    71      81.25
唐国强  数学    44      75.4
唐国强  音乐    85      75.4
唐国强  语文    64      75.4
唐国强  体育    85      75.4
唐国强  英语    99      75.4
郭德纲  音乐    79      74.2
郭德纲  体育    79      74.2
郭德纲  英语    60      74.2
郭德纲  语文    75      74.2
郭德纲  数学    78      74.2
陈道明  语文    71      73.33333333333333
陈道明  数学    90      73.33333333333333
陈道明  英语    59      73.33333333333333
……
李冰冰  音乐    87      58.0
李冰冰  语文    38      58.0
李冰冰  英语    49      58.0
李冰冰  数学    58      58.0
赵丽颖  数学    39      48.0
赵丽颖  语文    81      48.0
赵丽颖  体育    40      48.0
赵丽颖  英语    32      48.0
范冰冰  英语    55      45.25
范冰冰  体育    34      45.25
范冰冰  数学    34      45.25
范冰冰  语文    58      45.25
吴京    语文    56      43.0
吴京    数学    34      43.0
吴京    英语    39      43.0
Time taken: 20.137 seconds, Fetched: 74 row(s)

大数据框架之Hive：第7章综合案例练习（初级）

第7章综合案例练习（初级） 一环境准备 1.1 建表语句 hive> -- 创建学生表 DROP TABLE IF EXISTS student; create table if not exists student_info(stu_id string COMMENT 学生id,stu_name string COMMENT 学生姓名,birthday string COMMENT 出…...

编程日记 2023/3/9 23:01:45

kafka：linux 安装 kafka集群

kafka运行依赖于 jdk、zookeeper，kafka可视化工具选择kafka-eagle。所以要装的组件有：jdk、zookeeper、kafka、kafka-eagle一、安装jdk下载linux版本的jdk包，比如：jdk-8u192-linux-x64.tar.gz。将其复制到 /opt 目录下并解压&…...

编程日记 2023/4/11 22:49:55

springboot实现Hessian协议的RPC服务

背景这段时间在公司接手了一个新项目，看到一段代码非常奇怪的，研究了好久。内容大概是这样 public void getUser (int id) {userService.getById(id); }当我点击这个方法进去的时候，我发现这个UserService类居然是导入jar包的一个接口&…...

编程日记 2023/4/11 22:49:53

2-6 SpringCloud快速开发入门: Eureka 服务注册中心发现与消费服务

接上一章节向Eureka 服务注册中心注册服务，这里讲讲Eureka 服务注册中心发现与消费服务 Eureka 服务注册中心发现与消费服务我们已经搭建一个服务注册中心，同时也向这个服务注册中心注册了服务，接下来我们就可以发现和消费服务了&#xff0…...

编程日记 2023/4/11 22:49:50

Java-Web之s2-001与CommonsCollections

本文源自我个人入坑Java-Web安全的一点小经验，献给那些看得懂java代码但不知道从哪里入手代审的师傅们：） Struts2之s2-001 环境配置说说环境配置的问题，大多数人对漏洞复现的恐惧感还是来自于环境的配置，也许配了大…...

编程日记 2023/3/9 22:57:35

【JavaSE】数组的定义和使用（下）

数组的定义和使用（下）4. 数组练习4.1 模拟实现toString4.2 数组拷贝4.3 比较两个数组是否相同4.4 填充数组4.3 求数组中元素的平均值4.4 查找数组中指定元素（顺序查找）4.5 查找数组中指定元素（二分查找）4.6…...

编程日记 2023/3/9 22:56:33

Oracle 实现对全局错误语句的审计监控 ORA- alert

--将所有数据库ora-错误写入表create table error_tab (username varchar2(4000), d_current_nr_error varchar2(4000), ora_server_error_msg varchar2(4000), full_text varchar2(4000),errdate date);create or replace trigger error_triggerafter servererror on database…...

编程日记 2023/3/9 22:55:30

React解决样式冲突问题的方法

React解决样式冲突问题的方法前言： 1、React最终编译打包后都在一个html页面中，如果在两个组件中取一样类名分别引用在自身，那么后者会覆盖前者。 2、默认情况下，只要导入了组件，不管组件有没有显示在页面中&#x…...

编程日记 2023/4/12 12:01:15

Go项目（用户操作微服务）

简介用户留言、收藏、修改收货地址等，统一放在用户操作微服务这里按照业务类型划分微服务表设计，三张表// 用户收藏 type UserFav struct {BaseModel// 联合索引 idx_user_goods，且唯一User int32 gorm:"type:int;index:idx_user_goo…...

编程日记 2023/4/12 12:01:13

Spring Boot统一功能处理

目录一、统一用户登录权限验证 1.1 自定义拦截器 1.2 将自定义拦截器加入到系统配置 1.3 统一访问前缀二、统一异常处理三、统一数据格式返回一、统一用户登录权限验证 1.1 自定义拦截器拦截器是一个普通的类，需要实现HandlerInterceptor接口并重写pre…...

编程日记 2023/4/12 12:01:11

ETCD多次出现CONTEXT DEADLINE EXCEEDED

roothqa-master-01:~# etcdctl --endpoints$ETCD_ENDPOINTS member list --write-outtable {“level”:“warn”,“ts”:“2020-03-23T14:19:45.0330800”,“caller”:“clientv3/retry_interceptor.go:61”,“msg”:“retrying of unary invoker failed”,“target”:“endpoi…...

编程日记 2023/4/12 12:01:09

git 提交多人开发避免冲突

代码正常提交 git add . git commit -m ‘备注信息’ git status 查看本地提交状态 git pull 拉取代码 git push origin master 指定远程仓库名和分支名 ‘’ 如果多人开发 A和B 提交避免冲突 B拉取代码修改内容直接提交后 A也修改了内容在git add / git commit / git pull / g…...

编程日记 2023/4/12 12:01:07

求职复盘：干了四年外包出来，面试5次全挂

我的情况大概介绍一下个人情况，男，毕业于普通二本院校非计算机专业，18年跨专业入行测试，第一份工作在湖南某软件公司，做了接近4年的外包测试工程师，今年年初，感觉自己不能够再这样下去了&…...

编程日记 2023/3/9 22:49:15

AXI总线核心解读---基于官方文档

AXI总线何处使用AXI ZYNQ异构芯片，内部总线使用的AXI总线纯FPGA的IP接口也要用高速接口，DDR（AXI、传统）等模块都有涉及到什么是AXI总线 AXI的三种形式: AXI-FULL：高性能的存储器映射需求—可以256个以内发送存储器…...

编程日记 2023/4/12 12:01:05

【Linux修炼】15.进程间通信

每一个不曾起舞的日子，都是对生命的辜负。进程间通信进程间通信一.理解进程间通信1.1 什么是通信1.2 为什么要有通信1.3 如何进行进程间通信二.管道2.1 匿名管道2.2 匿名管道编码部分2.3 管道的特点2.4 如何理解命令行中的管道2.5 进程控制多个子进程三.命名管道3.…...

编程日记 2023/4/13 5:38:26

每天一道大厂SQL题【Day15】微众银行真题实战(五)

每天一道大厂SQL题【Day15】微众银行真题实战(五) 大家好，我是Maynor。相信大家和我一样，都有一个大厂梦，作为一名资深大数据选手，深知SQL重要性，接下来我准备用100天时间，基于大数据岗面试中的经典SQL题&…...

编程日记 2023/4/13 5:38:19

如何优化查询大数据量的表

给你100万条数据的一张表，你将如何查询优化？1.两种查询引擎查询速度（myIsam 引擎 ）InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多…...

编程日记 2023/4/13 5:38:15

卷麻了，00后Jmeter用的比我还熟练，简直没脸见人......

经常看到无论是刚入职场的新人，还是工作了一段时间的老人，都会对测试工具的使用感到困扰？前言性能测试是一个全栈工程师/架构师必会的技能之一，只有学会性能测试，才能根据得到的测试报告进行分析，找到系统性…...

编程日记 2023/4/13 5:38:08

力扣-树节点

大家好，我是空空star，本篇带大家了解一道中等的力扣sql练习题。文章目录前言一、题目：608. 树节点二、解题1.正确示范①提交SQL运行结果2.正确示范②提交SQL运行结果3.正确示范③提交SQL运行结果4.正确示范④提交SQL运行结果5.其他总结前言 …...

编程日记 2023/4/12 3:04:50

MySQL8启动错误“Neither found #innodb_redo subdirectory, nor ib_logfile* files”

今天做MySQL备份文件回复测试，用来检验MySQL备份文件可用性。 MySQL版本8.0.32 备份文件为腾讯云MySQL实例，版本8.0 使用xtrabackup恢复备份。执行过程顺利，启动MySQL时发生错误。提示如下：注意，这里使用了systemctl stop mysql。虽然启动失败了，但是如果不执行这条…...

编程日记 2023/4/13 5:38:02

JVM系列——详细说明Volatile，原子性/可见性，先行发生原则

上篇我们讨论了JMM中的工作内存和主内存、内存直接的交互指令，以及指令之间的顺序规则。本篇将会以上篇为基础，详细介绍并发编程中的三个重要概念/工具：Volatile、原子性/可见性和先行发生（happens-before)原则。 volatile型变量…...

编程日记 2023/4/12 3:04:42

ArcGIS：栅格计算器的运算符和函数详解

01 栅格计算器在哪？02 运算符说明栅格计算器的表达式书写与Python语法一致（由于其为解释型语言并且语言简洁优美，因此简单上手），这里主要简单说明各个运算符即可使用栅格计算器构建地图代数表达式以输出要求的栅格图像…...

编程日记 2023/4/13 5:37:56

spring的beanfactory与applicationContext的区别以及继承关系

applicationContext继承关系首先可以看一张图 ListableBeanFactory 可列举的bean工厂 hierarchical 分层bean工厂 messageSource 国际化信息 //国际化（internationalization）是设计和…...

编程日记 2023/4/13 5:37:50

分享一个 hive on spark 模式下使用 HikariCP 数据库连接池造成的资源泄露问题

最近在针对某系统进行性能优化时，发现了一个hive on spark 模式下使用 HikariCP 数据库连接池造成的资源泄露问题，该问题具有普适性，故特地拿出来跟大家分享下。 1 问题描述在微服务中，我们普遍会使用各种数据库连接池技术以加快…...

编程日记 2023/4/11 22:49:48

MySQL 行锁

行锁 : 对表中行记录的锁 MySQL 的行锁 : 由各个引擎自己实现MyISAM 不支持行锁InnoDB 支持行锁两阶段锁协议 : 行锁是在需要时才加上，要等到事务结束才释放例子 : id 是表 t 的主键的 B 的 update 会阻塞，直到 A 执行 commit 后，B 才能…...

编程日记 2023/4/11 22:49:46

成为AI架构师的三大能力

AI架构师的定义 “AI 架构师”是以深度学习为代表的第三次AI热潮所催生的新型复合型人才，它的产生最本质的驱动因素是AI产业化落地应用的蓬勃发展对人才的需求，深度学习突出的工程属性也特别需要复合型人才来驾驭。从字面来看，AI架构师的“…...

编程日记 2023/4/15 12:26:25

博客系统实现（前端 + 后端）代码

文章目录博客系统前端代码 ：1. add.html2. blog_detail.html3. blog_edit.html4. blog_list.html5. login.htmlcss 文件1. blog_detail.css2. blog_edit.css3. blog_list.css4. common.css5. login.css6. 分页器使用的 css后端代码1.config 包1.1 AppConfig类1.2 Lo…...

编程日记 2023/4/15 12:26:16

C语言:如何在cmd命令窗口上玩贪吃蛇游戏

最近在重新学习C语言知识，还别说，在这个过程中，我还真的学到了过去很多没有学习到C语言知识，于是就做了这个游戏–贪吃蛇. 运行结果如下： C语言：如何在cmd命令窗口上玩贪吃蛇游戏文章目录 1. 实现原理2. 实现过程3. 参考代码1. 实现原理其实，就是利用了人的视觉错觉来…...

编程日记 2023/4/11 16:05:01

Flutter-自定义图标

虽然Flutter有许多内置的icon图标，但是有些特殊功能的话，需要自定义图标或者需要在iconfont 阿里巴巴的图标库里找对应合适的图标。第一步：在iconfont 阿里巴巴里搜索想要的图标并加入到购物车，点击下载代码后，会生成…...

编程日记 2023/4/11 14:03:45

教学场景应用视频试看预览功能

html5播放器视频预览功能效果 - 视频预览代码示例预播放一小段时间的视频内容，比如3分钟，然后引导用户付费观看或注册会员观看完整视频。原理：视频播放结束，执行s2j_onPlayOver()函数，显示提示信息或对话框&#xff0c…...

编程日记 2023/4/15 12:26:05

第7章 综合案例练习（初级）

一 环境准备

1.1 建表语句

1.2 数据准备

1.3 插入数据

二 简单查询

2.1 查找特定条件

2.1.1 查询姓名中带“冰”的学生名单

2.1.2 查询姓“王”老师的个数

2.1.3 检索课程编号为“04”且分数小于60的学生的课程信息，结果按分数降序排列

2.1.4 查询数学成绩不及格的学生和其对应的成绩，按照学号升序排序

三 汇总分析

3.1 汇总分析

3.1.1 查询编号为“02”的课程的总成绩

3.1.2 查询参加考试的学生个数

3.2 分组

3.2.1 查询各科成绩最高和最低的分，以如下的形式显示：课程号，最高分，最低分

3.2.2 查询每门课程有多少学生参加了考试（有考试成绩）

3.2.3 查询男生、女生人数

3.3 分组结果的条件

3.3.1 查询平均成绩大于60分的学生的学号和平均成绩

3.3.2 查询至少选修四门课程的学生学号

3.3.3 [课堂讲解]查询同姓（假设每个学生姓名的第一个字为姓）的学生名单并统计同姓人数大于2的姓

3.3.4 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

3.3.5 统计参加考试人数大于等于15的学科

3.4 查询结果排序&分组指定条件

3.4.1 查询学生的总成绩并按照总成绩降序排序

3.4.2 [课堂讲解]按照如下格式显示学生的语文、数学、英语三科成绩，没有成绩的输出为0，按照学生的有效平均成绩降序显示

3.4.3 查询一共参加三门课程且其中一门为语文课程的学生的id和姓名

四 复杂查询

4.1 子查询

4.1.1 [课堂讲解]查询所有课程成绩均小于60分的学生的学号、姓名

4.1.2 查询没有学全所有课的学生的学号、姓名

4.1.3 查询出只选修了三门课程的全部学生的学号和姓名

五 多表查询

5.1 表联结

5.1.1 [课堂讲解]查询有两门以上的课程不及格的同学的学号及其平均成绩

5.1.2 查询所有学生的学号、姓名、选课数、总成绩

5.1.3 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

5.1.4 查询学生的选课情况：学号，姓名，课程号，课程名称

5.1.5 查询出每门课程的及格人数和不及格人数

5.1.6 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息

5.2 多表连接

5.2.1 课程编号为"01"且课程分数小于60，按分数降序排列的学生信息

5.2.2 查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列

5.2.3 查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩

5.2.4 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

5.2.5 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

5.2.6 [课堂讲解]查询学过“李体音”老师所教的所有课的同学的学号、姓名

5.2.7 [课堂讲解]查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名

5.2.8 [课堂讲解]查询没学过"李体音"老师讲授的任一门课程的学生姓名

5.2.9 [课堂讲解]查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名

5.2.10 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

相关文章：

第7章综合案例练习（初级）

一环境准备

二简单查询

三汇总分析

四复杂查询

五多表查询