数仓｜函数 Hive开窗函数实战

欢迎大家来到IT世界,在知识的湖畔探索吧!

基本语法

欢迎大家来到IT世界,在知识的湖畔探索吧!

可以是下面的函数：

• Aggregate Functions: 聚合函数,比如：sum(…)max(…)min(…)avg(…)等.
• Sort Functions: 数据排序函数, 比如 ：rank(…)row_number(…)等.
• Analytics Functions: 统计和比较函数, 比如：lead(…)lag(…)first_value(…)等.

数据准备

窗口聚合函数

1.查询姓名、部门编号、工资以及部门人数

2.查询姓名、部门编号、工资以及每个部门的总工资，部门总工资按照降序输出

窗口排序函数

窗口排序函数提供了数据的排序信息，比如行号和排名。在一个分组的内部将行号或者排名作为数据的一部分进行返回，最常用的排序函数主要包括：

• row_number

根据具体的分组和排序，为每行数据生成一个起始值等于1的唯一序列数

• rank

对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。比如查找具体条件的topN行

• dense_rank

dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。

• percent_rank

排名计算公式为：(current rank – 1)/(total number of rows – 1)

• ntile

将一个有序的数据集划分为多个桶(bucket)，并为每行分配一个适当的桶数。它可用于将数据划分为相等的小切片，为每一行分配该小切片的数字序号。

1.查询姓名、部门编号、工资、排名编号(按工资的多少排名)

2.查询每个部门工资最高的两个人的信息(姓名、部门、薪水)

3.查询每个部门的员工工资排名信息

4.使用rank函数进行排名

5.使用dense_rank进行排名

6.使用percent_rank()进行排名

7.使用ntile进行数据分片排名

尖叫提示：从 Hive v2.1.0开始, 支持在OVER语句里使用聚集函数，比如：

窗口分析函数

常用的分析函数主要包括：

• cume_dist

如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。比如，统计小于等于当前工资的人数占总人数的比例 ，用于累计统计.

• lead(value_expr[,offset[,default]])

用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL.

• lag(value_expr[,offset[,default]])

与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL.

• first_value

取分组内排序后，截止到当前行，第一个值

• last_value

取分组内排序后，截止到当前行，最后一个值

1.统计小于等于当前工资的人数占总人数的比例

2.统计大于等于当前工资的人数占总人数的比例

3.按照部门统计小于等于当前工资的人数占部门总人数的比例

4.按部门分组，统计每个部门员工的工资以及大于等于该员工工资的下一个员工的工资

5.按部门分组，统计每个部门员工的工资以及小于等于该员工工资的上一个员工的工资

6.按部门分组，统计每个部门员工工资以及该部门最低的员工工资

7.按部门分组，统计每个部门员工工资以及该部门最高的员工工资

注意: last_value默认的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示当前行永远是最后一个值，需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

为默认值，即当指定了ORDER BY从句，而省略了window从句 ，表示从开始到当前行。

• RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

表示从当前行到最后一行

• RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

表示所有行

• n PRECEDING m FOLLOWING

表示窗口的范围是：[（当前行的行数）- n, （当前行的行数）+m] row.