TSQL lead and LAG functions

Lead and Lag are two analytic functions in TSQL, and the order of execution is used to move forward and back the result set of a query after the SELECT statement. If the window function does not partition the result set of the query, the entire result set is treated as a partition, and if the window function partitions The result set of the query, then the lead and lag are the forward and post-move operations on the partition.

A window is defined by the OVER clause in the query Result set window or user-specified rowset, in fact, in the query result sets of SELECT, will meet the criteria of multiple data rows as a window, a select query result set can be divided into multiple windows, you can have only one window ; Each window is independent of the other windows, allowing the data to be manipulated separately from the window.

The first part: the lead is a forward operation, "lead provides access to a row at a given physical offset that follows the current row", which means moving the data window forward, moving the The offset is determined by the offset parameter, and the data outside the data window is specified by the default parameter after the window moves forward.

1,syntax

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

Over ([Partition_by_clause] order_by_clause)

Scalar_expression

The value to is returned based on the specified offset. It is an expression of any of the type that returns a single (scalar) value. Scalar_expression cannot is 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 is a column, subquery, or other expression, this evaluates to a positive integer or can be implicitly conver Ted to bigint. offset cannot is 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, and NULL is returned. default can is a column, subquery, or other expression, but it cannot is an analytic function. default must is type-compatible with scalar_expression.

Over ( [ partition_by_clause] order_by_clause)

Partition_by_clause divides the result set produced by the-clause into partitions to which of the function is applied. If not specified, the function treats any 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 was 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 are nullable or default is set to NULL.

2, example

Select *  from  order by code

3, use the lead to pan the window

The window specified by the 3.1,OVER clause is partitioned by code and sorted by code, so all data rows for code=1 are a window, and the value of code in the window is code=1,1,1.

The field of the lead function operation is code, which shifts the Code column in the window forward one bit, and the data outside the window is used instead. After panning, the window is code=1,1,-1

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

Select Lead (code,1,-1)throughorder by as Leadid,* from Dbo.test

3.2, the window function over the specified window, according to the name of the partition, according to code to sort, a partition within the code in the row after the smooth, can maintain the window value.

In the Name=c window, the value of code is sorted, and the function that has been fixed to the 2,3,3,LEAD clause is that the entire window moves forward.

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

Select Leads (code,1,-1)over byorder by as Leadid, *  from Dbo.test

For example: After partitioning, in the Name=c window, code =2,3,3, the window moves forward one bit, code=3,3,-1, because of default=-1, so the value of the window exceeds the use-one fill.

Part II: LAG

1,syntax

LAG (scalar_expression [, offset] [, default])
Over ([Partition_by_clause] order_by_clause)

Arguments is the same as the lead.

2, the same usage and lead, but the direction of movement is different, lag direction is backward.

TSQL lead and LAG functions