HiveQL常用查询语句where、group by、having、join子句记录

计算emp表中每个部门每个岗位的最高薪水

select deptno,job,max(sal) max_sal from emp group by deptno,job;

+---------+------------+----------+--+
| deptno  |    job     | max_sal  |
+---------+------------+----------+--+
| 10      | CLERK      | 1300.0   |
| 10      | MANAGER    | 2450.0   |
| 10      | PRESIDENT  | 5000.0   |
| 20      | ANALYST    | 3000.0   |
| 20      | CLERK      | 1100.0   |
| 20      | MANAGER    | 2975.0   |
| 30      | CLERK      | 950.0    |
| 30      | MANAGER    | 2850.0   |
| 30      | SALESMAN   | 1600.0   |
+---------+------------+----------+--+

ps：groupby，对多行聚合后输出一行的结果；若没有max（col）、collect_set(col)、concat_ws('~'，col)等行转列，即多行变一行的sql条件，是没法聚合的会报错。

3. having子句（只用于group by之后）

having只用于group by子句后，给分组字段添加条件；

一般是跟聚合函数，例如select count(*) from table group by a having count(*)>10这种，having可以接别名

求每个部门的平均薪水大于2000的部门

select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal>2000;


+---------+---------------------+--+
| deptno  |       avg_sal       |
+---------+---------------------+--+
| 10      | 2916.6666666666665  |
| 20      | 2175.0              |
+---------+---------------------+--+

4. join子句（A join B on A.key = B.key where X.key is null）

（1）A join B on A.字段 = B.字段，连接两张表

例如两张hive表，员工表emp和部门表dept，通过deptno来串联

要求：根据员工表和部门表中的部门编号，查询员工编号、员工名称和部门名称

0: jdbc:hive2://hadoop100:10000> select e.empno,e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

+----------+----------+-------------+--+
| e.empno  | e.ename  |   d.dname   |
+----------+----------+-------------+--+
| 7369     | SMITH    | RESEARCH    |
| 7499     | ALLEN    | SALES       |
| 7521     | WARD     | SALES       |
| 7566     | JONES    | RESEARCH    |
| 7654     | MARTIN   | SALES       |
| 7698     | BLAKE    | SALES       |
| 7782     | CLARK    | ACCOUNTING  |
| 7788     | SCOTT    | RESEARCH    |
| 7839     | KING     | ACCOUNTING  |
| 7844     | TURNER   | SALES       |
| 7876     | ADAMS    | RESEARCH    |
| 7900     | JAMES    | SALES       |
| 7902     | FORD     | RESEARCH    |
| 7934     | MILLER   | ACCOUNTING  |
+----------+----------+-------------+--+

（2）内连接（求交集）

select * from A a
join B b
on a.key = b.key;

（3）左外连接（求左表独有）

select * from A a
left join B b
on a.key = b.key
where b.key is null;

（4）右外连接（求右表独有）

select * from A a
left join B b
on a.key = b.key
where a.key is null;

（5）全外连接（求并集）

select * from A a
full join B b
on a.key = b.key;

（6）去重全外连接（求并集并且去除交集部分）

select * from A a
full join B b
on a.key = b.key
where a.key is null or b.key is null;

（7）多表连接

select a.name, b.name, c.name
from A a
join B b
on a.key1 = b.key1
join C c
on a.key2 = c.key2;

ps：hive从左到右顺序执行，多表连接join顺序也是按照查询字段从上到下。

（8）笛卡尔积

0: jdbc:hive2://hadoop100:10000> select empno,dname from emp, dept;