Hive SQL 分组与毗连操纵详解

目次
分组
Group By语句
1. 案例实操
 Having语句
1. having 与 where 差别点
2. 案例实操
 Join语句
 等值Join
1. 案例实操
 表的别名
1. 长处
2. 案例实操
 内毗连
 左外毗连
 右外毗连
 满外毗连
 多表毗连
1. 创建位置表
2. 导入数据
3. 多表毗连查询
 笛卡尔集
1. 笛卡尔集会合会在下面条件下产生
2. 案例实操
 连合（union & union all）
1. union & union all 上下拼接
2. 案例实操

---

分组

Group By语句

Group By 语句通常会和聚合函数一起使用，按照一个大概多个列对效果举行分组，然后对每个组实行聚合操纵。
1. 案例实操

• 盘算 emp 表每个部门的均匀工资
  
  1. SELECT
  2.   t.deptno,
  3.   AVG(t.sal) avg_sal
  4. FROM emp t
  5. GROUP BY t.deptno;

  复制代码
• 盘算 emp 每个部门中每个岗位的最高薪水
  
  1. SELECT
  2.   t.deptno,
  3.   t.job,
  4.   MAX(t.sal) max_sal
  5. FROM emp t
  6. GROUP BY t.deptno, t.job;

  复制代码

 Having语句

1. having 与 where 差别点

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

2. 案例实操

• 求每个部门的均匀薪水大于2000的部门
    ① 求每个部门的均匀工资
  
  1. SELECT
  2.   deptno,
  3.   AVG(sal)
  4. FROM emp
  5. GROUP BY deptno;

  复制代码
  ② 求每个部门的均匀薪水大于2000的部门
  
  1. SELECT
  2.   deptno,
  3.   AVG(sal) avg_sal
  4. FROM emp
  5. GROUP BY deptno
  6. HAVING avg_sal > 2000;

  复制代码

 Join语句

 等值Join

Hive支持通常的SQL join 语句，但是只支持等值毗连，不支持非等值毗连。
1. 案例实操

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

  复制代码

 表的别名

1. 长处

• 使用别名可以简化查询。
  
• 区分字段的泉源。
  

2. 案例实操

归并员工表和部门表。

1. SELECT
2.   e.*,
3.   d.*
4. FROM emp e
5. JOIN dept d
6. ON e.deptno = d.deptno;

复制代码

 内毗连

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

1. SELECT
2.   e.empno,
3.   e.ename,
4.   d.deptno
5. FROM emp e
6. JOIN dept d
7. ON e.deptno = d.deptno;

复制代码

 左外毗连

左外毗连：join 操纵符左边表中符合 where 子句的全部记录将会被返回。

1. SELECT
2.   e.empno,
3.   e.ename,
4.   d.deptno
5. FROM emp e
6. LEFT JOIN dept d
7. ON e.deptno = d.deptno;

复制代码

 右外毗连

右外毗连：join 操纵符右边表中符合 where 子句的全部记录将会被返回。

1. SELECT
2.   e.empno,
3.   e.ename,
4.   d.deptno
5. FROM emp e
6. RIGHT JOIN dept d
7. ON e.deptno = d.deptno;

复制代码

 满外毗连

满外毗连：将会返回全部表中符合 where 语句条件的全部记录。假如任一表的指定字段没有符合条件的值的话，那么就使用 null 值更换。

1. SELECT
2.   e.empno,
3.   e.ename,
4.   d.deptno
5. FROM emp e
6. FULL JOIN dept d
7. ON e.deptno = d.deptno;

复制代码

 多表毗连

注意：毗连 n 个表，至少必要 n-1 个毗连条件。比方：毗连三个表，至少必要两个毗连条件。
1. 创建位置表

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

复制代码

2. 导入数据

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

复制代码

3. 多表毗连查询

1. SELECT
2.   e.ename,
3.   d.dname,
4.   l.loc_name
5. FROM emp e
6. JOIN dept d
7. ON d.deptno = e.deptno
8. JOIN location l
9. ON d.loc = l.loc;

复制代码

大多数环境下，Hive会对每对 join 毗连对象启动一个 MapReduce 任务。本例中会起首启动一个 MapReduce job 对表 e 和表 d 举行毗连操纵，然后会再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 l 举行毗连操纵。
注意：为什么不是表 d 和表 l 先辈行毗连操纵呢？这是由于 Hive 总是按照从左到右的序次实行的。
 笛卡尔集

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

• 省略毗连条件
  
• 毗连条件无效
  
• 全部表中的全部行相互毗连
  

2. 案例实操

1. SELECT
2.   empno,
3.   dname
4. 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 效果时有两个要求：

• 两个 SQL 的效果，列的个数必须雷同
  
• 两个 SQL 的效果，上下所对应列的范例必须划一
  

2. 案例实操

将员工表 30 部门的员工信息和 40 部门的员工信息，使用 union 举行拼接体现。

1. SELECT
2.   *
3. FROM emp
4. WHERE deptno = 30
5. UNION
6. SELECT
7.   *
8. FROM emp
9. WHERE deptno = 40;

复制代码