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

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

01

—

【场景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 (   [ PARTITION BY part_list ]   [ ORDER BY order_list ]

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

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

传统的聚合、排序等函数都是基于全局整表的，窗口函数可以基于表中的每个细分部分。窗口函数在select子句的执行顺序中，仅在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这种

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

SELECT userid,createtime,pv,rank() over(partition BY userid order by pv DESC) AS rn1,dense_rank() over(partition BY userid order by pv DESC) AS rn2,row_number() over(partition BY userid order by pv DESC) AS rn3FROM tb_visit;

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

select *,row_number() over(partition by cate2_name order by amount desc) rank_secdfrom tbnamewhere  rank_secd<=10

（2）聚合函数

有以下示例：

SELECT *,sum(col2) over (partition by col1 order by col2) as current_sum,avg(col2) over (partition by col1 order by col2) as current_avg,count(col2) over (partition by col1 order by col2) as current_count,max(col2) over (partition by col1 order by col2) as current_max,min(col2) over (partition by col1 order bycol2) as current_minFROM tbname;

（3）取值函数

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

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

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

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

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

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