hadoop学习之hive练习题+答案

所用数据：https://pan.baidu.com/s/1XJBCKCMG727XQ6KA1E5Pow
提取码：dsdz

第一题

建表

create table visit(
user_id STRING,
shop STRING
)
row format delimited
fields terminated by ' ';
load data local inpath '/root/kb08/hive/1/jd.txt' into table visit;

1、每个店铺UV（访客数）

select shop,count(*)UV
from visit
group by shop;
+-------+-----+
| shop  | uv  |
+-------+-----+
| a     | 80  |
| b     | 40  |
| c     | 32  |
+-------+-----+

2、每个店铺访问次数top3的访客信息

with
r1 as 
(select
shop,user_id,count(*)UV
from visit
group by shop,user_id
having shop='a'
order by UV DESC
limit 3),
r2 as 
(select
shop,user_id,count(*)UV
from visit
group by shop,user_id
having shop='b'
order by UV DESC
limit 3),
r3 as 
(select
shop,user_id,count(*)UV
from visit
group by shop,user_id
having shop='c'
order by UV DESC
limit 3)
select * from r1 union all select * from r2 union all select * from r3;

+-----------+--------------+---------+--+
| _u1.shop  | _u1.user_id  | _u1.uv  |
+-----------+--------------+---------+--+
| a         | u1           | 32      |
| a         | u5           | 24      |
| a         | u2           | 16      |
| b         | u4           | 16      |
| b         | u5           | 8       |
| b         | u2           | 8       |
| c         | u2           | 16      |
| c         | u6           | 8       |
| c         | u3           | 8       |
+-----------+--------------+---------+--+

第二题

建表

create table storeconsume(
shop STRING,
month STRING,
price DOUBLE
)
row format delimited
fields terminated by ',';
load data local inpath '/root/kb08/hive/2/store.txt' into table storeconsume;

每个店铺的当月销售额和累计到当月的总销售额

select 
shop,
month,
SUM,
sum(SUM) over(distribute by shop sort by month)
from
(select 
shop,
month,
sum(price) SUM
from storeconsume
group by shop,month) r1;

+-------+--------+---------+----------+--+
| shop  | month  |   sum   |  _wcol0  |
+-------+--------+---------+----------+--+
| a     | 01     | 350.0   | 350.0    |
| a     | 02     | 5000.0  | 5350.0   |
| a     | 03     | 600.0   | 5950.0   |
| b     | 01     | 7800.0  | 7800.0   |
| b     | 02     | 2500.0  | 10300.0  |
| c     | 01     | 470.0   | 470.0    |
| c     | 02     | 630.0   | 1100.0   |
+-------+--------+---------+----------+--+

第三题

建表

create table sale(
name STRING,
month STRING,
pv INT
)
row format delimited
fields terminated by ',';
load data local inpath '/root/kb08/hive/3/pv.txt' into table sale;

每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数。

select
name,
month,
max_pv,
sum(sum_pv) over(distribute by name sort by month) sum
from
(select
name,
month,
max(pv) max_pv,
sum(pv) sum_pv
from sale
group by name,month) r1;

+-------+----------+---------+------+--+
| name  |  month   | max_pv  | sum  |
+-------+----------+---------+------+--+
| A     | 2015-01  | 15      | 33   |
| A     | 2015-02  | 6       | 43   |
| A     | 2015-03  | 22      | 81   |
| B     | 2015-01  | 25      | 30   |
| B     | 2015-02  | 10      | 45   |
| B     | 2015-03  | 23      | 89   |
+-------+----------+---------+------+--+

第四题

建表

-- 科目表（编号，名称，任课老师编号）
create table course(
sub_id INT,
sub_name STRING,
teacher_id INT
)
row format delimited
fields terminated by '\t';
load data local inpath '/root/kb08/hive/4/course.txt' into table course;
-- 成绩表（学生编号，科目编号，分数）
create table stuscore(
stu_id INT,
sub_id INT,
score DOUBLE
)
row format delimited
fields terminated by '\t';
load data local inpath '/root/kb08/hive/4/score.txt' into table stuscore;
-- 学生表（学生编号，学生姓名，出生日期，性别）
create table student(
stu_id INT,
stu_name STRING,
brithday DATE,
stu_gender STRING
)
row format delimited
fields terminated by '\t';
load data local inpath '/root/kb08/hive/4/student.txt' into table student;
-- 老师表（教师编号，教师姓名）
create table teacher(
teacher_id INT,
teacher_name STRING
)
row format delimited
fields terminated by '\t';
load data local inpath '/root/kb08/hive/4/teacher.txt' into table teacher;

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

select T.stu_id,T.stu_name,T.brithday,T.stu_gender,r1.score
from
(select stu_id,score from stuscore where sub_id='1') r1
inner join
(select stu_id,score from stuscore where sub_id='2') r2
on r1.stu_id = r2.stu_id
join student T
on T.stu_id = r2.stu_id
where r1.score > r2.score;
+-----------+-------------+-------------+---------------+-----------+--+
| t.stu_id  | t.stu_name  | t.brithday  | t.stu_gender  | r1.score  |
+-----------+-------------+-------------+---------------+-----------+--+
| 2         | 钱电          | 1990-12-21  | 男             | 70.0      |
| 4         | 李云          | 1990-08-06  | 男             | 50.0      |
+-----------+-------------+-------------+---------------+-----------+--+

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

select T.stu_id,T.stu_name,T.brithday,T.stu_gender,r1.score
from
(select stu_id,score from stuscore where sub_id='1') r1
inner join
(select stu_id,score from stuscore where sub_id='2') r2
on r1.stu_id = r2.stu_id
join student T
on T.stu_id = r2.stu_id
where r1.score < r2.score;
+-----------+-------------+-------------+---------------+-----------+--+
| t.stu_id  | t.stu_name  | t.brithday  | t.stu_gender  | r1.score  |
+-----------+-------------+-------------+---------------+-----------+--+
| 1         | 赵雷          | 1990-01-01  | 男             | 80.0      |
| 5         | 周梅          | 1991-12-01  | 女             | 76.0      |
+-----------+-------------+-------------+---------------+-----------+--+

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

select T.stu_id,T.stu_name,r1.avg_score
from
(select stu_id,avg(score) avg_score from stuscore group by stu_id) r1
inner join student T
on T.stu_id = r1.stu_id
where r1.avg_score>=60;
+-----------+-------------+--------------------+--+
| t.stu_id  | t.stu_name  |    r1.avg_score    |
+-----------+-------------+--------------------+--+
| 1         | 赵雷          | 89.66666666666667  |
| 2         | 钱电          | 70.0               |
| 3         | 孙风          | 80.0               |
| 5         | 周梅          | 81.5               |
| 7         | 郑竹          | 93.5               |
+-----------+-------------+--------------------+--+

4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩:

select T.stu_id,T.stu_name,r1.avg_score
from
(select stu_id,sum(score)/3 avg_score from stuscore group by stu_id) r1
inner join student T
on T.stu_id = r1.stu_id
where r1.avg_score<60;
+-----------+-------------+---------------------+--+
| t.stu_id  | t.stu_name  |    r1.avg_score     |
+-----------+-------------+---------------------+--+
| 4         | 李云          | 33.333333333333336  |
| 5         | 周梅          | 54.333333333333336  |
| 6         | 吴兰          | 21.666666666666668  |
+-----------+-------------+---------------------+--+

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

select T.stu_id,T.stu_name,r1.sub_num,r1.sum_score
from
(select stu_id,count(*) sub_num,sum(score) sum_score 
from stuscore
group by stu_id) r1
inner join student T
on T.stu_id = r1.stu_id;
+-----------+-------------+-------------+---------------+--+
| t.stu_id  | t.stu_name  | r1.sub_num  | r1.sum_score  |
+-----------+-------------+-------------+---------------+--+
| 1         | 赵雷          | 3           | 269.0         |
| 2         | 钱电          | 3           | 210.0         |
| 3         | 孙风          | 3           | 240.0         |
| 4         | 李云          | 3           | 100.0         |
| 5         | 周梅          | 2           | 163.0         |
| 6         | 吴兰          | 2           | 65.0          |
| 7         | 郑竹          | 2           | 187.0         |
+-----------+-------------+-------------+---------------+--+

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

select count(*) num from teacher where teacher_name like '李%';
+------+--+
| num  |
+------+--+
| 1    |
+------+--+

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

select T.stu_id,T.stu_name,T.brithday,T.stu_gender
from student T
inner join
stuscore E
on T.stu_id=E.stu_id
join
(select sub_id
from teacher R
inner join course C
on R.teacher_id=C.teacher_id
where R.teacher_name='张三') r1
where E.sub_id=r1.sub_id;

+-----------+-------------+-------------+---------------+--+
| t.stu_id  | t.stu_name  | t.brithday  | t.stu_gender  |
+-----------+-------------+-------------+---------------+--+
| 1         | 赵雷          | 1990-01-01  | 男             |
| 2         | 钱电          | 1990-12-21  | 男             |
| 3         | 孙风          | 1990-05-20  | 男             |
| 4         | 李云          | 1990-08-06  | 男             |
| 5         | 周梅          | 1991-12-01  | 女             |
| 7         | 郑竹          | 1989-07-01  | 女             |
+-----------+-------------+-------------+---------------+--+

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

select T.stu_id,T.stu_name,T.brithday,T.stu_gender
from student T
left join
(select stu_id 
from stuscore E
join  course C
on E.sub_id=C.sub_id
join  teacher R
on C.teacher_id=R.teacher_id and teacher_name='张三') r1
on T.stu_id = r1.stu_id
where r1.stu_id is null;
+-----------+-------------+-------------+---------------+--+
| t.stu_id  | t.stu_name  | t.brithday  | t.stu_gender  |
+-----------+-------------+-------------+---------------+--+
| 6         | 吴兰          | 1992-03-01  | 女             |
| 8         | 王菊          | 1990-01-20  | 女             |
+-----------+-------------+-------------+---------------+--+

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

select T.stu_id,T.stu_name,T.brithday,T.stu_gender
from student T
join
(select stu_id from stuscore where sub_id='1') r1
on T.stu_id=r1.stu_id
join
(select stu_id from stuscore where sub_id='2') r2
on T.stu_id=r2.stu_id;
+-----------+-------------+-------------+---------------+--+
| t.stu_id  | t.stu_name  | t.brithday  | t.stu_gender  |
+-----------+-------------+-------------+---------------+--+
| 1         | 赵雷          | 1990-01-01  | 男             |
| 2         | 钱电          | 1990-12-21  | 男             |
| 3         | 孙风          | 1990-05-20  | 男             |
| 4         | 李云          | 1990-08-06  | 男             |
| 5         | 周梅          | 1991-12-01  | 女             |
+-----------+-------------+-------------+---------------+--+

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

select T.stu_id,T.stu_name,T.brithday,T.stu_gender
from student T
join
(select stu_id from stuscore where sub_id='1') r1
on T.stu_id=r1.stu_id
left join
(select stu_id from stuscore where sub_id='2') r2
on T.stu_id=r2.stu_id
where r2.stu_id is null;
+-----------+-------------+-------------+---------------+--+
| t.stu_id  | t.stu_name  | t.brithday  | t.stu_gender  |
+-----------+-------------+-------------+---------------+--+
| 6         | 吴兰          | 1992-03-01  | 女             |
+-----------+-------------+-------------+---------------+--+