SQL分析函数-前后函数(二篇)

描述：开窗函数就是分析函数 分析函数就是开窗函数 保持之前的风格 就这样记录吧 今天详细解释一下分析函数之偏移函数lag()lead()

目录​​​​​​​

🏆一、理论

🏆二、实战

⭐️2.1、准备数据

⭐️2.2、演示lag()

⭐️2.3、演示​​​​​​​lead()

🏆三、参考文章

---

 

🏆一、理论

lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）

当偏移函数 参数只有一个的时候 默认偏移量是1 偏移的最后字段值为null

🏆二、实战

⭐️2.1、准备数据

drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:02', 84),
(1006, 9001, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89),
(1006, 9002, '2021-09-06 10:01:01', '2021-09-06 10:21:01', 81),
(1005, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 81),
(1005, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81);

select * from exam_record

效果图

 本次演示我想利用理论知识 对sql 语句查询结果一个预测 然后 使用sql语句去查询验证 

⭐️2.2、演示lag()

lag函数向后偏移 注意偏移这个词 需要好好理解一下 

select rank() over(order by start_time) 'sid', start_time from exam_record order by start_time

效果图(不要看上面的sql语句是怎么写的 这里不做解释 重点是看下面的效果图 我是将start_time字段升序排序 然后加了一个排名的sid字段和值) 

1.png

 lag()向前偏移

lag(start_time,2,0)这里意思就是将start_time字段的值向前偏移2位 前面没有值的显示为0

理论解释：

sid 1 2021-09-01 12:11:01 向前偏移2位 (1.png) 因为前面没有值 显示为0

sid 2 2021-09-05 10:01:01  向前偏移2位 (参照1.png) 因为前面没有值 显示为0

sid 3 2021-09-05 10:31:01  向前偏移2位 (相对1.png)  就是 2021-09-01 12:11:01

sid 4 2021-09-06 10:01:01 向前偏移2位 (1.png) 就是 2021-09-05 10:01:01

sid 5 2021-09-07 10:01:01 向前偏移2位 (1.png) 就是 2021-09-05 10:31:01

好 理论分析完毕 那么接下来 直接上查询sql 看看 我们的双重验证是否成立

select lag(start_time,2,0) over(order by start_time) from exam_record

效果图(ok,没有什么问题 成立 上面的sql需要补充的就是 任何分析函数都必须和over同时存在，如果对这句话不太理解，可以参考《SQL开窗函数(一)博客进行学习》) 

⭐️2.3、演示lead()

lead()向后偏移

lead(start_time,1,-3) 这里意思就是将start_time字段的值向后偏移1位 前面没有值的显示为-3

理论解释：

sid 1 2021-09-01 12:11:01 向后偏移1位(参考数据1.png) 就是 2021-09-05 10:01:01

sid 2 2021-09-05 10:01:01  向后偏移1位(参考数据1.png) 2021-09-05 10:31:01

sid 3 2021-09-05 10:31:01  向后偏移1位(参考数据1.png) 021-09-06 10:01:01

sid 4 2021-09-06 10:01:01 向后偏移1位(参考数据1.png) 021-09-07 10:01:01

sid 5 2021-09-07 10:01:01 向后偏移1位(参考数据1.png) 因为后面没有之 所以就是我们参数中填写的-3

好 理论分析完毕 那么接下来 直接上查询sql 看看 我们的双重验证是否成立

select lead(start_time,1,-3)over(order by start_time) from exam_record

效果图 

🏆三、参考文章

关于SQL的LAG()和LEAD()函数_一碗烫烫的汤圆的博客-CSDN博客_lag sql

SQL SERVER中LEAD和LAG函数_DePaul的博客-CSDN博客_sql中lead

SQL LEAD()函数 LAG()函数 - jasonlai2016 - 博客园