目录
1、窗口函数整体思路
窗口函数,顾名思义包含两个部分,窗口和函数
函数:是指数据的统计方式,如排序、求和、求均值等等
窗口:是指在什么范围内进行统计,可以想象为一个小窗户,通过这个小窗户可以看到整体数据的一部分,那么前面的函数就在这个窗口可见的数据范围内生效。并且这个小窗户是可以移动的,窗口移动时,数据范围也在变化。同时,窗口外的整体数据要有一定的分布方式而不是混乱零散的,只有这样,窗口看到的数据才是有规律的,分布方式有两个参数决定:
partition by:分组
order by :排序
所以一个完整的窗口函数的写法应该是这样的:
函数()over(partition by col1 order by col2)
这样讲,可能有些难理解,那么我们带入到实际的案例中看一下,窗口函数是如何使用的
2、典型窗口函数应用
2.1、排序
现在有一些用户的下单信息,想给每个用户下的订单按下单日期进行排序
user_id | date | 订单id |
小红 | 2022-01-01 | 001 |
小红 | 2022-01-01 | 002 |
小红 | 2022-01-02 | 003 |
小黑 | 2022-01-01 | 004 |
小黑 | 2022-01-03 | 005 |
select
user_id,
date,
order_id,
dense_rank()over(partition by user_id order by date ) num1,
--并列名次相同,后面的不跳过继续排序,如:123345
rank()over(partition by user_id order by date ) num2,
--并列名次相同,后面的跳过继续排序,如:123356
row_number()over(partition by user_id order by date ) num3
--并列名次不同,如123456
FROM table_name
在上面的例子中,可以看到窗口函数的拆解为:
函数部分:
- dense_rank(),并列连续
- rank(),并列不连续
- row_number() ,不并列
窗口部分
over(partition by user_id order by date)
可以理解为,每个用户作为一个统计窗口,得的每个用户下按日期排序后的顺序id
注意:这里窗口部分分组和排序都可以为空,如over(partition by()order by ()),也就是把整个数据作为一个整体进行统计
2.2、偏移
还是上面的例子,现在想知道用户没两笔订单之间的间隔天数,那就需要得到用户每笔订单的上一笔订单的下单日期,两个日期相减,这就要用到偏移的概念
select
user_id,
date as 下单日期,
LAG(date,1) OVER(PARTITION BY user_id ORDER BY date) AS lng1
--取上1个日期
LAG(date,2) OVER(PARTITION BY user_id ORDER BY date) AS lng2
--取上2个日期
Lead(date,1) OVER(PARTITION BY user_id ORDER BY date) AS lead1
--取下1个日期
Lead(date,2) OVER(PARTITION BY user_id ORDER BY date) AS lead2
--取下2个日期
FROM table_name
user_id | date | 订单id | 上一笔订单日期 |
小红 | 2022-01-01 | 001 | |
小红 | 2022-01-01 | 002 | 2022-01-01 |
小红 | 2022-01-02 | 003 | 2022-01-01 |
小黑 | 2022-01-01 | 004 | |
小黑 | 2022-01-03 | 005 | 2022-01-01 |
拆解窗口函数
函数部分
- 向过去寻找上一个记录lag(排序字段,移动n个),如:lag(date,1)就是站在当前找之前的1个日期
- 向未来寻找下一个记录lead(排序字段,移动n个),如:lead(date,1)就是站在当前找之后的1个日期
- 窗口部分同上 over(partition by user_id order by date)
偏移是非常好的计算间隔天的方法。
2.3、其他:如累计求和
现在有一份记录用户购买VIP情况的数据,需要将其转换为统计每月累计购买过VIP的用户数量,即第N月的VIP数量是第N-1个月VIP数量加上当月新增VIP数量。这里注意,用户会重复购买
SELECT
mon,
vip_num,--当月新增VIP数量
sum(vip_num) over( order by mon) as allvip --累计VIP数量
from
(
select
substr(date,1,6) as mon,
COUNT(DISTINCT user_id) as vip_num--去重统计购买VIP用户数量
from
(SELECT
user_id,
min(date) AS date --取最早购买日期yyyymmdd
FROM table
group by user_id)a
group by substr(date,1,6)--按照最早购买日期的月份汇总
这里在得到每个月的VIP购买量之后,使用窗口函数得到了累计购买VIP用户数
函数:sum(vip_num)
窗口:over(order by mon)
这里可以看到没有使用partition by,因为在本例中没有需要分组的字段。那么摄像如果购买的VIP有等级,想看每个等级VIP的累计购买量,那么VIP等级就会成为一个分组字段,需要写入partition by()中。