目录
分组
Group By语句
Group By
语句通常会和聚合函数一起使用,按照一个或者多个列对结果进行分组,然后对每个组执行聚合操作。
1. 案例实操
-
计算
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;
Having语句
1. having
与 where
不同点
where
后面不能写分组聚合函数,而having
后面可以使用分组聚合函数。having
只用于group by
分组统计语句。
2. 案例实操
-
求每个部门的平均薪水大于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. 案例实操
- 根据员工表和部门表中的部门编号相等,查询员工编号、员工名称和部门名称
SELECT e.empno, e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno;
表的别名
1. 好处
- 使用别名可以简化查询。
- 区分字段的来源。
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. 笛卡尔集会在下面条件下产生
- 省略连接条件
- 连接条件无效
- 所有表中的所有行互相连接
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 结果时有两个要求:
- 两个 SQL 的结果,列的个数必须相同
- 两个 SQL 的结果,上下所对应列的类型必须一致
2. 案例实操
将员工表 30 部门的员工信息和 40 部门的员工信息,利用 union
进行拼接显示。
SELECT
*
FROM emp
WHERE deptno = 30
UNION
SELECT
*
FROM emp
WHERE deptno = 40;