HIVE jion 语句

只支持等值连接，不支持非等值连接。

内连接

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

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

右外连接

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个连接条件。例如：连接三个表，至少需要两个连接条件。

连接词不支持 or

排序

Order By：全局排序，只有一个Reducer

1．使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

eg:select * from emp order by sal;

每个MapReduce内部排序（Sort By）

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。

Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。并行计算局部排序

分区排序（Distribute By）

在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute

by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

select * from emp distribute by deptno sort by empno desc;

Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

Cluster By

当distribute by和sorts by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

分桶查询

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储路径；分桶针对的是数据文件。

空字段赋值

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。

 select comm,nvl(comm, -1) from emp;

CASE WHEN

求出不同部门男女各多少人。结果如下：

A     2      1

B     1      2

select

dept_id,

  sum(case sex when '男' then 1 else 0 end) male_count,

  sum(case sex when '女' then 1 else 0 end) female_count

from

  emp_sex

group by

  dept_id;

窗口函数（开窗函数）

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。空着就是全表

RANK函数

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

这三个函数常常和开窗函数结合在一起使用

select

*,rank()over(partitionbysubjectorderbyscoredesc)rank,

dense_rank()over(partitionbysubjectorderbyscoredesc)dense_rank,

row_number()over(partitionbysubjectorderbyscoredesc) row_number

fromscore;