公众号: 数据小斑马,关注即可获得价值1000元的数据分析学习资料
SQL系列目录（文末有大礼赠送）：
SQL技巧初级系列①—建表/更新表/删除表
SQL技巧初级系列②—聚合和排序（group by,having,order by)
SQL技巧初级系列③——数据拼接（集合运算union和列连接join)
SQL技巧中级系列①——字符串函数的使用
SQL技巧中级系列②——日期函数的使用
SQL技巧高级系列②——聚合函数和CASE WHEN的使用
在窗口分析函数出现之前，存在很多 SQL 难以解决的问题，很多都要通过复杂的相关子查询完成，或到Excel中用函数处理
2003年ISO SQL标准加入了窗口分析函数，使得这些难题轻松被攻克，大大提升了效率，减少了Excel卡死导致前功尽弃的情况
本文从聚合/排序/极值/移动/切片五大场景来讲解窗口分析函数
窗口分析函数，可以指定数据窗口进行统计分析，它和普通聚合函数的区别：
① 窗口函数对每个组返回多行，而聚合函数对每个组只返回一行
② 窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，而聚合函数是针对所有数据进行统计
窗口函数写法：
函数( ) over (partition by 列名2 order by 列名3 rows between n/unbounded preceding and m following/current row )
函数( ) ——按列名进行统计，排序可以不写列名
partition by——按列名进行分组，如果不指定partition by，则不需要分组
order by——按列名进行排序，如果不指定order by，则将分组内所有值累加
rows_between——限定统计窗口大小，如果不指定rows between，默认从起点到当前行
① preceding:往前
② following：往后
③ current rows：当前行
④ unbounded preceding：起点行
⑤ unbounded following：终点行
⑥ rows between unbounded preceding and current row 是最常用的定位框架，可以省略
有了初步的语法了解，下面以某店铺2019年的销量为例来讲解五大应用，表名为 sale_detail，你准备好了吗？
一、聚合
公众号: 数据小斑马,关注即可获得价值1000元的数据分析学习资料
1、店铺19年总销量(sum)
sum是求和，over()没有参数，则对所有数据进行求和，输出的结果都是5800
select a.*, sum(sale)over() as total_sale from sale_detial a
2、店铺19年每个level总销量(sum)
用level分组求和，则每个level的输出结果一致
select a.*,sum(sale)over() as total_sale,sum(sale)over(partition by level) as level_sale from sale_detial a
3、店铺19年每个level按城市销量降序累加求和销量(sum)
当使用order by时，没有rows between则意味着窗口是从起始行到当前行，所以对不同level进行累加求和
select a.*,sum(sale)over() as total_sale,sum(sale)over(partition by level) as level_sale,sum(sale)over(partition by level order by sale desc) as level_cum_salefrom sale_detial a
4、店铺19年总销售城市数量、每个level城市数量(count)
count()是计数，可以用count(distinct city)进行去重，如果partition by进行分组，则分组后计数
select a.*,count(city)over() as total_city,count(city)over(partition by level) as level_cityfrom sale_detial a
5、 店铺19年平均每个城市销量，各level平均销量(avg)
avg用法与sum基本一致
select a.*,avg(sale)over() as avg_sale,avg(sale)over(partition by level) as level_avg_salefrom sale_detial a
6、店铺19年按城市销量降序后，截止当前平均、移动平均(avg)
当用rows between指定窗口后可以计算移动平均
select a.*,avg(sale)over(order by sale desc) as avg_sale,avg(sale)over(order by sale desc rows between 1 preceding and 1 following) as avg_sale_1from sale_detial a
7、 店铺19年城市最高销量，各level最低销量(max/min)
max/min用法与sum一致
select a.*,max(sale)over() as max_sale,min(sale)over(partition by level) as level_min_salefrom sale_detial a
二、排序
公众号: 数据小斑马,关注即可获得价值1000元的数据分析学习资料
1、店铺19年各城市按销量排序（row_number,rank,dense_rank)
row_number——从1开始，按照顺序，生成分组内记录的序列
rank——成数据项在分组中的排名，排名相等会在名次中留下空位。
dense_rank——生成数据项在分组中的排名，排名相等会在名词中不会留下空位
select a.*,row_number()over(order by sale desc) as row_number,rank()over(order by sale desc) as rank,dense_rank()over(order by sale desc) as dense_rankfrom sale_detial a
三、极值
公众号: 数据小斑马,关注即可获得价值1000元的数据分析学习资料
1、店铺19年销量最高最低城市，各level销量最低城市（first_value,last_value)
first_value，按分组排序后，取范围内第1个值，last_value，取最后1个值
因为默认窗口的关系，last_value会随着窗口的改变而改变，所以一般不用last_value，如果要用，则改变窗口为所有行
select a.*,first_value(city)over(order by sale desc) as max_city,first_value(city)over(order by sale asc) as min_city,last_value(city)over(order by sale desc) as min_city_1,last_value(city)over(partition by level order by sale desc rows between unbounded preceding and unbounded following) as level_min_cityfrom sale_detial a
四、移动
公众号: 数据小斑马,关注即可获得价值1000元的数据分析学习资料
1、店铺19年按level分组后各城市销量前1位和后1位的城市（lag,lead)
lag/lead是按照排序规则，取前多少位和后多少位，参数有3个，第1个是要取出来的列，第2个移动多少位，第3个是如果取不到，赋予的值，默认取不到是NULL
select a.*,lag(city,1,null)over(partition by level order by sale desc) as lag_city,lead(city,1,’0′)over(partition by level order by sale desc) as lead_cityfrom sale_detial a
五、切片
公众号: 数据小斑马,关注即可获得价值1000元的数据分析学习资料
1、店铺19年按销量切片、各level按销量切片（ntile)
ntile(n)，用于将分组数据按照顺序切分成N片，返回当前切片值。ntile不支持rows between，如果切片不均匀，默认增加第一个切片的分布。
ntile这个很强大，以前要获取一定比例的数据是非常困难的，ntile就是把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号
select a.*,ntile(3) over(order by sale desc) as total_part,ntile(2)over(partition by level order by sale desc) as level_partfrom sale_detial a
上面就是5大常用到的语法，你们都hold住了吗？
本人互联网数据分析师，目前已出Excel，SQL，Pandas，Matplotlib，Seaborn，机器学习，统计学，个性推荐，关联算法，工作总结系列。
福利:
微信搜索并关注 ” 数据小斑马” 公众号，回复“sql”就可以免费领取下方sql必知必会、sql基础教程等9本sql入门到精通9本书籍