本文將介紹 SQL Server 2012 新的資料分析函數(LEAD、LAG)。
當您需要在 SQL Server 中利用 T-SQL 比較結果集的每一列跟前一列或後一列的差異時,在過去可能需要利用 CURSOR 搭配暫存資料表變數,或是透過遞迴 CTE 來達到這個效果,如今 SQL Server 2012 提供了兩個分析用的函數(LEAD、LAG)來讓您更容易進行 ROW LEVEL 資料比較。
- 以下程式碼用來示範如何透過 LEAD 函數來計算每一列與後一列的 c2 欄位相差幾天:
1: declare @t table2: (
3: c1 int identity4: ,c2 date5: )
6:
7: insert into @t (c2)8: select '20120101'9: union all10: select '20120201'11: union all12: select '20120110'13: union all14: select '20120221'15: union all16: select '20120121'17: union all18: select '20120203'19:
20: select c1,c221: ,LEAD(c2) OVER (ORDER BY c2) as next_c222: ,DateDiff(day,c2,LEAD(c2) OVER (ORDER BY c2)) as diff23: from @t24: order by c2
執行結果:
- 以下程式碼用來示範如何透過 LAG 函數來計算每一列與前一列的 c2 欄位相差幾天:
1: declare @t table2: (
3: c1 int identity4: ,c2 date5: )
6:
7: insert into @t (c2)8: select '20120101'9: union all10: select '20120201'11: union all12: select '20120110'13: union all14: select '20120221'15: union all16: select '20120121'17: union all18: select '20120203'19:
20: select c1,c221: ,LAG(c2) OVER (ORDER BY c2) as previous_c222: ,DateDiff(day,LAG(c2) OVER (ORDER BY c2),c2) as diff23: from @t24: order by c2
執行結果:
【參考資料】