Hive SQL 分组与连接操作详解

分组

Group By语句

Group By

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

1. 案例实操

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(t.sal) max_sal FROM emp t GROUP BY t.deptno, t.job;

** Having语句**

1.

having

与

where

不同点

1. where 后面不能写分组聚合函数，而 having 后面可以使用分组聚合函数。
2. having 只用于 group by 分组统计语句。

2. 案例实操

1. 求每个部门的平均薪水大于2000的部门 ① 求每个部门的平均工资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;

** Join语句**

** 等值Join**

Hive支持通常的SQL

join

语句，但是只支持等值连接，不支持非等值连接。

1. 案例实操

1. 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称SELECT e.empno, e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno;

** 表的别名**

1. 好处

1. 使用别名可以简化查询。
2. 区分字段的来源。

2. 案例实操

合并员工表和部门表。

SELECT 
  e.*, 
  d.* 
FROM emp e 
JOIN dept d 
ON e.deptno = d.deptno;

** 内连接**

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

SELECT 
  e.empno, 
  e.ename, 
  d.deptno 
FROM emp e 
JOIN dept d 
ON e.deptno = d.deptno;

** 左外连接**

左外连接：

join

操作符左边表中符合

where

子句的所有记录将会被返回。

SELECT 
  e.empno, 
  e.ename, 
  d.deptno 
FROM emp e 
LEFT JOIN dept d 
ON e.deptno = d.deptno;

** 右外连接**

右外连接：

join

操作符右边表中符合

where

子句的所有记录将会被返回。

SELECT 
  e.empno, 
  e.ename, 
  d.deptno 
FROM emp e 
RIGHT JOIN dept d 
ON e.deptno = d.deptno;

** 满外连接**

满外连接：将会返回所有表中符合

where

语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用

null

值替代。

SELECT 
  e.empno, 
  e.ename, 
  d.deptno 
FROM emp e 
FULL JOIN dept d 
ON e.deptno = d.deptno;

** 多表连接**

注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。

1. 创建位置表

CREATE TABLE IF NOT EXISTS location (
  loc INT,      -- 部门位置id
  loc_name STRING  -- 部门位置
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

2. 导入数据

LOAD DATA LOCAL INPATH '/opt/module/hive/datas/location.txt' INTO TABLE location;

3. 多表连接查询

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 的输出和表 l 进行连接操作。

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

** 笛卡尔集**

1. 笛卡尔集会在下面条件下产生

1. 省略连接条件
2. 连接条件无效
3. 所有表中的所有行互相连接

2. 案例实操

SELECT 
  empno, 
  dname 
FROM emp, dept;

** 联合（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 的结果，上下所对应列的类型必须一致

2. 案例实操

将员工表 30 部门的员工信息和 40 部门的员工信息，利用

union

进行拼接显示。

SELECT 
  *
FROM emp
WHERE deptno = 30
UNION
SELECT 
  *
FROM emp
WHERE deptno = 40;