开窗函数
当我们需要进行一些比较复杂的子查询时,聚合函数就会非常的麻烦,因此可以使用开窗函数进行分组再运用函数查询。窗口函数既可以显示聚集前的数据,也可以显示聚集后的数据,可以在同一行中返回基础行的列值和聚合后的结果列
常见运用场景: 对班里同学成绩进行排序
常见的窗口函数
开窗函数基本形式
func_name(<parameter>) OVER([PARTITION BY <part_by_condition>] [ORDER BY <order_by_list> ASC|DESC][rows between " />And ??])
具体字段的解释看我的上一篇:SQL开窗函数之基本用法和聚合函数
前后函数
LEAD
函数和 LAG
函数主要用于查询当前字段的上一个值或下一个值,若向上取值或向下取值没有数据的时候显示为NULL
- LEAD: 向后偏移
- LAG: 向前偏移
LAG(<expression>,offset,default_value) OVER( PARTITION BY expr, ORDER BY expr [ASC|DESC])
字段解释
- Expression: 需要被偏移的字段
- Offset: 偏移的量
- default_value: 超出记录窗口时的默认值(可以设置为0,默认为null)
应用1:前后日期温度比较
weather表
- 获取前一天的温度和后一天的温度
select *,lead(temperature, 1) over(order by recordDate) as lead_temp,lag(temperature, 1) over(order by recordDate) as lag_tempfrom weather
- 获取与当天比前一天温度更高的日期
with a as ( select *,lead(temperature, 1) over(order by recordDate) as lead_temp,lag(temperature, 1) over(order by recordDate) as lag_tempfrom weather)select * from a where lag_temp < temperature
应用2:求出连续登录5天的用户
LeetCode:1454.活跃用户
用户登录表
题解:
- 用户可能同一天登录了多次,而我们只需要一个登陆日期,因此需要 group by user_id, login_time 去重
- 用 lead() over() 窗口函数查找往下第4个登录日期
- 用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_logingroup 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_difffrom user_logingroup by user_id, date(login_time))select distinct user_id from a where date_diff = 4;
这里主要用到的两个例子,建表如下
-- weather表drop table if exists weather;create table weather(id int,recordDate date,temperature int);insert into weathervalues (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天的活跃用户