2.2.7 聚合函数
1、语法
| 函数名 | 函数作用 |
| count(*) | 表示统计所有行数，包含null值； |
| count(某列) | 表示该列一共有多少行，不包含null值 |
| max() | 求最大值，不包含null，除非所有值都是null |
| min() | 求最小值，不包含null，除非所有值都是null |
| sum() | 求和，不包含null |
| avg() | 求平均值，不包含null |
--求总行数（count） select count(*) cnt from emp; --求工资的最大值（max） select max(sal) max_sal from emp; --求工资的最小值（min） select min(sal) min_sal from emp; --求工资的总和（sum） select sum(sal) sum_sal from emp; --求工资的平均值（avg） select avg(sal) avg_sal from emp;
2.3 分组
2.3.1 group by
Group by 语句通常会和聚合函数一起使用，按照一个或者多个队列结果进行分组，然后对每个组执行聚合操作。
--计算emp表每个部门的平均工资。 select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; --计算emp每个部门中每个岗位的最高薪水 select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;
2.3.2 Having语句
1、Having和where的不同点
（1）where后面不能写分组聚哈函数，而Having后面可以使用分组聚合函数
（2）having只用于group by 分组统计语句
--求每个部门的平均工资 select deptno, avg(sal) from emp group by deptno; --求每个部门的平均薪水大于2000的部门。 select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
2.4 Join
2.4.1 等值Join
Hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接。
--根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称。 select e.empno, e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;
2.4.2 表的别名
1、好处
（1）使用别名可以简化查询
（2）区分字段来源
--合并员工表和部门表。 select e.*, d.* from emp e join dept d on e.deptno = d.deptno;
2.4.3 内连接
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
2.4.4 左外连接
join操作符左边表中符合where子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
2.4.5 右外连接
join操作符右边表中符合where子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
2.4.6 满外连接
将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
2.4.7 多表连接
注意：连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
select e.ename, d.dname, l.loc_name from emp e join dept d on d.deptno = e.deptno join location l on d.loc = l.loc;
大多数情况下，Hive会对每对join连接对象启动一个MapReduce任务。本立会首先启动一个MapReduce job对表e和表d进行连接操作，然后再启动一个MapReduce job将第一个MapReduce job的输出和表1进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。
2.4.8 笛卡尔积
1、笛卡尔积会在下面条件产生
（1）省略连接条件
（2）连接条件无效
（3）所以表中的所有行互相连接
select empno, dname from emp, dept;
2.4.9 联合（union和union all）
1、union和union all上下拼接
union和union all都是上下拼接sql的结果，这点和join是有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。
union和union all上下拼接sql结果时有两个要求：
（1）两个sql结果，列的个数必须相同
（2）两个sql结果，上下所对应列的类型必须一致
select * from emp where deptno=30 union select * from emp where deptno=40;
2.5 排序
2.5.1 全局排序（Order by）
只有一个Reduce
1、使用Order by 子句排序
asc（ascend）：升序（默认）
desc（descend）：降序
2、Order by 子句在select语句的末尾
--查询员工信息按工资升序排列 select * from emp order by sal; --查询员工信息按工资降序排列 select * from emp order by sal desc; --按照别名排序案例实操 select ename, sal * 2 twosal from emp order by twosal; --多个列排序案例实操 select ename, deptno, sal from emp order by deptno, sal;
2.5.2 每个Reduce内部排序（Sort by）
对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用Sort by。
Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序。
1、设置reduce个数
set mapreduce.job.reduces=3;
2、查看设置reduce个数
set mapreduce.job.reduces;
3、根据部门编号降序查看员工信息
select * from emp sort by deptno desc;
2.5.3 分区（Distribute by）
Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by使用。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
--先按照部门编号分区，再按照员工编号薪资排序 set mapreduce.job.reduces=3; insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by sal desc;
注意：
（1）distribute by的分区规则是根据分区字段的hash码与reduce的个数进行相除后，余数相同的分到一个区
（2）Hive要求distribute by语句要写在sort by语句之前。
2.5.4 分区排序（Cluster by）
当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为asc或者desc。
select * from emp cluster by deptno; 或 select * from emp distribute by deptno sort by deptno;
注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。
