Hive 数据分析函数

Hive 数据分析函数：分析函数、窗口函数、增强 Group 三类，及用于辅助表达的 over 从句。

产生背景

常规 SQL 语句中，明细数据和聚合后的数据不能同时出现在一张表中，而此类需求又常见。

函数分类

分析函数（不支持与 window 子句联用，即 ROWS BETWEEN）

1、NTILE：序列分析函数，用于数据分片排序，对数据分片并返回当前分片值。(即对数据集分块，如第 1 块，第 2 块等)，不均匀的时候，依次增加前边分片序列的数量。
2、ROW_NUMBER：序列分析函数，用于排序，按照顺序，不关心是否有相等情况，从 1 开始逐条给数据一个加 1 后的序列号。如 1，2，3，4…
3、RANK：序列分析函数，用于排序，按照顺序，关心相等情况，如遇到相等情况，名次会留下空位。如 1,2,2,4,4,6…
3、DENSE_RANK：序列分析函数，用于排序，关心相等情况，如遇到相等情况，名次不会留下空位。如 1,2,2,3,3,4…
4、CUME_DIST：累计计算函数，计算公式为“小于等于当前值的行数/分组内总行数",用于计算数据分布等场景。
5、PERCENT_RANK：累计计算函数，计算公式为” 分组内当前行的 RANK 值-1/分组内总行数-1",用于计算处理进度等场景。

窗口函数

LAG:函数 LAG(col,n,DEFAULT)用于统计窗口内往上第 n 行值。
第一个参数为列名，第二个参数为往上第 n 行（可选，默认为 1）， 第三个参数为默认值（当往上第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL）
LEAD:与 LAG 作用相反，函数形式如 LEAD(col,n,DEFAULT) 用于统计窗口内往下第 n 行值。第一个参数为列名，第二个参数为往下第 n 行(可选，默认为 1)，第三个参数为默认值(当往下第 n 行为 NULL 时候， 取默认值，如不指定，则为 NULL)
FIRST_VALUE: 取分组内排序后，截止到当前行，第一个值
LAST_VALUE:与 FIRST_VALUE 相反，取分组内排序后，截止到当前行，最后一个值

增强 GroupBy

GROUPING SETS：根据不同的维度组合进行聚合，等价于将不同维度的 GROUP BY 结果集进行 UNION ALL
GROUPING__ID：表示结果属于哪一个分组集合，属于虚字段
CUBE：根据 GROUP BY 的维度的所有组合进行聚合。
ROLLUP：为 CUBE 的子集，以最左侧的维度为主，从该维度进行层级聚合。

over()从句：指定分析窗口函数的细化落围规则

与标准的聚合函数 COUNT、SUM、MIN、MAX、AVG 联用，如 sum(…) over(…)
与区配的分析窗口联用，如 ROW_NUMBER() over(…)
使用 PARTITION BY 语句，使用一个或者多个原始数据类型的列
使用 PARTITION BY 与 ORDER BY 语句，使用一个或者多个数据类型的分区或者排序列
使用窗口规范，窗口规范支持以下格式：
ROWS BETWEEN:即为 window 子句或称窗口子句，属于物理截取， 即物理窗口，从行数上控制截取数据的大小多少。
RANGE BETWEEN: 即为 window 子句或称窗口子句，属于逻辑截取， 即逻辑窗口，从列值上控制窗口的大小多少。
PRECEDING：window 子句之往前
FOLLOWING：window 子句之往后
CURRENT ROW：window 子句之当前行
UNBOUNDED：window 子句之起点，UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDED FOLLOWING：表示到后面的终点。
注意：order by 子句后边如果没有跟着多大窗口，则默认为 range between unbounded preceding and current row