LEAD and LAG functions in SQLSERVER

LEAD and LAG functions in SQL SERVER

LEAD and LAG Functions

LEAD

Access Data in subsequent rows of the same result set, instead of using the self-join in SQL Server 2012. LEAD provides row access based on the given physical offset after the current row. Use this analysis function in the SELECT statement to compare the values in the current row with those in subsequent rows.

Syntax: LEAD (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 an Analysis Function

The default value of offset is 1. offset can be a column, subquery, or another positive integer expression, or can be implicitly converted to bigint. Offset cannot be a negative value or an analysis function.

The default value is NULL. offset can be a column, subquery, or other positive integer expressions, or can be implicitly converted to bigint. Offset cannot be a negative value or an analysis function.

LAG

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

The following describes 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 (order by 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 previusscore1

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

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

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

From test

First half of the result:

Rownum score nextscore1 nextscore2 nextscore3 nextscore4

1 NULL 10 10 10 20

2 10 20 20 30

3 20 30 30 40

4 30 40 40 40 50

5 40 50 50 50 NULL

6 50 NULL 0 NULL

LEAD (score) over (order by score) is the same as LEAD (score, 1) over (order by score). The next SCORE is obtained in ascending order based on the score.

LEAD (score, Y) over (order by score) formula: calculates LEAD (score, Y) over (order by score) in the ascending order of score in row X) is the value of row X + Y.

Evaluate LEAD (score) over (order by score) for row X. The value is the value of row X + 1. See nextscore1 and nextscore2.

Evaluate LEAD (score, 2) over (order by score) for row X. The value is the value of row X + 2. See nextscore4.

For 6th rows, the LEAD value is NULL because there are no 7th rows of data. If the default value is specified, the system returns the default value ., For example, the last line of LEAD (score,) returns 0. See the netsocre3 column.

Half of the result:

Rownum score previousscore1 previusscore2 previusscore3 previusscore4

1 NULL 0 NULL

2 10 NULL

3 20 10 10 10 NULL

4 30 20 20 10

5 40 30 30 20

6 50 40 40 40 30

It is very similar to LEAD, but LAG is a forward evaluate.