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. 案例实操

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;