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

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

一环境准备

1.1 建表语句

hive>-- 创建学生表DROPTABLEIFEXISTS student;createtableifnotexists student_info(
    stu_id string COMMENT'学生id',
    stu_name string COMMENT'学生姓名',
    birthday string COMMENT'出生日期',
    sex string COMMENT'性别')row format delimited fieldsterminatedby',' 
stored as textfile;-- 创建课程表DROPTABLEIFEXISTS course;createtableifnotexists course_info(
    course_id string COMMENT'课程id',
    course_name string COMMENT'课程名',
    tea_id string COMMENT'任课老师id')row format delimited fieldsterminatedby',' 
stored as textfile;-- 创建老师表DROPTABLEIFEXISTS teacher;createtableifnotexists teacher_info(
    tea_id string COMMENT'老师id',
    tea_name string COMMENT'学生姓名')row format delimited fieldsterminatedby',' 
stored as textfile;-- 创建分数表DROPTABLEIFEXISTS score;createtableifnotexists score_info(
    stu_id string COMMENT'学生id',
    course_id string COMMENT'课程id',
    score intCOMMENT'成绩')row format delimited fieldsterminatedby',' 
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.txt

001,彭于晏,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.txt

01,语文,100302,数学,100103,英语,100404,体育,100205,音乐,1002[atguigu@hadoop102 data]$ vim teacher_info.txt

1001,张高数
1002,李体音
1003,王子文
1004,刘丽英

[atguigu@hadoop102 data]$ vim score_info.txt

001,01,94002,01,74004,01,85005,01,64006,01,71007,01,48008,01,56009,01,75010,01,84011,01,61012,01,44013,01,47014,01,81015,01,90016,01,71017,01,58018,01,38019,01,46020,01,89001,02,63002,02,84004,02,93005,02,44006,02,90007,02,55008,02,34009,02,78010,02,68011,02,49012,02,74013,02,35014,02,39015,02,48016,02,89017,02,34018,02,58019,02,39020,02,59001,03,79002,03,87004,03,89005,03,99006,03,59007,03,70008,03,39009,03,60010,03,47011,03,70012,03,62013,03,93014,03,32015,03,84016,03,71017,03,55018,03,49019,03,93020,03,81001,04,54002,04,100004,04,59005,04,85007,04,63009,04,79010,04,34013,04,69014,04,40016,04,94017,04,34020,04,50005,05,85007,05,63009,05,79015,05,59018,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 查询姓名中带“冰”的学生名单

codehive> select *from student_infowhere stu_name like "%冰%";

结果

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

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

codehive> select count(*) wang_countfrom teacher_infowhere tea_name like '王%';

结果

wang_count
1

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

codehive> select stu_id, course_id, scorefrom score_infowhere course_id ='04' and score<60order by score desc;

结果

stu_id  course_id   score
004045900104540200450014044001704340100434

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

codehive> select s.stu_id, s.stu_name, t1.scorefrom student_info sjoin( select * from score_info where course_id=(select course_id from course_info where course_name='数学') and score <60) t1 on s.stu_id = t1.stu_idorder by s.stu_id;

select
    s.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 <60orderby stu_id;

结果

s.stu_id  s.stu_name   t1.score
005     唐国强      44007     陈坤        55008     吴京        34011     潘长江      49013     蒋欣        35014     赵丽颖      39015     刘亦菲      48017     范冰冰      34018     李冰冰      58019     邓紫棋      39020     宋丹丹      59

三汇总分析

3.1 汇总分析

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

codehive>select course_id,sum(score) score_sumfrom score_infowhere course_id='02'group by course_id;

结果

course_id    score_sum  
021133

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

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

codehive>select count(distinct stu_id) stu_numfrom score_info;

结果

stu_num                                                                             
19

3.2 分组

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

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

codehive> select course_id,max(score) max_score,min(score) min_scorefrom score_infogroup by course_id;

结果

course_id  max_score  min_score 
0194380293340399320410034058759

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

codehive> select course_id,count(stu_id) stu_numfrom score_infogroup by course_id;

结果

course_id    stu_num
0119021903190412055

3.2.3 查询男生、女生人数

codehive>select sex,count(stu_id) countfrom student_infogroup by sex;

结果

sex     count
女      9
男      11

3.3 分组结果的条件

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

1）思路分析

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

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

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

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

2）Hql实操

codehive> select stu_id,avg(score) score_avgfrom score_infogroup by stu_idhaving score_avg >60;

结果

stu_id  score_avg
00172.500286.2500481.500575.400673.3333333333333300974.201361.001570.2501681.2502069.75

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

1）思路分析

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

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

2）Hql实操

codehive> select stu_id,count(course_id) course_countfrom score_infogroup by stu_idhaving course_count >=4;

结果

stu_id    course_num
00140024004400550075009501040134014401540164017401840204

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

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

codehive>select t1.first_name,count(*) count_first_namefrom( select stu_id, stu_name,substr(stu_name,0,1) first_name from student_info) t1group by t1.first_namehaving count_first_name >=2;

结果

t1.first_name   count_first_name
刘      2
周      2
陈      2

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

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

codehive>select course_id,avg(score) score_avgfrom score_infogroup by course_idorder by score_avg asc, course_id desc;

结果

course_id   score_avg
0259.631578947368420463.4166666666666640167.157894736842110369.421052631578950574.6

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

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

codehive>select course_id,count(stu_id) stu_countfrom score_infogroup by course_idhaving stu_count >=15;

结果

course_id   stu_count
011902190319

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

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

思路：分组、sum、排序

codehive>select stu_id,sum(score) sum_scorefrom score_infogroup by stu_idorder by sum_score desc;

结果

stu_id    sum_score
005377009371002345004326016325007299001290015281020279013244010233018232006220014192017181012180011180019178008129

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

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

codehive>select si.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)`平均成绩`from score_info sijoin course_info cion si.course_id=ci.course_idgroup by si.stu_idorder by `平均成绩` desc

select
    sc.stu_id `学生id`,sum(casewhen ci.course_name='语文'and sc.score >0then score else0end)`语文`,sum(casewhen ci.course_name='数学'and sc.score >0then score else0end)`数学`,sum(casewhen ci.course_name='英语'and sc.score >0then score else0end)`英语`,count(ci.course_id)`有效课程数`,avg(sc.score)`平均成绩`from score_info sc
join course_info ci
on sc.course_id=ci.course_id
groupby sc.stu_id
orderby`平均成绩`desc;

结果

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

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

codehive>select t2.stu_id, s.stu_namefrom( select t1.stu_id from( select stu_id, course_id from score_info where stu_id in( select stu_id from score_info where course_id ="01")) t1 group by t1.stu_id having count(t1.course_id)=3) t2join 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分的学生的学号、姓名

codehive>select s.stu_id, s.stu_namefrom( select stu_id,sum(if(score >=60,1,0)) flag from score_info group by stu_id having flag =0) t1 join student_info s on s.stu_id = t1.stu_id;

结果

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

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

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

codehive>select s.stu_id, s.stu_namefrom student_info sleft join score_info sc on s.stu_id = sc.stu_idgroup by s.stu_id, s.stu_namehaving 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

codehive>select s.stu_id, s.stu_namefrom student_info sjoin( select stu_id,count(course_id) course_count from score_info group by stu_id having course_count =3) t1on 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。

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

codehive>select t1.stu_id, t2.avg_scorefrom( select stu_id,sum(if(score <60,1,0)) flage from score_info group by stu_id having flage >=2) t1join( select stu_id,avg(score) avg_score from score_info group by stu_id) t2 on t1.stu_id = t2.stu_id;

结果

t1.stu_id       t2.avg_score
00759.800843.001058.2501361.001448.001570.2501745.2501858.001959.33333333333333602069.75

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

codehive>select s.stu_id, s.stu_name,count(sc.course_id) count_course,sum(sc.score) sum_scorefrom student_info sleft join score_info sc on s.stu_id = sc.stu_idgroup by s.stu_id,s.stu_name;

结果

stu_id      stu_name    course_count    course_sum
001           彭于晏              4290002           胡歌                4345003               周杰伦              00004           刘德华              4326005           唐国强              5377006           陈道明              3220007           陈坤                5299008           吴京                3129009           郭德纲              5371010           于谦                4233011           潘长江              3180012           杨紫                3180013           蒋欣                4244014           赵丽颖              4192015           刘亦菲              4281016           周冬雨              4325017           范冰冰              4181018           李冰冰              4232019           邓紫棋              3178020           宋丹丹              4279

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

codehive>select s.stu_id, s.stu_name,avg(sc.score) avg_scorefrom score_info scleft join student_info s on s.stu_id = sc.stu_idgroup by s.stu_id, s.stu_namehaving avg_score >85

结果

stu_id        stu_name      avg_score
002           胡歌            86.25

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

codehive>select s.stu_id, s.stu_name, c.course_id, c.course_namefrom score_info scjoin course_info c on sc.course_id = c.course_idjoin 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:74row(s)

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

codehive> select c.course_id, c.course_name, t1.`及格人数`, t1.`不及格人数`from course_info cjoin( select course_id,sum(if(score >=60,1,0))as`及格人数`,sum(if(score <60,1,0))as`不及格人数` from score_info group by course_id ) t1 on c.course_id = t1.course_id;

结果

c.course_id     c.course_name   t1.及格人数     t1.不及格人数
01            语文            12702            数学            81103            英语            13604            体育            6605            音乐            41
Time taken:10.746 seconds,Fetched:5row(s)

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

codehive>select s.stu_id, s.stu_name, t1.score, t1.course_id, c.course_namefrom student_info sjoin( select stu_id, score, course_id from score_info where score >80 and course_id ='03') t1on s.stu_id = t1.stu_idjoin 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           胡歌            8703             英语
004           刘德华          8903             英语
005           唐国强          9903             英语
013           蒋欣            9303             英语
015           刘亦菲          8403             英语
019           邓紫棋          9303             英语
020           宋丹丹          8103             英语
Time taken:9.064 seconds,Fetched:7row(s)

5.2 多表连接

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

codehive>select s.stu_id, s.stu_name, s.birthday, s.sex, t1.scorefrom student_info sjoin( select stu_id, course_id, score from score_info where score <60 and course_id ='01') t1on s.stu_id=t1.stu_idorder by t1.score desc;

结果

s.stu_id        s.stu_name      s.birthday      s.sex   t1.score
017           范冰冰        1992-07-04        女      58008           吴京          1994-02-06        男      56007           陈坤          1999-04-09        男      48013           蒋欣          1997-11-08        女      47019           邓紫棋        1994-08-31        女      46012           杨紫          1996-12-21        女      44018           李冰冰        1993-09-24        女      38
Time taken:8.936 seconds,Fetched:7row(s)

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

codehive>select s.stu_id, s.stu_name, c.course_name, s2.scorefrom student_info sjoin( select stu_id,sum(if(score >=70,0,1)) flage from score_info group by stu_id having flage =0) t1on s.stu_id = t1.stu_idleft join score_info s2 on s.stu_id = s2.stu_idleft join course_info c on s2.course_id = c.course_id;

结果

s.stu_id      s.stu_name    c.course_name  s2.course
002     胡歌    语文    74002     胡歌    数学    84002     胡歌    英语    87002     胡歌    体育    100016     周冬雨  语文    71016     周冬雨  数学    89016     周冬雨  英语    71016     周冬雨  体育    94
Time taken:27.166 seconds,Fetched:8row(s)

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

codehive>select sc1.stu_id, sc1.course_id, sc1.scorefrom score_info sc1 join score_info sc2 on sc1.stu_id = sc2.stu_idand sc1.course_id <> sc2.course_idand sc1.score = sc2.score;

结果

sc1.stu_id   sc1.course_id    sc1.score
0160371017043401601710050585007056300905790170234005048500704630090479
Time taken:8.881 seconds,Fetched:10row(s)

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

知识点：多表连接 + 条件

codehive>select s1.stu_idfrom( select sc1.stu_id, sc1.course_id, sc1.score from score_info sc1 where sc1.course_id ='01') s1join( select sc2.stu_id, sc2.course_id, score from score_info sc2 where sc2.course_id ="02")s2on s1.stu_id=s2.stu_idwhere s1.score > s2.score;

结果

stu_id
001005008010011013014015017019020

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

codehive>select t1.stu_id as`学号`, s.stu_name as`姓名`from( select stu_id from score_info sc1 where sc1.course_id='01' and stu_id in( select stu_id from score_info sc2 where sc2.course_id='02'))t1join student_info son 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:19row(s)

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

codehive>select t1.stu_id, si.stu_namefrom( select stu_id from score_info si where course_id in( select course_id from course_info c join teacher_info t on c.tea_id = t.tea_id where tea_name='李体音'--李体音教的所有课程 ) group by stu_id having count(*)=2--学习所有课程的学生)t1left join student_info sion t1.stu_id=si.stu_id;

结果

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

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

codehive>select t1.stu_id, si.stu_namefrom( select stu_id from score_info si where course_id in( select course_id from course_info c join teacher_info t on c.tea_id = t.tea_id where tea_name='李体音') group by stu_id)t1left join student_info sion 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:14row(s)

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

codehive>select stu_id, stu_namefrom student_infowhere stu_id not in( select stu_id from score_info si where course_id in( select course_id from course_info c join teacher_info t on c.tea_id = t.tea_id where tea_name='李体音') group by stu_id);

结果

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

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

codehive>select si.stu_id, si.stu_namefrom score_info scjoin student_info sion sc.stu_id = si.stu_idwhere sc.course_id in( select course_id from score_info where 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:18row(s)

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

codehive>select si.stu_name, ci.course_name, sc.score, t1.avg_scorefrom score_info scjoin student_info sion sc.stu_id=si.stu_idjoin course_info cion sc.course_id=ci.course_idjoin( select stu_id,avg(score) avg_score from score_info group by stu_id)t1on sc.stu_id=t1.stu_idorder by t1.avg_score desc;

结果

t2.stu_name  t2.course_name  t2.score       t1.avg_score
胡歌    体育    10086.25
胡歌    数学    8486.25
胡歌    英语    8786.25
胡歌    语文    7486.25
刘德华  体育    5981.5
刘德华  语文    8581.5
刘德华  英语    8981.5
刘德华  数学    9381.5
周冬雨  英语    7181.25
周冬雨  数学    8981.25
周冬雨  体育    9481.25
周冬雨  语文    7181.25
唐国强  数学    4475.4
唐国强  音乐    8575.4
唐国强  语文    6475.4
唐国强  体育    8575.4
唐国强  英语    9975.4
郭德纲  音乐    7974.2
郭德纲  体育    7974.2
郭德纲  英语    6074.2
郭德纲  语文    7574.2
郭德纲  数学    7874.2
陈道明  语文    7173.33333333333333
陈道明  数学    9073.33333333333333
陈道明  英语    5973.33333333333333
……
李冰冰  音乐    8758.0
李冰冰  语文    3858.0
李冰冰  英语    4958.0
李冰冰  数学    5858.0
赵丽颖  数学    3948.0
赵丽颖  语文    8148.0
赵丽颖  体育    4048.0
赵丽颖  英语    3248.0
范冰冰  英语    5545.25
范冰冰  体育    3445.25
范冰冰  数学    3445.25
范冰冰  语文    5845.25
吴京    语文    5643.0
吴京    数学    3443.0
吴京    英语    3943.0
Time taken:20.137 seconds,Fetched:74row(s)

标签：大数据 hive hadoop

本文转载自: https://blog.csdn.net/yiluohan0307/article/details/129431358
版权归原作者 yiluohan0307 所有，如有侵权，请联系我们删除。

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

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

一 环境准备