TSql Lead 和 Lag 函数

　　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                  

　　The data type of the specified scalar_expression. NULL is returned if scalar_expression is nullable or default is set to NULL.

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的方向是向后。

TSql Lead 和 Lag 函数

标签：