11分组和Join

一、分组–GroupBy

1.GroupBy 语句定义

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

2.实例

(1)计算emp表每个部门的平均工资
	select t.deptno,avg(t.sal) avg_sal from emp t group by t.deptno;
	
(2)计算emp每个部门中每个岗位的最高薪水
	select t.deptno,t.job,max(x.sal) max_sal from emp t group by t.deptno,t.job;

二、分组–Having

1.having与where不同点

	(1)where针对表中的列，查询数据；having针对查询结果的列，筛选数据。
	(2)where后面不能写分组函数，而having后面可以使用分组函数。
	(3)having只用于group by分组统计语句。

2.实例

	(1)求每个部门的平均工资
		select deptno,avg(sal) from emp 
		group by deptno;
		
	(2)求每个部门的平均工资大于2000的部门
		select deptno,avg(sal) avg_sal from emp 
		group by deptno 
		having avg_sal>2000;

三、join语句–等值Join

1.hive支持

hive支持通常的SQL·JOIN语句，但是只支持等值连接，不支持非等值连接

2.实例

	根据员工表和部门表中的部门编号相等，
		查询员工编号、员工名称和部门名称
	select e.empno,e.ename,d.deptno,d.dname 
	from emp e join dept d on e.deptno = d.deptno;

四、join语句–表的别名

1.好处

	(1)使用别名简化查询。
	(2)使用表名前缀可以提高执行效率。

2.实例

	合并员工表和部门表
select e.empno,e.ename,d.deptno 
	from emp e join dept d on e.deptno = d.deptno;

五、join语句–内连接

1.内连接：
	只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
	
2.实例
	select e.lobal,d.id,d.loc from emp e join dept d on e.name=d.name;

六、join语句–左外连接

1.左外连接：
	JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
	
2.实例
	select e.empno,e.ename,d.deptno from emp e left 
	join dept d on e.deptno=d.deptno;

七、join语句–右外连接

1.右外连接：
	JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
	
2.实例
	select e.empno,e.ename,d.deptno from emp e right 
	join dept d on e.deptno=d.deptno;

八、join语句–满外连接

1.满外连接：
	将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的
	指定字段没有符合条件的值的话，那么就使用NULL值替代。
	
2.实例
	select e.empno,e.ename,d.deptno from emp e full 
	join dept d on e.deptno=d.deptno;

九、join语句–多表连接

1.注意：

	连接n个表，至少需要(n-1)个连接条件。

2.创建位置表

create table if not exists default.location(loc_id int,loc_name string)
	row format delimited fields terminated by '\t';

3.导入数据

load data local inpath '/root/hivedata/location.txt' 
	into table default.location;

4.多表连接查询

	SELECT e.ename,d.deptno,l.loc_name
	FORM emp e
	JOIN dept d
	ON d.deptno=e.deptno
	JOIN location l
	ON d.loc_id=l.loc_id;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。
本例中会首先启动一个MapReduce job对表e和表d进行连接操作，
然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。

5.注意：

	为什么不是表d和表l先进行连接操作呢？
		这是因为Hive总是按照从左到右的顺序执行的。

十、join语句–笛卡尔积

1.笛卡尔积会在下面条件下产生
	(1)省略连接条件
	(2)连接条件无效
	(3)所有表中的所有行互相连接

2.实例
	select empno,dname from emp,dept;

十一、join语句-- 连接谓词中不支持or

	select e.empno, e.ename, d.deptno from emp e 
	join dept d on e.deptno=d.deptno or e.ename=d.ename; 
	错误的