hive的四种排序以及窗口函数的使用

1.order by
：对数据进行全局排序，因此只有一个Reducer(多个Reducer无法保证全局有序)，然而只有一个Reducer，会导致当输入规模较大时，消耗较长的计算时间

2.sort by
：局部排序，
一般要设置下reduces个数，默认是-1，当个数为1，相当于order by，一般设置为大于1，保证每个reducer的输出有序，并不保证全局有序。
如果运行过慢，可以设置为本地模式：

set hive.exec.mode.local.auto=true;

但是需要设置reduces个数为1或默认值-1，才可以让hive运行速度较快

3.distribute by
:分区但不是等同于分区排序，
distribute by是控制在map端如何拆分数据给reduce端的。hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法。sort by为每个reduce产生一个排序文件。使用在sort by之前

注：Distribute by和sort by的使用场景

1.Map输出的文件大小不均。
2.Reduce输出文件大小不均。
3.小文件过多。
4.文件超大。

4.cluster by
：cluster by=distribute by + sort by（先分区在排序）
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是正序排序，不能指定排序规则为ASC或者DESC。

5.窗口函数

5.1概念：一组特殊函数
扫描多个输入行来计算每个输出值，为每行数据生成一行结果
可以通过窗口函数来实现复杂的计算和聚合
按功能可划分为：序列，聚合，分析
5.2语法
<窗口函数> over(partition by 用于分组的列名 order by 用于排序的列名)

PARTITION BY类似于GROUP BY，未指定则按整个结果集
只有指定ORDER BY子句之后才能进行窗口定义
可同时使用多个窗口函数
过滤窗口函数计算结果必须在外面一层
5.3函数序列
1.ROW_NUMBER()

对所有数值输出不同的序号，序号唯一连续
2.RANK()

对相同数值，输出相同的序号，下一个序号跳过（1,1,3）
3.DENSE_RANK()

对相同数值，输出相同的序号，下一个序号连续（1,1,2）
4.NTILE(n)

将有序的数据集合平均分配到n个桶中, 将桶号分配给每一行，根据桶号，选取前或后 n分之几的数据
5.PERCENT_RANK()

(目前排名- 1)/(总行数- 1)，值相对于一组值的百分比排名
实列代码如下：

-- 窗口函数 序列
SELECT 
name, dept_num, salary, 
ROW_NUMBER() OVER () AS row_num, 
RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS rank, 
DENSE_RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS dense_rank,
PERCENT_RANK() OVER(PARTITION BY dept_num ORDER BY salary) AS percent_rank, 
NTILE(2) OVER(PARTITION BY dept_num ORDER BY salary) AS ntile 
FROM employee_contract 
ORDER BY dept_num, salary;

5.4函数聚合
count:
计数，可以和DISTINCT一起用

SELECT 
COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d 
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 

sum
max/min
avg

实列代码如下：

--求平均，最大，最小
select name,dept_num,salary,
avg(salary)over(partition by dept_num)as avgdept,
min(salary)over(partition by dept_num)as mindept,
max(salary)over(partition by dept_num)as maxdept
from employee_contract;

5.5窗口函分析
sum(col) over() : 分组对col累计求和，over() 中的语法如下
count(col) over() : 分组对col累计，over() 中的语法如下
min(col) over() : 分组对col求最小
max(col) over() : 分组求col的最大值
avg(col) over() : 分组求col列的平均值
first_value(col) over() : 某分区排序后的第一个col值
last_value(col) over() : 某分区排序后的最后一个col值
lag(col,n,DEFAULT) : 统计往前n行的col值，n可选，默认为1，DEFAULT当往上第n行为NULL时候，
取默认值，如不指定，则为NULL
lead(col,n,DEFAULT) : 统计往后n行的col值，n可选，默认为1，DEFAULT当往下第n行为NULL时候
，取默认值，如不指定，则为NULL
ntile(n) : 用于将分组数据按照顺序切分成n片，返回当前切片值。注意：n必须为int类型。

5.6窗口函数 - 窗口子句
行窗口：根据当前行之前或之后的行号确定的窗口

SELECT
name, dept_num AS dept, salary AS sal,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) win2,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) win3,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) win4,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) win5,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND CURRENT ROW) win6,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) win7,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) win8,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) win9,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) win10,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) win11,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS 2 PRECEDING) win12
FROM employee_contract  ORDER BY dept, name;