SQL SERVER中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是往前求值。