Hive（二）查询、排序

SELECT

基本查询语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
][
LIMIT number]

查询语法
全表查询

select * from score;

选择特定列查询

select s_id ,c_id from score;

列别名
1）重命名一个列。
2）便于计算。
3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

select s_id as myid ,c_id from score;

常用函数

求总行数（count）

select count(1) from score;

求分数的最大值（max）

select max(s_score) from score;

求分数的最小值（min）

select min(s_score) from score;

求分数的总和（sum）

select sum(s_score) from score;

求分数的平均值（avg）

select avg(s_score) from score;

LIMIT语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from score limit 3;

WHERE语句

1. 使用WHERE 子句，将不满足条件的行过滤掉。
2. WHERE 子句紧随 FROM 子句。
3. 可集合逻辑运算符使用（and、or、not）
   如：查询出分数大于60的数据

select * from score where s_score > 60;

查询列s_score值在80到100的所有数据，区间数据

select * from score where s_score between 80 and 100;

查询列名s_score值为空的数据

select * from score where s_score is null;

查询列s_score值是80和90的数据

select * from score where s_score in(80,90);

like 和 rlike

% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
如：

1. 查找列s_score以8开头的所有成绩

select * from score where s_score like '8%';

2. 查找列s_score第二个数值为9的所有数据

select * from score where s_score like '_9%';

3. 查找列s_score中含9的所有成绩数据

select * from score where s_score rlike '[9]'; # like '%9%'

分组

GROUP BY 语句

通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

计算每个学生的平均分数

select s_id ,avg(s_score) from score group by s_id;

计算每个学生最高成绩

select s_id ,max(s_score) from score group by s_id;

HAVING 语句

1. having与where不同点
   1. where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。（前者对表中的列操作，后者对查询后的数据的列操作）
   2. where后面不能写分组函数，而having后面可以使用分组函数。
   3. having只用于group by分组统计语句。
   如：
   求每个学生的平均分数

select s_id ,avg(s_score) from score group by s_id;

求每个学生平均分数大于85的人

select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

表的别名

好处：
简化表名的输入。
使用表名前缀可以提高执行效率。
如：

select * from techer t join course c on t.t_id = c.t_id;
# 
select * from 表名1 别名1 join 表名2 别名2 on 别名1.字段 = 别名2.字段;

内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select * from techer t inner join course c on t.t_id = c.t_id;

左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
查询老师对应的课程

select * from techer t left join course c on t.t_id = c.t_id;

右外连接

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select * from teacher t right join course c on t.t_id = c.t_id;

多表连接

注意：连接 n个表，至少需要n-1个连接条件

select * from teacher t left join course c on t.t_id = c.t_id
left join score s on s.c_id = c.c_id
left join student stu on s.s_id = stu.s_id;

排序

全局排序

Order By：全局排序，一个reduce

1. 使用 ORDER BY 子句排序
   ASC（ascend）: 升序（默认）
   DESC（descend）: 降序
2. ORDER BY 子句在SELECT语句的结尾。
   如：
   查询学生的成绩，并按照分数降序排列

SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;
# 通过sco下的s_score字段进行降序

按照别名排序

如：在多个科目中，按照分数的平均值排序

select s_id ,avg(s_score) avg from score group by s_id order by avg;
# avg(s_score) 列的别名设为 avg
# order by avg 使用avg值进行排序（默认是升序）

多个列排序

按照学生id和平均成绩进行排序

select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;
# 直接在order by之后置多个字段

每个MapReduce内部排序（Sort By）局部排序

Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。

1. 设置reduce个数

set mapreduce.job.reduces=3;

2. 查看设置reduce个数

hive>set mapreduce.job.reduces;

3. 查询成绩按照成绩降序排列

select * from score sort by s_score;

4. 将查询结果导入到文件中（按照成绩降序排列）

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

分区排序（distribute by）

distribute by：类似MR中partition，进行分区，结合sort by使用。
注意: Hive要求distribute by语句要写在sort by语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实操：先按照学生id进行分区，再按照学生成绩进行排序。

1. 设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去
   set mapreduce.job.reduces=7;
2. 通过distribute by 进行数据的分区

insert overwrite local directory '/export/servers/hivedatas/sort' 
select * from score distribute by s_id sort by s_score;

cluster by

当distribute by和sort by字段相同时，可以使用cluster by方式。

scluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC
或者DESC。

以下两种写法等价

select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;