1.什么是窗口函数
sql中有一类函数叫做聚合函数，例如sum()、avg()、max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据，又要显示聚集后的数据，这时我们便引入了窗口函数。
窗口函数也称为OLAP(Online Analytical Processing)函数，是对一组值进行操作，不需要使用Group by子句对数据进行分组，还能在同一行返回原来行的列和使用聚合函数得到的聚合列。
那为什么叫窗口函数呢？因为窗口函数将表以窗口为单位进行分割，并在其中进行各种分析操作，为了让大家快速形成直观印象，才起了这样一个容易理解的名称。
2.sql语法
() OVER ( [PARTITION BY ] [ORDER BY ] [ASC/DESC] (ROWS | RANGE) )如上代码所示，窗口函数的语法分为四个部分：
函数子句：指明具体操作，如sum-求和，first_value-取第一个值；
partition by子句：指明分区字段，如果没有，则将所有数据作为一个分区；
order by子句：指明了每个分区排序的字段和方式,也是可选的，没有就是按照表中的顺序；
窗口子句：指明相对当前记录的计算范围，可以向上(preceding)，可以向下(following),也可以使用between指明，上下边界的值，没有的话默认为当前分区。有些场景比较特殊，后文会讲到这种场景。
3.窗口函数分类
下面的思维导图基本包含了Hive所有的窗口函数，按照窗口函数的功能分为：计算、取值、排序、序列四种，前三种的使用场景比较常见，容易理解，最后一种(序列)的使用场景比较少。
4.窗口函数使用场景
结合实际场景看看怎么用窗口函数来解决问题。下面针对不同的使用场景，将窗口函数的使用呈现给大家。所有例子的数据均来自下图这张表。
<1>用于辅助计算
主要的用法是在原有表的基础上，增加一列聚合后的值，辅以后续的计算。
例如：统计出不同产品类型售价最高的产品。
具体代码如下：
--使用窗口函数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; --保留与最高售价相同的记录数执行结果：
<2>累积计算
标准聚合函数作为窗口函数配合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(上边界不限制，下边界到当前行)。
<3>移动计算
移动计算是在分区和排序的基础上，对计算范围进一步做出限定。
例如：按照产品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，就可以指定截止到之后~行。
<4>取任一字段值
取值的窗口函数有：first_value/last_value、lag/lead，其中first_value和lag在开篇的例子中已经使用到了，这里就不举例说明了。只细化说明下他们的语法。
first_value(字段名)-取出分区中的第一条记录的任意一个字段的值，可以排序也可以不排序，此处也可以进一步指明Window子句。
lag(字段名,N,默认值)-取出当前行之上的第N条记录的任意一个字段的值，这里的N和默认值都是可选的，默认N为1，默认值为null。
<5>排序
排序对应的四个窗口函数为：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)中的切片大于了总记录数时，切片的值为记录的序号。
<6>序列
序列中的两个窗口函数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窗口函数的几种常用场景，希望大家看完以后可以对窗口函数有更进一步的了解。
