MYSQL lag() 和lead()函数使用介绍

语法

简单理解，选择第3行数据，LAG()提取先前行（3行之前）内容，LEAD()提取后续行（3行之后）的数据。两者语法没有区别，就是方向不同。

LEAD()函数是一个窗口函数，允许您向前看多行并从当前行访问行的数据。与LAG()函数类似，LEAD()函数对于计算同一结果集中当前行和后续行之间的差异非常有用。

LAG语法：LAG(<expression>[,offset[, default_value]]) OVER ( PARTITION BY expr,... ORDER BY expr [ASC|DESC],... )

LEAD语法：LEAD(<expression>[,offset[, default_value]]) OVER ( PARTITION BY (expr) ORDER BY (expr))

• expression：LEAD()函数返回的值expression从offset-th有序分区排。
• offset：offset是从当前行向前行的行数，以获取值。offset必须是一个非负整数。如果offset为零，则LEAD()函数计算expression当前行的值。如果省略 offset，则LEAD()函数默认使用一个。
• default_value：如果没有后续行，则LEAD()函数返回default_value。例如，如果offset是1，则最后一行的返回值为default_value。如果您未指定default_value，则函数返回 NULL 。
• PARTITION BY子句：PARTITION BY子句将结果集中的行划分LEAD()为应用函数的分区。如果PARTITION BY未指定子句，则结果集中的所有行都将被视为单个分区。
• ORDER BY子句：ORDER BY子句确定LEAD()应用函数之前分区中行的顺序。

例题一：

以下语句查找每个客户的订单日期和下一个订单日期：

SELECT 
    customerName,
    orderDate,
    LEAD(orderDate,1) OVER ( #返回的值是向前一行的orderDate，没有定义后续行，返回NULL。
        PARTITION BY customerNumber #根据customerNumber分组
        ORDER BY orderDate ) nextOrderDate #根据orderDate排序
FROM 
    orders
INNER JOIN customers USING (customerNumber); 

结果输出：
+------------------------------------+------------+---------------+
| customerName                       | orderDate  | nextOrderDate |
+------------------------------------+------------+---------------+
| Atelier graphique                  | 2013-05-20 | 2014-09-27    |
| Atelier graphique                  | 2014-09-27 | 2014-11-25    |
| Atelier graphique                  | 2014-11-25 | NULL          |
| Signal Gift Stores                 | 2013-05-21 | 2014-08-06    |
| Signal Gift Stores                 | 2014-08-06 | 2014-11-29    |
| Signal Gift Stores                 | 2014-11-29 | NULL          |
| Australian Collectors, Co.         | 2013-04-29 | 2013-05-21    |
| Australian Collectors, Co.         | 2013-05-21 | 2014-02-20    |
| Australian Collectors, Co.         | 2014-02-20 | 2014-11-24    |
| Australian Collectors, Co.         | 2014-11-24 | 2014-11-29    |
| Australian Collectors, Co.         | 2014-11-29 | NULL          |
| La Rochelle Gifts                  | 2014-07-23 | 2014-10-29    |
| La Rochelle Gifts                  | 2014-10-29 | 2015-02-03    |
| La Rochelle Gifts                  | 2015-02-03 | 2015-05-31    |
| La Rochelle Gifts                  | 2015-05-31 | NULL          |
| Baane Mini Imports                 | 2013-01-29 | 2013-10-10    |
| Baane Mini Imports                 | 2013-10-10 | 2014-10-15    |
...

例题二：使用LAG()和LEAD()的结果一样

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

输入表格：
+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

SQL语句:
SELECT DISTINCT Num as ConsecutiveNums
FROM(
    SELECT Id,Num,
    Lead(Num,1)OVER() as num_1, #选择指定行的向前一行的内容
    Lead(Num,2)OVER() as num_2  #选择指定行的向前两行的内容
    FROM Logs
) as c
WHERE c.Num = c.num_1 AND c.num_1 = c.num_2 #这三个数都要相等。

结果输出：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+