每日SQL自学知识点(第三天)—lead的窗口函数、datediff函数

1）lead的窗口函数用法

• lead(字段名，n) over () :取值向后偏移n行（空间的理解就是直接将一列数据往前推n个位置，后面的位置就空出来了，具体配合图片理解）;

• lag(字段名，n) over () :取值向前偏移n行（空间的理解就是直接将一列数据往前后n个位置，前面的位置就空出来了，具体配合图片理解）;
• lag(字段名，n，x) over () :取值向前偏移n行，并将空值填充为数字x（空间的理解就是直接将一列数据往前后n个位置，前面的空出来的位置用X填充上，具体配合图片理解） 。

SELECT id,score,Lead(score,2) over(order by id) lead_score,-- score数列向前推动2位，后面就腾空了2个位置

Lag(score,2) over(order by id) lag_score, -- score数列向后推2位，腾空2个位置

lag(score,2,666) over(order by id) lag_score_3 -- score数列向后推动2位，空值被填充为666

FROM exam_record;

2）datediff 函数

• datediff(时间1，时间2)：计算两个日期之间间隔的天数，单位为日
• timestampdiff(时间单位，开始时间，结束时间):两个日期的时间差，返回的时间差形式由时间单位决定(日，周，月，年)
• date_add(日期，INTERVAL n 时间单位) ：返回加上n个时间单位后的日期
• date_sub(日期，INTERVAL n 时间单位 )：返回减去n个时间单位后的日期
• date_format(时间，‘%Y-%m-%d’)：强制转换时间为所需要的格式

3）datediff和timestampdiff函数的区别

• datediff()函数的作用是求日期差，也就是把一个时间的日期部分取出来求差。例如：'2021-09-05 12:00:00'和'2021-09-04 11:00:00'这两个日期，datediff只取2021-09-05和2021-09-04求日期差，并不会管后面的时间部分。
• timestampdiff()函数的作用则是求时间戳的差，例如：'2021-09-05 12:00:00'和'2021-09-04 11:00:00'这两个日期，datediff只会先求出这个日期的时分秒差，之后再转换成天数来求日期差。

直接说可能有点懵，看完差别后，具体来看下面这个例子：

#先看第一组时间差是23小时，日期差（9月5日-9月4日）是1天

select DATEDIFF('2021-09-05 12:00:00','2021-09-04 11:00:00') datediff_1,

TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 11:00:00') hourdiff_1,

TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 11:00:00')timediff_1;

#再看第二组时间差是28小时，日期差（9月5日-9月4日）是1天

select DATEDIFF('2021-09-05 16:00:00','2021-09-04 11:00:00') datediff_2,

TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 16:00:00') hourdiff_2,

TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 16:00:00')timediff_2;