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

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

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

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

01

窗口函数的应用场景

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

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

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

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

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

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

02

基础概念

什么是窗口函数呢?

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

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

【关键词】over , partition by

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

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

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

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

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

03

应用示例

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

(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的数据

select*,row_number()over(partitionbycate2_nameorderbyamountdesc)rank_secdfromtbnamewhererank_secd<=10

(2)聚合函数

有以下示例:

select*,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行值

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

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

原文链接:https://www.1588tao.com/19131.html,转载请注明出处。

0
手机用户充值请使用第二个微信即可支付,电脑端两个充值窗口都正常。有任何问题向客服请问
没有账号? 注册  忘记密码?
'); })();