SQLSERVER中LEAD和LAG函數

 　　SQL SERVER中LEAD和LAG函數

　　LEAD和LAG函數

　　LEAD

　　訪問相同結果集的後續行中的數據，而不使用 SQL Server 2012 中的自聯接。 LEAD 以當前行之後的給定物理偏移量來提供對行的訪問。 在 SELECT 語句中使用此分析函數可將當前行中的值與後續行中的值進行比較。

　　語法：LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )

　　scalar_expression，要返回的值基於指定的偏移量。 這是一個返回單個(標量)值的任何類型的表達式。scalar_expression 不能為分析函數

　　offset默認值為1， offset 可以是列、子查詢或其他求值為正整數的表達式，或者可隱式轉換為bigint。offset 不能是負數值或分析函數。

　　default默認值為NULL， offset 可以是列、子查詢或其他求值為正整數的表達式，或者可隱式轉換為bigint。offset不能是負數值或分析函數。

　　LAG

　　訪問相同結果集的先前行中的數據，而不使用 SQL Server 2012 中的自聯接。 LAG 以當前行之前的給定物理偏移量來提供對行的訪問。 在 SELECT 語句中使用此分析函數可將當前行中的值與先前行中的值進行比較。

　　下面看一組SQL語句：

　　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 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

　　結果前半部分：

　　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 40 50 50 50 NULL

　　6 50 NULL NULL 0 NULL

　　LEAD(score)over(order by score)與LEAD(score,1)over(order by score)相同，基於SCORE升序取下一個score。

　　LEAD(score,Y)over(order by score)公式：按score升序排列第X行計算LEAD(score,Y)over(order by score)為第X+Y行的值。

　　對第X行求LEAD(score)over(order by score)，值為第X+1行的值。見列nextscore1和nextscore2。

　　對第X行求LEAD(score，2)over(order by score)，值為第X+2行的值。見列nextscore4。

　　對於第6行，由於沒有第7行數據，所以LEAD求出的值為NULL。如果指定默認值，則返回指定默認值。，如LEAD(score,1,0)最後一行返回0。見netsocre3列。

　　結果後半部分：

　　rownum score previousscore1 previousscore2 previousscore3 previousscore4

　　1 NULL NULL NULL 0 NULL

　　2 10 NULL NULL NULL NULL

　　3 20 10 10 10 NULL

　　4 30 20 20 20 10

　　5 40 30 30 30 20

　　6 50 40 40 40 30

　　跟LEAD非常相似，只是LAG是往前求值。