Hive常用查询语句

数据源如下：

学生表：
sid sname	birth		sex
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	女
老师表：
tid tname
01	张三
02	李四
03	王五
课程表：
cid cname   tid
01	语文	02
02	数学	01
03	英语	03
成绩表：
sid cid score
01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

下面将通过上述简单数据对hivesql的查询进行练习：

# 使用limit指定返回行数
select * from student limit 4;
OK
1	赵雷	1990-01-01	男
2	钱电	1990-12-21	男
3	孙风	1990-05-20	男
4	李云	1990-08-06	男
Time taken: 0.203 seconds, Fetched: 4 row(s)

# 查询出分数大于60的数据
select * from score where score>60;
OK
1	1	80
1	2	90
1	3	99
2	1	70
2	3	80
3	1	80
3	2	80
3	3	80
5	1	76
5	2	87
7	2	89
7	3	98
Time taken: 1.555 seconds, Fetched: 12 row(s)

# 查询分数在80到100的所有数据
select * from score where score between 80 and 100;
OK
1	1	80
1	2	90
1	3	99
2	3	80
3	1	80
3	2	80
3	3	80
5	2	87
7	2	89
7	3	98
Time taken: 0.15 seconds, Fetched: 10 row(s)

# 查询成绩为空的所有数据
select * from score where score is NULL;
OK
Time taken: 0.157 seconds

# 查询成绩是80和90的数据
select * from score where score in (80, 90);
OK
1	1	80
1	2	90
2	3	80
3	1	80
3	2	80
3	3	80
Time taken: 0.139 seconds, Fetched: 6 row(s)

# 查找以8开头的所有成绩
select * from score where score like "8%";
OK
1	1	80
2	3	80
3	1	80
3	2	80
3	3	80
5	2	87
7	2	89
Time taken: 0.181 seconds, Fetched: 7 row(s)

# 查找第二个数值为9的所有成绩数据
select * from score where score like "_9%";
OK
1	3	99
7	2	89
Time taken: 0.153 seconds, Fetched: 2 row(s)

# 查找s_id中含1的数据
select * from score where score like "%1%";
OK
6	1	31
Time taken: 0.116 seconds, Fetched: 1 row(s)

# 计算每个学生的平均分数
select sid, avg(score) from score group by sid;
OK
1	89.66666666666667
2	70.0
3	80.0
4	33.333333333333336
5	81.5
6	32.5
7	93.5
Time taken: 3.568 seconds, Fetched: 7 row(s)

# 计算每个学生高成绩
select sid, cid, max(score) from score group by sid;
OK
1	99
2	80
3	80
4	50
5	87
6	34
7	98
Time taken: 1.698 seconds, Fetched: 7 row(s)

# 求每个学生平均分数大于85的人
select sid,avg(score) avg from score group by sid having avg > 85;
OK
1	89.66666666666667
7	93.5
Time taken: 6.188 seconds, Fetched: 2 row(s)

# 等值连接join
select stu.sid, stu.sname, stu.birth, stu.sex, sc.cid, sc.score from student stu join score sc on stu.sid = sc.sid;
OK
1	赵雷	1990-01-01	男	1	80
1	赵雷	1990-01-01	男	2	90
1	赵雷	1990-01-01	男	3	99
2	钱电	1990-12-21	男	1	70
2	钱电	1990-12-21	男	2	60
2	钱电	1990-12-21	男	3	80
3	孙风	1990-05-20	男	1	80
3	孙风	1990-05-20	男	2	80
3	孙风	1990-05-20	男	3	80
4	李云	1990-08-06	男	1	50
4	李云	1990-08-06	男	2	30
4	李云	1990-08-06	男	3	20
5	周梅	1991-12-01	女	1	76
5	周梅	1991-12-01	女	2	87
6	吴兰	1992-03-01	女	1	31
6	吴兰	1992-03-01	女	3	34
7	郑竹	1989-07-01	女	2	89
7	郑竹	1989-07-01	女	3	98
Time taken: 12.416 seconds, Fetched: 18 row(s)

# 内连接
select * from teacher t inner join course c on t.tid = c.tid;
OK
2	李四	1	语文	2
1	张三	2	数学	1
3	王五	3	英语	3
Time taken: 9.088 seconds, Fetched: 3 row(s)

# 左外连接
select * from teacher t left join course c on t.tid = c.tid;
OK
1	张三	2	数学	1
2	李四	1	语文	2
3	王五	3	英语	3
Time taken: 9.444 seconds, Fetched: 3 row(s)

# 右外链接
select * from teacher t right join course c on t.tid = c.tid;
OK
2	李四	1	语文	2
1	张三	2	数学	1
3	王五	3	英语	3
Time taken: 9.625 seconds, Fetched: 3 row(s)

# 多表连接
select * from teacher t left join course c
    > on t.tid=c.tid
    > left join score sc
    > on sc.cid=c.cid
    > left join student stu
    > on stu.sid=sc.sid;
OK
1	张三	2	数学	1	1	2	90	1	赵雷	1990-01-01	男
1	张三	2	数学	1	2	2	60	2	钱电	1990-12-21	男
1	张三	2	数学	1	3	2	80	3	孙风	1990-05-20	男
1	张三	2	数学	1	4	2	30	4	李云	1990-08-06	男
1	张三	2	数学	1	5	2	87	5	周梅	1991-12-01	女
1	张三	2	数学	1	7	2	89	7	郑竹	1989-07-01	女
2	李四	1	语文	2	1	1	80	1	赵雷	1990-01-01	男
2	李四	1	语文	2	2	1	70	2	钱电	1990-12-21	男
2	李四	1	语文	2	3	1	80	3	孙风	1990-05-20	男
2	李四	1	语文	2	4	1	50	4	李云	1990-08-06	男
2	李四	1	语文	2	5	1	76	5	周梅	1991-12-01	女
2	李四	1	语文	2	6	1	31	6	吴兰	1992-03-01	女
3	王五	3	英语	3	1	3	99	1	赵雷	1990-01-01	男
3	王五	3	英语	3	2	3	80	2	钱电	1990-12-21	男
3	王五	3	英语	3	3	3	80	3	孙风	1990-05-20	男
3	王五	3	英语	3	4	3	20	4	李云	1990-08-06	男
3	王五	3	英语	3	6	3	34	6	吴兰	1992-03-01	女
3	王五	3	英语	3	7	3	98	7	郑竹	1989-07-01	女
Time taken: 10.311 seconds, Fetched: 18 row(s)

# 全局排序order by只有一个reduce 
# ASC（升序排序,默认）
select * from score order by score;
OK
4	3	20
4	2	30
6	1	31
6	3	34
4	1	50
2	2	60
2	1	70
5	1	76
1	1	80
3	3	80
3	2	80
3	1	80
2	3	80
5	2	87
7	2	89
1	2	90
7	3	98
1	3	99
Time taken: 1.34 seconds, Fetched: 18 row(s)
# DESC（降序排序）
select * from score order by score;
OK
1	3	99
7	3	98
1	2	90
7	2	89
5	2	87
3	3	80
3	2	80
3	1	80
2	3	80
1	1	80
5	1	76
2	1	70
2	2	60
4	1	50
6	3	34
6	1	31
4	2	30
4	3	20
Time taken: 1.46 seconds, Fetched: 18 row(s)

# 多列排序
select sid,avg(score) avg_score from score group by sid order by sid,avg_score;
OK
1	89.66666666666667
2	70.0
3	80.0
4	33.333333333333336
5	81.5
6	32.5
7	93.5
Time taken: 2.658 seconds, Fetched: 7 row(s)

# 每个MapReduce内部有序（sort by）
# 先设置reduce个数
set mapreduce.job.reduces=3;
# reduce内部按成绩排序
select * from score sort by score;
OK
6	1	31
4	1	50
2	2	60
2	3	80
3	1	80
3	2	80
3	3	80
7	3	98
------------
6	3	34
5	1	76
1	1	80
------------
4	3	20
4	2	30
2	1	70
5	2	87
7	2	89
1	2	90
1	3	99
Time taken: 1.285 seconds, Fetched: 18 row(s)

# 分区DISTRIBUTE BY，一般配合sort by使用，组合使用就有分区排序效果
# 首先设置分区数
set mapreduce.job.reduces=7;
# 按学号分区，分区内部按成绩排序
select * from score distribute by sid order by score;
OK
7	2	89
7	3	98
3	3	80
3	2	80
3	1	80
2	2	60
2	1	70
2	3	80
6	1	31
6	3	34
4	3	20
4	2	30
4	1	50
5	1	76
5	2	87
1	1	80
1	2	90
1	3	99
Time taken: 1.306 seconds, Fetched: 18 row(s)

# 分区排序之cluster by
# 使用cluster by等同于使用distribute by sid sort by sid 以sid分区并按sid排序
select *from score cluster by sid;
OK
7	3	98
7	2	89
3	3	80
3	2	80
3	1	80
2	1	70
2	3	80
2	2	60
6	1	31
6	3	34
4	3	20
4	2	30
4	1	50
5	1	76
5	2	87
1	1	80
1	3	99
1	2	90
Time taken: 1.325 seconds, Fetched: 18 row(s)