窗口函数：数据分析的常见场景+SQL分析利器

更新时间: 2022-01-13 20:16:10 点击数:

“ 窗口函数，是面试数据分析师岗位的高频考点之一，也是数据分析中的常见场景。”

今天和大家一起聊聊窗口函数。如果还不知道窗口函数的同学，今天的分享一定会给你带来较多收获的。

—

窗口函数的应用场景

在讲什么是窗口函数之前，先来举几个在写SQL时，经常遇到的一些场景。

【场景1】现在数据库中有一张用户交易表order，其中有userid（用户ID）、amount（消费金额）、paytime（支付时间），请写出对应的SQL语句，查出每个用户第一单的消费金额。

【场景2】数据库中有一张销售业绩表，其中有销售员id，部门名称，销售金额。要取出每个部门销售金额Top10的员工，作为优秀员工。

其实本质上，场景1和场景2的内容是一样。

如果是查询每个用户最大金额、最小金额，对于熟悉SQL的同学，应该比较清晰，直接group by就行。但这里多了一个条件，按照金额的时间取第一单、或者按照销售取top10，即不再是全局排序、统计。你该咋办呢？

对，解决这种SQL取数的问题，就需要用到窗口函数。

—

基础概念

什么是窗口函数呢？

窗口函数，也叫OLAP(Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理，一般和分析函数搭配使用以达到数据处理的目的。

其实可以将窗口函数理解成，将整体表按照某个字段拆分成多个小表，然后在小表中求排序、聚合、取值等相关操作的函数。

【关键词】over , partition by

窗口函数的语法结构如下：

window_function(expression)OVER([PARTITIONBYpart_list][ORDERBYorder_list]

PARTITION BY 表示将数据先按 part_list 进行分区

ORDER BY 表示将各个分区内的数据按 order_list 进行排序

传统的聚合、排序等函数都是基于全局整表的，窗口函数可以基于表中的每个细分部分。窗口函数在ｓｅｌｅｃｔ子句的执行顺序中，仅在order by之前。没事，没理解的话，下面会详细举几个例子。

—

应用示例

窗口函数主要有以下几类，其中排序函数应该是最常用到的。

（1）排序函数

几个不同排序函数的一些差异，可以根据不同的业务场景选择合适的函数：

RANK(): 生成数据项在分组中的排名，排名相等会在名次中留下空位。比如会出现1、2、2、4、4、6、7

ROW_NUMBER(): 从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列。结果只会是1、2、3、4、5、6、7

DENSE_RANK(): 生成数据项在分组中的排名，排名相等会在名次中不会留下空位，比如会出现1、2、2、3、3、4、5这种

举例，给流量表按照用户的访问时间加上每个用户的访问次序：

SELECTuserid,createtime,pv,rank()over(partitionBYuseridorderbypvDESC)ASrn1,dense_rank()over(partitionBYuseridorderbypvDESC)ASrn2,row_number()over(partitionBYuseridorderbypvDESC)ASrn3FROMtb_visit;

举例，取各二级类目下sku的订单金额总和前10的数据

ｓｅｌｅｃｔ*,row_number()over(partitionbycate2_nameorderbyamountdesc)rank_secdfromtbnamewhererank_secd<=10

（2）聚合函数

有以下示例：

ｓｅｌｅｃｔ*,sum(col2)over(partitionbycol1orderbycol2)ascurrent_sum,avg(col2)over(partitionbycol1orderbycol2)ascurrent_avg,count(col2)over(partitionbycol1orderbycol2)ascurrent_count,max(col2)over(partitionbycol1orderbycol2)ascurrent_max,min(col2)over(partitionbycol1orderbycol2)ascurrent_minFROMtbname;

（3）取值函数

FIRST_VALUE():取分组内排序后，截止到当前行，第一个值

LAST_VALUE():取分组内排序后，截止到当前行，最后一个值

LAG():LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

LEAD():与LAG相反 LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

取值函数主要是用于错位进行计算，这里就不详细举例了。

以上是窗口函数的一些介绍，欢迎交流~