【Hive 进阶】窗口函数

 

 

作为一名数据小哥，在写SQL的漫漫路上，窗口函数犹如一把披荆斩棘的利剑，帮助作者解决了很多繁琐复杂的需求，在此对窗口函数表示感谢。

 

本文在介绍了窗口函数的同时，着重介绍Hive窗口函数的使用，希望读者在看完本篇文章之后，对窗口函数的使用能够有所掌握。

 

值得注意的是本文中的例子使用的是HQL（Hive SQL），本文需要一定的SQL基础，如果想了解基础SQL，请移步数据分析师之快速掌握SQL基础 。

 

两个问题

 

 

对于数据工作者来说，窗口函数或多或少都使用过，但是可能没有系统的去总结它的用法。

 

如果读者对于窗口函数有一点了解的话，不妨先看看针对下表的两个问题，如何使用SQL去解决；如果读者对于窗口函数一点都不了解，那请您直接跳过这一部分，直接从什么是窗口函数开始阅读。

 

 

针对上面一张学生成绩表(class)，有year-学年，class-课程，student-学生，score-分数这四个字段，请看问题：

 

问题1：每年每门学科排名第一的学生是？

问题2：每年总成绩都有所提升的学生是？

 

对于问题1来说比较简单，既可以使用聚合函数来统计，也可以使用窗口函数来统计，其中窗口函数给了两种解法：

 

•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  

--使用聚合函数select a.year,a.class,b.studentfrom(select year,class,max(score) as max_scorefrom classgroup by year,class) a join class b on a.year = b.year and a.class = b.class and a.max_score = b.scoreorder by a.year

 

执行结果如下，如果有相同成绩的话都会保留。

 

 

•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  

--使用窗口函数maxselect a.year,a.class,a.studentfrom(select year,class,score,student,max(score) over (partition by year,class) as max_score --增加一列为聚合后的最高分    from `class`) a where a.score = max_score  --保留与最高分相同的记录数 

 

 

执行结果如下，同样的如果有相同记录也会保留下来。

 

 

•  
•  
•  
•  
•  
•  

--使用窗口函数first_valueselect distinct year,class,first_value(student) over (partition by year,class order by score desc) as studentfrom class

 

执行结果，需要注意的是如果有相同成绩，只会取一条记录。

 

 

对比两种写法可以发现：

• 使用窗口函数的SQL代码量少

• 避免了与原表的join

 

对于问题2，是一个相对复杂但是比较常见的需求，无法只使用聚合函数来统计，只能配合窗口函数来统计。

 

•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  

select studentfrom(  select year,student  ,if((sum_score - lag(sum_score,1,0)   over   (    partition by student     order by year  )) > 0,1,0) as flag  ,(sum_score - lag(sum_score,1,0)   over   (partition by student   order by year  )) as flag1  --按照student进行分区并进行year正序排序  --，找到每个学生的上一条学年总成绩  --，并与当年成绩相减，如果小于  --，则将flag值置为1，否则置为0  from  (    select year,student    ,sum(score) as sum_score     --按照学年和学生进行成绩汇总    from class    group by year,student  ) a ) b group by studenthaving avg(flag) = 1 --平均值为1则代表是每年都有增长

 

执行结果：

 

 

 

通过上面两个问题，可以对窗口函数的特征做一个简单的小结：

 

• 聚合函数可以作为窗口函数使用

• 具有计算和取值的功能

• 不改变记录数

 

什么是窗口函数

 

相信看了上面的两个问题后，对窗口函数的使用有一个大概的了解。下面从理论方面来详细了解下窗口函数。

 

理论

 

窗口函数也称为OLAP（Online Analytical Processing）函数，是对一组值进行操作，不需要使用Group by子句对数据进行分组，还能在同一行返回原来行的列和使用聚合函数得到的聚合列。

 

那为什么叫窗口函数呢？因为窗口函数将表以窗口为单位进行分割，并在其中进行各种分析操作，为了让大家快速形成直观印象，才起了这样一个容易理解的名称。

 

 

 

SQL语法

 

•  
•  
•  
•  
•  
•  
•  

<窗口函数>() OVER (  [PARTITION BY <列清单>]  [ORDER BY <排序用清单列>] [ASC/DESC]  (ROWS | RANGE) <范围条件>)

 

如上代码所示，窗口函数的语法分为四个部分：

 

函数子句：指明具体操作，如sum-求和，first_value-取第一个值；

partition by子句：指明分区字段，如果没有，则将所有数据作为一个分区；

order by子句：指明了每个分区排序的字段和方式,也是可选的，没有就是按照表中的顺序；

窗口子句：指明相对当前记录的计算范围，可以向上（preceding），可以向下（following）,也可以使用between指明，上下边界的值，没有的话默认为当前分区。有些场景比较特殊，后文会讲到这种场景。

 

窗口函数分类

 

下面的思维导图基本包含了Hive所有的窗口函数，按照窗口函数的功能分为：计算、取值、排序、序列四种，前三种的使用场景比较常见，容易理解，最后一种(序列)的使用场景比较少。

 

 

窗口函数使用场景

 

 

介绍了这么多，那窗口函数到底可以帮我们做什么呢？

 

结合实际场景看看怎么用窗口函数来解决问题。下面针对不同的使用场景，将窗口函数的使用呈现给大家。所有例子的数据均来自下图这张表。

 

 

用于辅助计算

 

主要的用法是在原有表的基础上，增加一列聚合后的值，辅以后续的计算。

例如：统计出不同产品类型售价最高的产品。

具体代码如下：

•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  

--使用窗口函数maxselect a.product_type,a.product_namefrom(  select product_name,product_type,sale_price  ,max(sale_price) over   (    partition by product_type  ) as max_sale_price   --增加一列为聚合后的最高售价  from product) a where a.sale_price = a.max_sale_price; --保留与最高售价相同的记录数

 

执行结果：

 

 

几乎所有的窗口函数都可以用于辅助计算。

 

累积计算

 

标准聚合函数作为窗口函数配合order by使用，可以实现累积计算。

 

例如：sum窗口函数配合order by，可以实现累积和。

具体代码如下：

•  
•  
•  
•  
•  
•  
•  

SELECT product_id,product_name  ,product_type,sale_price  ,SUM(sale_price) OVER   (    ORDER BY product_id  ) AS current_sumFROM product;

 

执行结果：

 

 

相应的AVG窗口函数配合order by，可以实现累积平均，max可以实现累积最大值，min可以实现累积最小值，count则可以实现累积计数。注意，只有计算类的窗口函数可以实现累积计算。

 

这里提出一个问题，为什么增加了order by就可以实现累积计算呢？读者可以停顿思考一下！

 

答案马上揭晓：标准聚合函数作为窗口函数使用的时候，在指明order by的情况下，如果没有Window子句，则Window子句默认为：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(上边界不限制，下边界到当前行)。

 

移动计算

 

移动计算是在分区和排序的基础上，对计算范围进一步做出限定。

 

例如：按照产品ID排序，将最近3条的销售价格进行汇总平均。

具体代码如下：

•  
•  
•  
•  
•  
•  
•  
•  
•  

SELECT product_id,product_name   ,sale_price   ,AVG(sale_price)    over    (      ORDER BY product_id      rows 2 preceding    ) AS moving_avgFROM product;

 

rows 2 preceding的意思就是“截止到之前2行”。也就是将作为汇总对象的记录限定为如下的最靠近的3行。

 

执行结果如下：

 

使用关键字FOLLOWING(“之后”）替换PRECEDING，就可以指定截止到之后~行。

 

取任一字段值

 

取值的窗口函数有：first_value/last_value、lag/lead，其中first_value和lag在开篇的例子中已经使用到了，这里就不举例说明了。只细化说明下他们的语法。

 

first_value(字段名)-取出分区中的第一条记录的任意一个字段的值，可以排序也可以不排序，此处也可以进一步指明Window子句。

 

lag(字段名,N,默认值)-取出当前行之上的第N条记录的任意一个字段的值，这里的N和默认值都是可选的，默认N为1，默认值为null。

 

排序

 

排序对应的四个窗口函数为：rank、dense_rank、row_number、ntitle

 

rank：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

e.g. 有三条记录排在第1位时：1位、1位、1位、4位......

dense_rank：计算排序时，即使存在相同位次的记录，也不会跳过之后的位次。

e.g. 有三条记录排在第1位时：1位、1位、1位、2位......

row_number：赋予唯一的连续位次。

e.g. 有三条记录排在第1位时：1位、2位、3位、4位...

ntitle：用于将分组数据按照顺序切分成n片，返回当前切片值

e.g. 对于一组数字（1，2，3，4，5，6），ntile(2)切片后为（1，1，1，2，2，2）

 

1）统计所有产品的售价排名

具体代码如下：

•  
•  
•  
•  
•  
•  
•  

SELECT product_name,product_type   ,sale_price,   RANK () OVER    (     ORDER BY sale_price    ) AS rankingFROM product;

 

执行结果如下：

 

 

2）统计各产品类型下各产品的售价排名

具体代码如下：

•  
•  
•  
•  
•  
•  
•  
•  

SELECT product_name,product_type   ,sale_price,   RANK () OVER    (     PARTITION BY product_type      ORDER BY sale_price    ) AS rankingFROM product;

 

执行结果如下：

 

 

对比一下dense_rank、row_number、ntile

 

具体代码如下：

•  
•  
•  
•  
•  
•  
•  
•  

SELECT product_name,product_type,sale_price,    RANK () OVER (ORDER BY sale_price) AS ranking,    DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking,    ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num,    ntile(3) OVER (ORDER BY sale_price) as nt1,    ntile(30) OVER (ORDER BY sale_price) as nt2     --切片大于总记录数FROM product;

 

执行结果如下：

 

 

从结果可以发现，当ntile(30)中的切片大于了总记录数时，切片的值为记录的序号。

 

序列

 

序列中的两个窗口函数cume_dist和percent_rank，通过实例来看看它们是怎么使用的。

 

1）统计小于等于当前售价的产品数，所占总产品数的比例

具体代码如下：

•  
•  
•  
•  
•  
•  
•  
•  

SELECT product_type,product_name,sale_price,CUME_DIST() OVER(ORDER BY sale_price) AS rn1,CUME_DIST() OVER(  PARTITION BY product_type   ORDER BY sale_price) AS rn2 FROM product;

 

执行结果如下：

 

 

rn1: 没有partition,所有数据均为1组，总行数为8，

     第一行：小于等于100的行数为1，因此，1/8=0.125

     第二行：小于等于500的行数为3，因此，3/8=0.375

rn2: 按照产品类型分组，product_type=厨房用品的行数为4,

     第三行：小于等于500的行数为1，因此，1/4=0.25

 

2）统计每个产品的百分比排序

当前行的RANK值-1/分组内总行数-1

具体代码如下：

•  
•  
•  
•  
•  
•  
•  
•  

SELECT product_type,product_name,sale_price,percent_rank() OVER (ORDER BY sale_price) AS rn1,percent_rank() OVER (  PARTITION BY product_type   ORDER BY sale_price)  AS rn2 FROM product;

 

执行结果如下：

 

 

rn1: 没有partition,所有数据均为1组，总行数为8，

第一行：排序为1，因此，（1-1）/（8-1）= 0

第二行：排序为2，因此，（2-1）/（8-1）= 0.14

rn2: 按照产品类型分组，product_type=厨房用品的行数为4,

第三行：排序为1，因此，（1-1）/（4-1）= 0

第四行：排序为1，因此，（2-1）/（4-1）= 0.33

 

总结

 

以上介绍了Hive中窗口函数的几乎所有的使用场景，每种函数的用法也配合代码进行讲解，相信大家看了本文后，在实际数据工作中对于窗口函数的使用肯定会得心应手。

 

-end-

参考：