【博学谷学习记录】超强总结，用心分享|HiveSQL必练50题(上)

最新推荐文章于 2024-08-14 15:20:01 发布

熊猫同学呀

最新推荐文章于 2024-08-14 15:20:01 发布

阅读量252

点赞数 2

分类专栏： Hive 博学谷学习记录文章标签：学习 hive 大数据

本文链接：https://blog.csdn.net/xz842913787/article/details/128074807

版权

博学谷学习记录同时被 2 个专栏收录

21 篇文章 1 订阅

订阅专栏

Hive

11 篇文章 1 订阅

订阅专栏

文章目录

Hive版本: 3.1.2

https://blog.csdn.net/Thomson617/article/details/83212338
题目是这个博客提供的,答题是自己根据题目写的,所以有些答案和原博主的不一样,sql者是已经验证可以执行并能够获取正确结果的,如果有题目理解错误请大家指出

练习时建议开启本地模式, 大幅缩短执行时间

--开启本地mr
set hive.exec.mode.local.auto=true;

数据源

vim /export/data/hivedatas/student.txt

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 女

vim /export/data/hivedatas/course.txt

01 语文 02
02 数学 01
03 英语 03

vim /export/data/hivedatas/teacher.txt

01 张三
02 李四
03 王五

vim /export/data/hivedatas/score.txt

建表语句


create table student
(
    s_id    string,
    s_name  string,
    s_birth string,
    s_sex   string
) row format delimited fields terminated by ' ';

create table course
(
    c_id   string,
    c_name string,
    t_id   string
) row format delimited fields terminated by ' ';

create table teacher
(
    t_id   string,
    t_name string
) row format delimited fields terminated by ' ';

create table score
(
    s_id    string,
    c_id    string,
    s_score int
) row format delimited fields terminated by ' ';

导入数据

load data local inpath '/export/data/hivedatas/student.txt' into table student;

load data local inpath '/export/data/hivedatas/course.txt' into table course;

load data local inpath '/export/data/hivedatas/teacher.txt' into table teacher;

load data local inpath '/export/data/hivedatas/score.txt' into table score;

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

select s.*, s2.s_score score1, s3.s_score score2
from student s
         join score s2 on s.s_id = s2.s_id and s2.c_id = '01'
         join score s3 on s.s_id = s3.s_id and s3.c_id = '02'
where s2.s_score > s3.s_score;

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

select s.*, s2.s_score score1, s3.s_score score2
from student s
         join score s2 on s.s_id = s2.s_id and s2.c_id = '01'
         join score s3 on s.s_id = s3.s_id and s3.c_id = '02'
where s2.s_score < s3.s_score;

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

select s.s_id, s.s_name, avg(s_score) as score_avg
from student s
         left join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name
having score_avg >= 60;

select s.s_id, s.s_name, temp.avg_score
from student s
         right join (select s_id, avg(s_score) as avg_score from score group by s_id having avg_score >= 60) as temp
                    on s.s_id = temp.s_id;

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

select s.s_id, s.s_name, temp.avg_score
from student s
         left join (select s_id, avg(s_score) as avg_score
                    from score
                    group by s_id) as temp on s.s_id = temp.s_id
where avg_score < 60
   or avg_score is null;

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

select s.s_id, s.s_name, count(s2.c_id) count_course, sum(s2.s_score) sum_score
from student s
         left join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name;

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

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

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

select s.*, t.t_name, c.c_name
from student s
         left join score s2 on s.s_id = s2.s_id
         left join course c on s2.c_id = c.c_id
         left join teacher t on c.t_id = t.t_id
where t_name = '张三';

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

select s.*
from student s
         left join (select s.*, t.t_name, c.c_name
                    from student s
                             left join score s2 on s.s_id = s2.s_id
                             left join course c on s2.c_id = c.c_id
                             left join teacher t on c.t_id = t.t_id
                    where t_name = '张三') temp
                   on s.s_id = temp.s_id
where temp.t_name is null;

select *
from student
where s_id not in (select s.s_id
                   from student s
                            left join score s2 on s.s_id = s2.s_id
                            left join course c on s2.c_id = c.c_id
                            left join teacher t on c.t_id = t.t_id
                   where t_name = '张三');

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

select s.*
from student s
         join score s2 on s.s_id = s2.s_id and s2.c_id = '01'
         join score s3 on s.s_id = s3.s_id and s3.c_id = '02';

```sql
select *
from student
         join (select s_id from score where c_id = 1) tmp1
              on student.s_id = tmp1.s_id
         join (select s_id from score where c_id = 2) tmp2
              on student.s_id = tmp2.s_id;

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

select s.*, s3.c_id
from student s
         join score s2 on s.s_id = s2.s_id and s2.c_id = '01'
         left join score s3 on s.s_id = s3.s_id and s3.c_id = '02'
where s3.c_id is null;

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

select s.s_id, s.s_name, count(s2.c_id) count
from student s
         left join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name
having count < 3;

```sql
select *
from student s
where s_id not in (select s.s_id
                   from score s
                   group by s.s_id
                   having count(c_id) = 3);

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

with t as (select s.*, row_number() over (partition by s.s_id order by s.s_id) rk
           from student s
                    join (select c_id
                          from score
                          where s_id = '01') temp1
                    join (select s_id,
                                 c_id
                          from score) temp2
                         on temp1.c_id = temp2.c_id and s.s_id = temp2.s_id)

select *
from t
where rk = 1;

select s.*, row_number() over (partition by s.s_id order by s.s_id) rk
from student s
         join (select c_id
               from score
               where s_id = '01') temp1
         join (select s_id,
                      c_id
               from score) temp2
              on temp1.c_id = temp2.c_id and s.s_id = temp2.s_id
group by s.s_id, s.s_name, s.s_birth, s.s_sex;

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

select s.*
from student s
         join (select s_id, concat_ws('|', collect_set(c_id)) course1
               from score
               group by s_id
               having s_id != '01') temp1
              on s.s_id = temp1.s_id
         join (select concat_ws('|', collect_set(c_id)) course2 from score group by s_id having s_id = '01') temp2
              on temp1.course1 = temp2.course2;

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select s.*
from student s
         left join (select *
                    from score s2
                             join course c on s2.c_id = c.c_id
                             join teacher t on c.t_id = t.t_id and t_name = '张三') t1 on s.s_id = t1.s_id
where t1.t_name is null;

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.*, s2.avg_score
from student s
         join (select s_id,
                      count(c_id)  count_fail,
                      avg(s_score) avg_score
               from score
               where s_score < 60
               group by s_id) s2
              on s.s_id = s2.s_id;

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

select s.*
from student s
         join (select *
               from score
               where c_id = '01'
                 and s_score < 60) s2 on s.s_id = s2.s_id
order by s2.s_score desc;

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

select s1.*, s2.s_score, avg(s_score) over (partition by s1.s_id) avg_score
from student s1,
     score s2
where s1.s_id = s2.s_id
order by avg_score desc;

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select c.c_id,
       c.c_name,
       max(s.s_score)                                                                                max_score,
       min(s.s_score)                                                                                min_score,
       round(avg(s.s_score), 2)                                                                      avg_score,
       round(sum(case when s.s_score >= 60 then 1 else 0 end) / count(c.c_id), 2)                    passRate,
       round(sum(case when s.s_score >= 70 and s.s_score < 80 then 1 else 0 end) / count(c.c_id), 2) middleRate,
       round(sum(case when s.s_score >= 80 and s.s_score < 90 then 1 else 0 end) / count(c.c_id), 2) goodRate,
       round(sum(case when s.s_score >= 90 then 1 else 0 end) / count(c.c_id), 2)                    excellentRate
from course c,
     score s
where c.c_id = s.c_id
group by c.c_id, c.c_name;

19、按各科成绩进行排序，并显示排名

select *, row_number() over (partition by c_id order by s_score desc ) rk
from score;

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

select s1.s_id,
       sum(s2.s_score)                                    sum_score,
       row_number() over (order by sum(s2.s_score) desc ) rk
from student s1,
     score s2
where s1.s_id = s2.s_id
group by s1.s_id;

21、查询不同老师所教不同课程平均分从高到低显示

select t.t_name, s.c_id, avg(s_score) avg_score
from score s
         join course c on s.c_id = c.c_id
         join teacher t on c.t_id = t.t_id
group by s.c_id, t.t_name
order by avg_score desc;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select *
from student s
         join (with t as (select *, dense_rank() over (partition by c_id order by s_score desc ) rk
                          from score)
               select *
               from t
               where rk = 2
                  or rk = 3) s2 on s.s_id = s2.s_id;

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select c.c_id,
       c.c_name,
       sum(case when s.s_score >= 85 then 1 else 0 end)                                              s100_85,
       sum(case when s.s_score >= 70 and s.s_score < 85 then 1 else 0 end)                           s85_70,
       sum(case when s.s_score >= 60 and s.s_score < 70 then 1 else 0 end)                           s70_60,
       sum(case when s.s_score < 60 then 1 else 0 end)                                               s60_0,
       round(sum(case when s.s_score >= 85 then 1 else 0 end) / count(s.c_id), 2)                    s100_85_Rate,
       round(sum(case when s.s_score >= 70 and s.s_score < 85 then 1 else 0 end) / count(s.c_id), 2) s85_70_Rate,
       round(sum(case when s.s_score >= 60 and s.s_score < 70 then 1 else 0 end) / count(s.c_id), 2) s70_60_Rate,
       round(sum(case when s.s_score < 60 then 1 else 0 end) / count(s.c_id), 2)                     s60_0_Rate
from course c
         join score s on c.c_id = s.c_id
group by c.c_id, c.c_name;

24、查询学生平均成绩及其名次

select s.s_id,
       s.s_name,
       round(avg(s2.s_score), 2)                         avg_score,
       dense_rank() over (order by avg(s2.s_score) desc) rk
from student s
         join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name;

25、查询各科成绩前三名的记录

with t1 as (select s.*,
                   s2.s_score,
                   c.*,
                   dense_rank() over (partition by s2.c_id order by s2.s_score) rk
            from student s
                     join score s2 on s.s_id = s2.s_id
                     left join course c on s2.c_id = c.c_id)

select *
from t1
where rk < 4;