语法
简单理解,选择第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 |
+-----------------+