MySQL LEAD() and LAG()用法及代码示例

MySQL中的LEAD()和LAG()函数用于获取其分区内任何行的前后值。这些函数称为非聚合窗口函数。

窗口函数是对分区或窗口的每一行执行操作的那些函数。这些函数为每个查询行生成结果，这与将它们分组并在单个行中生成结果的聚合函数不太可能。

• 发生操作的行称为当前行。
• 表现为当前行或使用哪个函数在当前行上不透明的行的集合称为“窗口”。

LAG()函数用于从当前行之前的行中获取值。

LEAD()函数用于从成功当前行的行中获取值。

用法:
对于LEAD()函数-

LEAD(expr, N, default) 
          OVER (Window_specification | Window_name)

对于LAG()函数-

LAG(expr, N, default) 
          OVER (Window_specification | Window_name)

函数中的N和默认参数是可选的。

参数：

1. expr:它可以是列或任何内置函数。
2. N:它是一个正值，它确定当前行之前/之后的行数。如果在查询中将其省略，则其默认值为1。
3. default:如果在当前行之前/之后没有行N行的情况下，它是函数返回的默认值。如果缺少，则默认为NULL。
4. OVER()：它定义了如何将行划分为组。如果OVER()为空，则函数使用所有行计算结果。
5. Window_specification:它由查询分区子句组成，该子句确定查询行的分区和排序方式。
6. Window_name:如果在查询的其他位置指定了窗口，则使用此Window_name对其进行引用。

例：
考虑一个“contest”表：

c_id	start_date	end_date
1	2015-02-01	2015-02-04
2	2015-02-02	2015-02-05
3	2015-02-03	2015-02-07
4	2015-02-04	2015-02-06
5	2015-02-06	2015-02-09
6	2015-02-08	2015-02-10
7	2015-02-10	2015-02-11

在上表中，“c_id”代表比赛ID，“start_date”和“end_date”分别代表比赛的开始日期和结束日期。

问题描述：我们必须找出下一场比赛将要结束的天数，即没有。两场比赛举行的日期。

查询：

Select c_id, start_date, end_date, 
        end_date - lead (start_date) 
        over (order by start_date) 
               + 1 as 'no_of_days' 
                   from contest;

在上面的查询中，“end_date”返回当前比赛的结束日期，线索(start_date)返回下一个比赛的开始日期。因此，这些日期之间的差加1将返回否。比赛将在几天后发生冲突。

此处，窗口规范由“order by”子句给出，该子句表示lead()函数将按其“start_date”的升序对表进行操作。由于它们不是分区子句，因此整个表被视为单个窗口。

输出：

c_id	start_date	end_date	no_of_days
1	2015-02-01	2015-02-04	3
2	2015-02-02	2015-02-05	3
3	2015-02-03	2015-02-07	4
4	2015-02-04	2015-02-06	1
5	2015-02-06	2015-02-09	2
6	2015-02-08	2015-02-10	1
7	2015-02-10	2015-02-11	NULL

由于比赛7(即c_id = 7)之后没有比赛。因此，lead(start_date)返回NULL值。

注意：LEAD()和LAG()函数始终与OVER()一起使用。缺少over子句将引发错误。