SQL Server 2012 的 T-SQL 新功能 – 新的資料分析函數(LEAD、LAG)

本文將介紹 SQL Server 2012 新的資料分析函數(LEAD、LAG)。

當您需要在 SQL Server 中利用 T-SQL 比較結果集的每一列跟前一列或後一列的差異時,在過去可能需要利用 CURSOR 搭配暫存資料表變數,或是透過遞迴 CTE 來達到這個效果,如今 SQL Server 2012 提供了兩個分析用的函數(LEAD、LAG)來讓您更容易進行 ROW LEVEL 資料比較。

  • 以下程式碼用來示範如何透過 LEAD 函數來計算每一列與後一列的 c2 欄位相差幾天:
   1:  declare @t table
   2:  (
   3:      c1 int identity
   4:      ,c2 date
   5:  )
   6:  
   7:  insert into @t (c2)
   8:  select '20120101' 
   9:  union all
  10:  select '20120201' 
  11:  union all
  12:  select '20120110' 
  13:  union all
  14:  select '20120221' 
  15:  union all
  16:  select '20120121' 
  17:  union all
  18:  select '20120203' 
  19:  
  20:  select c1,c2
  21:      ,LEAD(c2) OVER (ORDER BY c2) as next_c2
  22:      ,DateDiff(day,c2,LEAD(c2) OVER (ORDER BY c2)) as diff
  23:  from @t
  24:  order by c2

執行結果:

image

 

  • 以下程式碼用來示範如何透過 LAG  函數來計算每一列與前一列的 c2 欄位相差幾天:
   1:  declare @t table
   2:  (
   3:      c1 int identity
   4:      ,c2 date
   5:  )
   6:  
   7:  insert into @t (c2)
   8:  select '20120101' 
   9:  union all
  10:  select '20120201' 
  11:  union all
  12:  select '20120110' 
  13:  union all
  14:  select '20120221' 
  15:  union all
  16:  select '20120121' 
  17:  union all
  18:  select '20120203' 
  19:  
  20:  select c1,c2
  21:      ,LAG(c2) OVER (ORDER BY c2) as previous_c2
  22:      ,DateDiff(day,LAG(c2) OVER (ORDER BY c2),c2) as diff
  23:  from @t
  24:  order by c2

執行結果:

 

image

 

【參考資料】