Lead and LAG functions in SQL Server

Lead and LAG functions in SQL Server

Lead and LAG functions

Lead

Accesses data in subsequent rows of the same result set, instead of using a self-join in SQL Server 2012. The lead provides access to the row with the given physical offset after the current line. Use this profiling function in a SELECT statement to compare the values in the current row with the values in subsequent rows.

Syntax: Leads (scalar_expression [, offset], [default]) over ([Partition_by_clause] order_by_clause)

Scalar_expression, the value to be returned is based on the specified offset. This is an expression of any type that returns a single (scalar) value. Scalar_expression cannot be a parse function

The offset default value is 1, and offset can be a column, subquery, or other expression that is evaluated as a positive integer, or it can be implicitly converted to bigint. Offset cannot be a negative value or analytic function.

Default defaults are null, and offset can be a column, subquery, or other expression that is evaluated as a positive integer, or it can be implicitly converted to bigint. Offset cannot be a negative value or analytic function.

LAG

Access data from previous rows of the same result set, instead of using a self-join in SQL Server 2012. LAG provides access to the row with the given physical offset before the current row. Use this profiling function in a SELECT statement to compare the values in the current row with the values in the previous row.

Let's look at a set of SQL statements:

With Test

As

(

Select NULL as Score

UNION All

Select 10

UNION All

Select 20

UNION All

Select 30

UNION All

Select 40

UNION All

Select 50

)

Select Row_number () over (score) as RowNum

, score

, lead (score) over (order by score) as Nextscore1

, lead (score,1) over (order by score) as Nextscore2

, lead (score,1,0) over (order by score) as Nextscore3

, lead (score,2) over (order by score) as Nextscore4

, LAG (Score) over (order by score) as Previousscore1

, LAG (score,1) over (order by score) as Previousscore2

, LAG (score,1,0) over (order by score) as Previousscore3

, LAG (score,2) over (order by score) as Previousscore4

From Test

The first half of the result:

RowNum score Nextscore1 Nextscore2 nextscore3 Nextscore4

1 NULL 10 10 10 20

2 10 20 20 20 30

3 20 30 30 30 40

4 30 40 40 40 50

5-A-M NULL

6 NULL NULL 0 NULL

The lead (score) over (the order by score) is the same as the lead (score,1) over, and a score is removed based on score ascending.

Lead (Score,y) over (order by score) formula: The value of Line x in ascending score to calculate the number of lead (score,y) over (order by score) as the X+y row.

For line X (score) over (the order by score), the value is the value of line x+1. See Columns Nextscore1 and Nextscore2.

For line X (score,2) over (the order by score), the value is the value of line x+2. See column Nextscore4.

For line 6th, the value of the lead is NULL because there is no 7th row of data. If you specify a default value, the specified default value is returned. , such as the last line of the lead (score,1,0) returns 0. See Netsocre3 column.

The latter part of the result:

RowNum score Previousscore1 Previousscore2 previousscore3 Previousscore4

1 null NULL NULL 0 NULL

2-null NULL NULL NULL

3 NULL

4 30 20 20 20 10

5 40 30 30 30 20

6 50 40 40 40 30

Very similar to the lead, but lag is a forward evaluation.