### 第一题

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  | 女             |
+-----------+-------------+-------------+---------------+--+

01-15
12-06

02-25 420
04-13 3539
07-22 1101
10-19 1285
06-27 2万+
05-06 748