TSql Lead 和 Lag 函数

Lead 和 Lag 是TSql中的两个分析函数，执行顺序是在select语句之后，用于对查询的结果集进行前移和后移操作。如果窗口函数没有对查询的结果集分区，那么将整个结果集作为一个分区看待；如果窗口函数对查询的结果集分区，那么Lead和Lag就是对分区进行前移和后移操作。

窗口是由OVER 子句定义查询结果集内的窗口或用户指定的行集，其实就是在select的查询结果集中，将符合条件的多个数据行作为一个窗口，一个select的查询结果集可以划分为多个窗口，也可以仅有一个窗口；每一个窗口都独立于其他窗口，能够单独对窗口进行数据操作。

第一部分：Lead 是 前移操作，“LEAD provides access to a row at a given physical offset that follows the current row”，就是说，将数据窗口向前移动，移动的偏移量由Offset参数决定，窗口向前移动之后，超出数据窗口之外的数据由default参数指定。

1，Syntax

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 

    OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression                               

　　The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.

offset                               

　　The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

default                               

　　The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause)              

　　partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. When partition_by_clause is specified, it determines the order of the data in each partition. The order_by_clause is required.

Return Types                  

2，示例

select * 
from dbo.test 
order by code

3，使用 Lead 平移窗口

3.1，over子句指定的窗口是按照code进行分区，并按照code排序，所以code=1的所有数据行是一个窗口，在窗口中Code的值是code=1,1,1。

Lead 函数操作的字段是Code，将窗口中的Code列向前平移一位，窗口之外的数据使用-1来代替。平移之后，窗口是code=1，1，-1

Lead(code,1,-1)over(partition by code order by code)

select lead(code,1,-1)over(partition by code order by code) as leadid,*
from dbo.test

3.2，窗口函数over指定了窗口，按照name进行分区，按照code进行排序，一个分区内的code进过排顺之后，能够保持窗口值。

在 name=c 的窗口中，Code的值经过排序，已经固定为2，3，3，Lead子句的作用是整个窗口向前移动。

Lead(code,1,-1)over(partition by name order by code)

select Lead(code,1,-1)over(partition by name order by code) as leadid,*
from dbo.test

例如：分区之后，在 name=c 的窗口中，code =2，3，3， 该窗口向前平移一位，code=3，3，-1，由于default=-1，故超出窗口的数值使用-1来填充。

第二部分： Lag

1，Syntax

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Arguments 的和 Lead 相同

2，用法和Lead相同，只是移动的方向不同，Lag的方向是向后。