窗口函数在日常的处理分析中必不可少,相关详细介绍可查看之前的文章,大家可在公众号(Excel办公小技巧)首页点击右上角搜索按钮进行搜索。今天主要是有节奏的从使用示例中巩固。
【1、从排序开始入门】
学习窗口函数,怎么能错过排序用法使用。从三种排序函数的功能使用中,还可以掌握窗口函数结构,比如分组、排序参数的使用。
比如,按订单时间降序排序用户订单数据:
1.1 row_number
普通排序,重复的编号+1,编号最大值与实际数据行数相同。
select *,row_number() over(partition by userid order by orderdate desc) rn
from table
1.2 rank
重复的编号不变,后面的按前面行数+1往后排。
select *,rank() over(partition by userid order by orderdate desc) rn
from table
1.3 dense_rank
重复行编号不变,后面的编号+1,行的数量≥排序编号
select *,dense_rank() over(partition by userid order by orderdate desc) rn
from table
【2、使用聚合函数】
数据统计分析,聚合函数当然必不可少,如何将聚合函数与窗口函数完美结合呢?
比如,获得用户的销售总额、最大销售、订单数量等等:
2.1 sum函数+窗口函数
select *,sum(money) over(partition by userid ) rn
from table
2.2 max函数+窗口函数
select *,max(money) over(partition by userid ) rn
from table
2.3 count函数+窗口函数
select *,count(orderid) over(partition by userid ) rn
from table
【3、进一步应用】
以上,可以看到,窗口函数不会改变原有数据的行数,还能帮助我们添加想要的列,而有了新增列,你想做什么,是不是很容易了呢?
3.1 窗口函数+聚合函数
取销售额前三名的用户订单明细:
select *
from(select * ,DENSE_RANK()over( order by rn desc) rn1
from(select *,sum(money) over(partition by userid ) rn
from hdp_yunchuang_defaultdb.guofeng06_gogo))
where rn1<4
同样的方法,也可以取到订单数、最大销售额、销售数量、平均销售额等排序后的用户订单明细。
3.2 连续登陆判别
如何判别客户是否连续下单(天维度)
select *,lead(orderdate,1,'补足') over(partition by userid order by orderdate asc ) rn
from table
当‘orderdate’列和“求和”列的差值=1,说明用户有连续下单行为出现。
如果是判别某天下单用户在前一天是否有下单,则可以使用lag函数。
3.3 指定连续天数判别
比如取半个月内,连续15天登陆,14天,...,2天等
select *,lead(orderdate,3) over(partition by userid order by orderdate asc ) lianxu4,
lead(orderdate,2) over(partition by userid order by orderdate asc ) lianxu3,
lead(orderdate,1) over(partition by userid order by orderdate asc ) lianxu2,lead(orderdate,0) over(partition by userid order by orderdate asc ) lianxu1
from table
不为空即为符合要求结果,后面简单处理就可以了(以上,记得先去重,本例未去重,实际计算的不对,再在后续进行天数比较)。