公众号: 数据小斑马,关注即可获得价值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本书籍