SQL开窗函数之前后函数（LEAD、LAG）

开窗函数

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

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

常见的窗口函数

开窗函数基本形式

func_name(<parameter>)OVER([PARTITIONBY<part_by_condition>][ORDERBY<order_by_list>ASC|DESC][rowsbetween ?? And ??])

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

前后函数

LEAD

函数和

LAG

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

• LEAD: 向后偏移
• LAG: 向前偏移

LAG(<expression>,offset,default_value)OVER(PARTITIONBY expr,ORDERBY expr [ASC|DESC])

字段解释

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

应用1：前后日期温度比较
weather表

• 获取前一天的温度和后一天的温度

select*,
lead(temperature,1)over(orderby recordDate)as lead_temp,
lag(temperature,1)over(orderby recordDate)as lag_temp
from weather

• 获取与当天比前一天温度更高的日期

with a as(select*,
lead(temperature,1)over(orderby recordDate)as lead_temp,
lag(temperature,1)over(orderby recordDate)as lag_temp
from weather
)select*from a 
where lag_temp < temperature

应用2：求出连续登录5天的用户
LeetCode：1454.活跃用户

用户登录表

题解：

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(partitionby user_id orderby login_time)as'5次后登录的时间', 
datediff(lead(login_time,4)over(partitionby user_id orderby login_time), login_time)as'天数差'from user_login
groupby user_id,date(login_time);

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

-- 完整代码with a as(select user_id, login_time, 
lead(login_time,4)over(partitionby user_id orderby login_time)as'5次后登录的时间', 
datediff(lead(login_time,4)over(partitionby user_id orderby login_time), login_time)as date_diff
from user_login
groupby user_id,date(login_time))selectdistinct user_id from a where date_diff =4;

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

-- weather表droptableifexists weather;createtable weather(
    id int,
    recordDate date,
    temperature int);insertinto weather
values(1,'2015-01-01',10),(2,'2015-01-02',25),(3,'2015-01-03',20),(4,'2015-01-04',30);-- 用户登录表droptableifexists user_login;createtable user_login
( 
user_id varchar(100), 
login_time datetime);insertinto 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天的活跃用户