SQL-窗口函数知识点及案例学习

目录

1、窗口函数整体思路

2、典型窗口函数应用

2.1、排序

2.2、偏移

2.3、其他：如累计求和

---

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（）中。