hive 窗口函数_HIVE窗口函数实战

在上一篇HIVE 窗口函数详解 中，我们介绍了hive窗口函数的定义和语法，本篇中我们根据例子来进行实战。

数据准备

首先我们来定义测试表结构，建立一张student_score学生分数表，主要三个字段，学生，班级，成绩，如下图所示

接下来准备数据，为了方便，我们直接采用插入的方式，如下图所示

最后我们的数据如下图所示：

---

排序窗口

需求：求每个班级成绩排名前三的学生。

row_number()函数，唯一排序，不会生成排序相同的用户

示例和结果如下：

可以看到，一班的学生三和学生四同样为90分的学生，会按照不同的排名进行排名。

rank()函数，不唯一排序，相同分数排名会相同，但后续排名会跳过。

示例和结果如下图：

可以看到，一班的学生三和学生四，分数都为90分，排名都同样为1，同时，一班的学生二为40分，排名为3，直接跳过了排名为2，所以一般没有排名为2的学生。

dense_rank()函数，不唯一排序，排名相同的分数排序会相同，和rank()不同的时，后续排名不会跳过。

示例和结果如下图

可以看到，一班的学生三和学生四，分数都为90分，排名并列第一，而学生二分数低于他们，排名为第二。

聚合窗口

聚合窗口能使用的有count，sum，avg，max，min。

这里，我们以sum举例，其他函数请举一反三

需求： 求每个班级的学生中该学生和他上一个学号及下一个学号的学习总分。

其他类似需求，截止至当前月的销售额，最近三个月的销售额这种。

示例和结果如下图

可以看到，学生一没有学号比他低的，只有学号比他高的学生二，故总分为10+40=50分

学生二有学号比他低的学生一和学号比他高的学生三，故总分为10+40+90=140

学生四有学号比他低的学生三，学生五学号虽然比学生四高，但是属于不同班级，故不记录，所以最后总分为90+90=180

其他窗口

lag(col, n, DEFAULT) 用于统计窗口内向上第n行的值

lead请参考lag

需求：分班级，求上一个学号的学生成绩。

示例和结果如下图：

可以看到学生一上一个学号没有，所以上一个分数为null

学生二上一个学生为学生一，所以上一个分数显示10

cume_dist

计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：(小于等于当前值的行数) / (分区内总行数)

比如，统计小于等于当前薪水的人数，所占总人数的比例

需求：计算班级内小于等于自己的比例

示例和结果如下图：

如图所示，一班一共有4个人

学生一分数最低，所以为1/4=0.25

学生二的分数为40，<=40的有学生一和学生二，所以为2/4=0.25

学生三和学生四的分数为90，最高，所以为4/4=1

ntile

作用：将分区中的数据按照顺序划分为N片，返回当前片的值。

注1：如果切片分布不均匀，默认增加第一个切片的分布注2：ntile不支持ROWS BETWEEN

需求：分班级,将数据分为2份，返回份数

示例和需求如下：

可以看到，一班一共有4个人，按照成绩，10，40属于低分区，故归属于第一份，90，90属于高分区，故归属于第二份。

percent_rank

计算给定行的百分比排名。分组内当前行的RANK值-1/分组内总行数-1，可以用来计算超过了百分之多少的人。

需求，计算成绩在班级内的百分位

示例和结果如下图

可以看到，二班内的学生五成绩最低，rank为1，(1-1)/(4-1)=0

学生六和学生七同为50分，rank为2，结果为(2-1)/(4-1)=0.33

学生八最高，rank为4，结果未(4-1)/(4-1)=1