文章目录
一、窗口函数是什么？
个人理解窗口函数的格式是：
聚合函数(如sum/count/…) / rank() / lag() / lead() / ntile + over(窗口的范围)
以上便是窗口函数使用的格式，接下来会根据几个例子，带你逐渐理解窗口函数
二、over(窗口的范围)的理解
2.1 窗口的范围
窗口函数中最重要的就是理解over()，over()是指对于每一行要分析的数据的窗口的范围，这个范围也就是要填入over()中的内容，可以由以下一些参数指定
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
PARTITION BY 表示分组
ORDER BY 表示排序
这里不理解这些参数没关系，下文会逐步带你使用这些参数，并让你逐步理解over()函数的意义
2.2 窗口函数使用示例
现有如下数据，表示用户当天的购买明细：
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
Hive建表，导入数据代码如下：
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/data/business.txt" into table business;
现有如下需求：
（1）查询在 2017 年 4 月份购买过的顾客及总人数
（2）查询顾客的购买明细及顾客的月购买总额
（3）将每个顾客的 cost 按照日期进行累加
（4）查询每个顾客上次的购买时间
（5）查询前 20%时间的订单信息
2.2.1 查询在 2017 年 4 月份购买过的顾客及总人数
结果如下：4月份一共有两名顾客购买过东西，总人数为2。
这里先从最简单的思路入手，我们先查询4月份购买过的顾客有哪些
select
name
from business where substring(orderdate,1,7) = '2017-04'
group by name;
那么我们怎样在加一列，这一列表示4月份购买过商品的顾客的总人数，仅仅用count(*)是不能完成的，这里就需要用到窗口函数over()，完整代码如下
select
name
count(*) over() as nums
from business where substring(orderdate,1,7) = '2017-04'
group by name;
这里count(*) over()是指对group by后的每一行数据进行开窗，统计窗口中数据的个数，这里over()中没有指定窗口的范围，那么默认窗口的范围就是group by后整个数据的范围，这个窗口范围内的数据个数为2(mart 和 jack)。
看完上面一个例子我想你对窗口函数有了那么一点点理解了吧，如果还没理解，请再看下面几个例子
2.2.2 查询顾客的购买明细及顾客的月购买总额
结果如下图所示
该例子是让我们求的每位顾客的购买明细，同时在后面加上该顾客在当月的购买总额，这个问题在于如果是单纯求月够买总额的话可以对顾客和月份进行分组求和，如果单纯求购买明细，直接查询出相关的字段即可，但二者合在一起就有了冲突，这里还是借助窗口函数来解决，代码如下
select
name,
orderdate,
cost,
sum(cost) over(partition by name,month(orderdate)) as monthallmoney
from business;
这里如何理解 sum(cost) over(partition by name,month(orderdate))？这个字段表示对每一行的cost开窗并对窗口内数据求和，那么对于每一行数据来说窗口的范围就是根据这一行数据的name 和 购买明细中的月份进行分组后的数据范围，然后对这个范围内的数据进行求和，不就知道了该顾客在当前月的购买总额了吗。
2.2.3 将每个顾客的 cost 按照日期进行累加
结果如下图：
通过观察结果我们发现这个例子其实就是在查询每个顾客的购买明细的基础上又多加了cost按照日期的累计，如果要实现这个需求可以在查询购买明细的基础上加一个窗口函数即可，代码如下：
select
name,
orderdate,
cost,
sum(cost) over(PARTITION BY name ORDER BY orderdate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_1
from business;
这里的over()函数内的字段就有点复杂了，不过也好理解，现在我们需要对购买明细的当前行进行开窗，窗口的范围是对name进行分组，在组内对orderdate排序，由于是求日期的累加和，那么需要指定范围是分组排序后从第一行到当前行，rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW就是这个意思，指定窗口范围是分组排序后的第一行到当前行，这样就可以求出每个人按照日期排序后的cost累加和了，仔细想想是不是这个道理？
其实这里的窗口函数可以简写为sum(cost) over(PARTITION BY name ORDER BY orderdate)，当前行的窗口范围默认就是分组排序后从起点到当前行。
以下给出一些表示窗口范围的字段：
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED PRECEDING 表示从前面的起点
UNBOUNDED FOLLOWING 表示到后面的终点
下面的代码加上以上字段，大家可以对窗口函数中每一行数据的开窗范围再深入理解理解
select
name,
orderdate,
cost,
sum(cost) over(PARTITION BY name ORDER BY orderdate) sum_1,
sum(cost) over(PARTITION BY name ORDER BY orderdate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_2, --同sum_1
sum(cost) over() sum_3,--所有行相加
sum(cost) over(partition by name) sum_4,--按 name 分组，组内数据相加
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING) sum_5, --当前行的前一行到后一行的数据相加
sum(cost) over(partition by name order by orderdate rows between CURRENT ROW AND UNBOUNDED FOLLOWING) sum_6 --当前行及后面所有行
from business;
2.2.4 查询每个顾客上次的购买时间
结果如下图所示：
这里需要注意：如果是第一次购买，那么上次购买时间的值为当前购买时间
这里直接看代码吧
select
name,
orderdate,
cost,
lag(orderdate,1,orderdate) over(partition by name order by orderdate) follower_1
from business;
这里先介绍一下 lag(col,n,default_val) 函数的使用，表示往前取第n行的col列的数据，如果没有则用默认值default_val。over(partition by name order by orderdate) 的意思就是对每一行来说，窗口的范围是对name分组，对orderdate排序的数据，那么lag(orderdate,1,orderdate) 表示取窗口中的上一行的数据。
同理lead(col,n, default_val) 函数表示往后取第n行的col列的数据，如果没有则用默认值default_val，大家可以自行测试一下该函数
2.2.5 根据时间先后将购买明细分为5组
结果如下图：
这里先介绍一下ntile(n)函数：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，ntile返回此行所属的组的编号。
代码如下：
select
name,
orderdate,
cost,
ntile(5) over(order by orderdate) id
from business
2.3 经典案例求分组TopN
现有如下数据，表示学生各个成绩：
小张 语文 87
小张 数学 95
小张 英语 68
小明 语文 94
小明 数学 56
小明 英语 84
小李 语文 64
小李 数学 86
小李 英语 84
小王 语文 65
小王 数学 85
小王 英语 78
Hive建表，导入数据代码如下：
create table score(
name string,
subject string,
score int)
row format delimited fields terminated by " ";
load data local inpath '/opt/data/score.txt' into table score;
现在需要求出每门学科前三名的学生？
先介绍一下有关排序函数+over()的使用
rank() 排序相同时会重复，总数不会变
dense_rank() 排序相同时会重复，总数会减少
row_number() 排序相同时不会重复，根据顺序计算
我们先求各科的成绩排名，直接看代码（包含rank、dense_rank、row_number的对比）
select
subject,
name,
score,
rank() over(partition by subject order by score desc) rank_1,
dense_rank() over(partition by subject order by score desc) rank_2,
row_number() over(partition by subject order by score desc) rank_3
from score;
根据上文对于窗口函数的讲解，我想这段代码应该很好理解吧，rank()/dense_rank()/row_number()函数就是给当前行设置一个排名，排名就是窗口函数中范围的最后一个，窗口函数的范围就是根据subject分组，score降序排序后到当前行的数据。
在以上结果的基础上我们在求各学科排名前3的学生，很简单，就是取rank_1 <= 3的数据即可，代码如下（这里用rank()函数来求）
select
tmp.*
from
(
select
subject,
name,
score,
rank() over(partition by subject order by score desc) rank_1
from score
) tmp
where tmp.rank_1 <= 3;
总结
本文主要讲解了对于窗口函数中over()开窗范围的理解，以及一些表示范围的字字段如：partiton by、order by、current row等等，以及rank()、lag()、row_number()一些和over()函数相结合的函数，平时用的多的开窗范围就是over(partition by 字段 order by 字段)，分组TopN也是rank() over(partition by 字段 order by 字段)这么一个套路。看到这里我想你对窗口函数已经有了自己的理解，本文的数据均来自于尚硅谷大数据Hive资料中。若其中的讲解有误，还请各位多多指正。