Hive SQL主要开窗函数用法介绍

爱若晨风

　　在开窗函数出现之前，存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在2003年ISO SQL标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。
　　下面通过一些简单的需求示例介绍主要的开窗函数。
　　本例的数据源：
　　select * from wx_tmp1;

　　需求1：要在源表中，增加两列，全国总的gmv和各城市的gmv占比。
　　select *,sum(gmv) over() as all_gmv,
　　gmv/sum(gmv) over() as gmv_pro
　　from wx_tmp1;

　　这就是开窗函数的妙处。SQL标准允许将所有聚合函数用做开窗函数，只需要在聚合函数后加over()即可。
　　over()可以传入对应的子句来达到不同的效果，下面一一介绍。
　　需求2：要在源表中，增加两列，各区域的gmv及各分组的gmv。
　　select *,
　　sum(gmv) over(partition by area) as area_gmv,
　　sum(gmv) over(partition by group) as group_gmv
　　from wx_tmp1;

　　partition by对表进行分区然后聚合计算每个分区的数据，且同一个select语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。
　　需求3：在源表的基础上，增加一列，按gmv升序排列并计算累计gmv。
　　select *,
　　sum(gmv) over(order by gmv rows between unbounded preceding and current row) as leiji_gmv
　　from wx_tmp1;

　　order by子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。
　　order by子句的语法为：order by 字段名 range|rows between 边界规则1 and 边界规则2。
　　range|rows between 边界规则1 and 边界规则2，这个子句又被称为定位框架。
　　range是按照值的范围进行范围的定义，rows是按照行的范围进行范围的定义，边界范围可取值：
　　current row：当前行
　　n preceding：前N行，比如 2 preceding
　　unbounded preceding：一直到第一条记录
　　n following：后N行
　　unbounded following：一直到最后一条记录
　　这个需求中，计算累计gmv，即计算第一条记录到当条记录的gmv之和。
　　rows between unbounded preceding and current row，是最常用的定位框架，可以省略。
　　因此可以简写为以下：
　　select *,
　　sum(gmv) over(order by gmv) as leiji_gmv
　　from wx_tmp1;
　　需求4：在源表中，增加一列，计算gmv的分组排名。
　　select *,
　　count(city) over(partition by group order by gmv desc) as ranks
　　from wx_tmp1;

　　分组排名有另外三个开窗函数：
　　select *,count(city) over(partition by group order by gmv desc) as ranks1,
　　rank() over(partition by group order by gmv desc) as ranks2,
　　dense_rank() over(partition by group order by gmv desc) as ranks3,
　　row_number() over(partition by group order by gmv desc) as ranks4
　　from wx_tmp1

　　通过结果可以看出几种排序的差异。ranks2和ranks3的排序在本例中没有体现：
　　ranks2：如果下面还有一个城市的话，排名是11；ranks3：如果下面还有一个城市的话，排名是10。
　　需求5：在源表中增加两列，取出每组gmv最大的城市，和最小的城市。
　　select *,first_value(city) over(partition by group order by gmv desc) as first_city,
　　first_value(city) over(partition by group order by gmv) last_city
　　from wx_tmp1

　　first_value:按分组排序后，取范围内第一个值。相应的还有last_value,按分组排序后，取范围内，最后一个值。
　　如果这个需求中，按以下写法的话：
　　select *,first_value(city) over(partition by group order by gmv desc) as first_city,
　　last_value(city) over(partition by group order by gmv desc) last_city
　　from wx_tmp1
　　会发现last_city的结果不是我们想要的。是因为使用了默认定义框架：第一行到当前行的最后一个值。

　　要纠正这个问题，需要使用定位框架，因为相对麻烦，建议用first_value()替代。
　　需求6：在源数据中增加两列，第一列取gmv分组排名在当前城市上面1位的城市，第二列取gmv分组排名在当前城市下面1位的城市。
　　select *,lag(city,1,null) over(partition by group order by gmv desc) as up_city,
　　lead(city,1,null) over(partition by group order by gmv desc) down_city
　　from wx_tmp1

　　如果要取排名前/后两位的城市，调整lag和lead第二个参数即可。第三个参数为默认值，可以省略，省略的话，默认取不到时是NULL，如果不省略的话，取不到时则取指定默认值。
　　还有一个开窗函数，如下用法：
　　select *,ntile(2) over(partition by group order by gmv desc) rn from wx_tmp1
　　即将源表按group分组，并按gmv降序排列后，将每组平均分为2部分。

　　还有其他聚合函数用法跟sum()一致，就不一一介绍了。
　　转自：https://baijiahao.baidu.com/s?id=1592467638440407962&wfr=spider&for=pc