SQL开窗函数之前后函数(LEAD、LAG)
作者:mmseoamin日期:2023-12-21

开窗函数

当我们需要进行一些比较复杂的子查询时,聚合函数就会非常的麻烦,因此可以使用开窗函数进行分组再运用函数查询。窗口函数既可以显示聚集前的数据,也可以显示聚集后的数据,可以在同一行中返回基础行的列值和聚合后的结果列

常见运用场景: 对班里同学成绩进行排序

常见的窗口函数

SQL开窗函数之前后函数(LEAD、LAG),在这里插入图片描述,第1张

开窗函数基本形式

func_name() 
OVER(
[PARTITION BY ] 
[ORDER BY  ASC|DESC]
[rows between ?? And ??]
)

具体字段的解释看我的上一篇:SQL开窗函数之基本用法和聚合函数

前后函数

LEAD函数和 LAG函数主要用于查询当前字段的上一个值或下一个值,若向上取值或向下取值没有数据的时候显示为NULL

  • LEAD: 向后偏移
  • LAG: 向前偏移
    LAG(,offset,default_value) 
    OVER(
         PARTITION BY expr,
         ORDER BY expr [ASC|DESC]
    	)
    

    字段解释

    • Expression: 需要被偏移的字段
    • Offset: 偏移的量
    • default_value: 超出记录窗口时的默认值(可以设置为0,默认为null)

      应用1:前后日期温度比较

      weather表

      SQL开窗函数之前后函数(LEAD、LAG),在这里插入图片描述,第2张

      • 获取前一天的温度和后一天的温度
        select *,
        lead(temperature, 1) over(order by recordDate) as lead_temp,
        lag(temperature, 1) over(order by recordDate) as lag_temp
        from weather
        

        SQL开窗函数之前后函数(LEAD、LAG),在这里插入图片描述,第3张

        • 获取与当天比前一天温度更高的日期
          with a as (       
          select *,
          lead(temperature, 1) over(order by recordDate) as lead_temp,
          lag(temperature, 1) over(order by recordDate) as lag_temp
          from weather
          )
          select * from a 
          where lag_temp < temperature
          

          SQL开窗函数之前后函数(LEAD、LAG),在这里插入图片描述,第4张

          应用2:求出连续登录5天的用户

          LeetCode:1454.活跃用户

          用户登录表

          SQL开窗函数之前后函数(LEAD、LAG),在这里插入图片描述,第5张

          题解:

          1. 用户可能同一天登录了多次,而我们只需要一个登陆日期,因此需要 group by user_id, login_time 去重
          2. 用 lead() over() 窗口函数查找往下第4个登录日期
          3. 用datediff查找往下第4个登陆日期是否与当前日期相差4天,即连续5天

          用lead找出天数差

          select user_id, login_time, 
          lead(login_time,4) over(partition by user_id order by login_time) as '5次后登录的时间', 
          datediff(lead(login_time,4) over(partition by user_id order by login_time), login_time) as '天数差'
          from user_login
          group by user_id, date(login_time);
          

          然后从上面找到连续登录了5天的用户,即 “天数差” 为4的用户

          -- 完整代码
          with a as(
          select user_id, login_time, 
          lead(login_time,4) over(partition by user_id order by login_time) as '5次后登录的时间', 
          datediff(lead(login_time,4) over(partition by user_id order by login_time), login_time) as date_diff
          from user_login
          group by user_id, date(login_time)
          )
          select distinct user_id from a where date_diff = 4;
          

          SQL开窗函数之前后函数(LEAD、LAG),在这里插入图片描述,第6张

          这里主要用到的两个例子,建表如下

          -- weather表
          drop table if exists weather;
          create table weather(
              id int,
              recordDate date,
              temperature int
          );
          insert into weather
          values (1,'2015-01-01',10),
                 (2,'2015-01-02',25),
                 (3,'2015-01-03',20),
                 (4,'2015-01-04',30);
          -- 用户登录表
          drop table if exists user_login;
          create table user_login
          ( 
          user_id varchar(100), 
          login_time datetime
          ); 
          insert into user_login values 
          (1,'2020-11-25 13:21:12'), 
          (1,'2020-11-24 13:15:22'), 
          (1,'2020-11-24 10:30:15'), 
          (1,'2020-11-24 09:18:27'), 
          (1,'2020-11-23 07:43:54'), 
          (1,'2020-11-10 09:48:36'), 
          (1,'2020-11-09 03:30:22'), 
          (1,'2020-11-01 15:28:29'), 
          (1,'2020-10-31 09:37:45'), 
          (2,'2020-11-25 13:54:40'), 
          (2,'2020-11-24 13:22:32'), 
          (2,'2020-11-23 10:55:52'), 
          (2,'2020-11-22 08:56:33'),
          (2,'2020-11-22 06:30:09'), 
          (2,'2020-11-21 08:33:15'), 
          (2,'2020-11-20 05:38:18'), 
          (2,'2020-11-19 09:21:42'), 
          (2,'2020-11-02 00:19:38'), 
          (2,'2020-11-01 09:03:11'), 
          (2,'2020-10-31 07:44:55'), 
          (2,'2020-10-30 08:56:33'), 
          (2,'2020-10-29 09:30:28'); 
          

          参考来源:

          MySQL8中的开窗函数

          SQL练习题:连续登录5天的活跃用户