mysql中lead_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和默认参数是可选的。

参数：

expr:它可以是列或任何内置函数。

N:它是一个正值，它确定当前行之前/之后的行数。如果在查询中将其省略，则其默认值为1。

default:如果在当前行之前/之后没有行N行的情况下，它是函数返回的默认值。如果缺少，则默认为NULL。

OVER()：它定义了如何将行划分为组。如果OVER()为空，则函数使用所有行计算结果。

Window_specification:它由查询分区子句组成，该子句确定查询行的分区和排序方式。

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子句将引发错误。