新手學SQL Server 2012「SQL視窗函數(SQL Windows Function)」(4),以LAG()和LEAD()視窗相對位移函數為例

by Derrick 12. 三月 2013 11:00

前言

在前一篇文章中,介紹了認識視窗相對位移函數、認識FIRST_VALUE、認識LAST_VALUE等主題。

在本次文章中,將繼續討論視窗相對位移函數,包含有:ROWS或RANGE子句、LAG()以及LEAD()等主題。

認識ROWS或RANGE子句


在使用「視窗框架」時,需要指定ROWS或RANGE子句,這可以用於設定資料分割內的起始點和結束點,藉此能夠進一步操作資料分割內的資料列,指定目前資料列與其他相關資料列之間的比較運算。

而ROWS或RANGE子句的預設範圍是:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,接下來討論各個引數的意義:

  • ROWS子句

可以用於限制資料分割內的資料列,其方法是:指定目前資料列之前或之後的固定資料列數。而前後的資料列是根據ORDER BY子句的順序來定義。也就是說,ROWS子句適用於需要明確取得前後幾筆資料列來操作運算的環境。

  • RANGE子句

可以用於以邏輯方式來限制資料分割內的資料列,其方法是:指定與目前資料列的值相關的值範圍。而前後的資料列是根據ORDER BY子句的順序來定義。

  • 在使用ROWS或RANGE子句時,必須要指定ORDER BY子句。若是在ORDER BY子句內,包含了多個順序運算式,在使用CURRENT ROW FOR RANGE來判斷目前資料列時,會考量在ORDER BY子句清單內的所有資料列。
  • UNBOUNDED PRECEDING

指定此視窗是從資料分割的第一個資料列為起始點。UNBOUNDED PRECEDING只能用於指定視窗的起始點。

  • PRECEDING

需要搭配使用<指定不帶正負號的值>來一起設定,用於指定以目前資料列的前面多少筆資料列為起始點,這項引數不能用在RANGE子句上。

  • CURRENT ROW

可以用於指定視窗是以目前資料列作為起始點或是結束點,這可以在ROWS或RANGE子句內一起使用。

  • BETWEEN <繫結的視窗框架 > AND <繫結的視窗框架 >

可以用於指定視窗的起始點以及結束點之邊界,在指定時,結束點的值不能夠小於起始點的值。這可以在ROWS或RANGE子句內一起使用。

  • UNBOUNDED FOLLOWING

指定此視窗是在資料分割的最後一個資料列作為結束點。UNBOUNDED FOLLOWING只能用於指定視窗的結束點。

  • FOLLOWING

需要搭配使用<指定不帶正負號的值>來一起設定,用於指定要取目前資料列後面的資料列之筆數。若以FOLLOWING指定為視窗起始點時,則其結束點也需要使用FOLLOWING。這項引數不能用在RANGE子句上。

以下列舉數個範例來說明:

  • 以「視窗框架」:ROWS BETWEEN 2 PRECEDING AND CURRENT ROW為例,這是指定一個視窗,此視窗是從目前資料列前面的第二個資料列作為起始點,而以目前資料列作為結束點,此視窗函數可以操作的資料列視窗大小為三筆資料列。
  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:此為預設值,這是指定一個視窗,此視窗從資料分割的第一個資料列作為起始點,而以資料分割的目前資料列作為結束點。
  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING:這是指定一個視窗,此視窗從資料分割的目前資料列作為起始點,而以資料分割的最後一個資料列作為結束點。
  • ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING:這是指定一個視窗,此視窗是從目前資料列後面的第二個資料列作為起始點,而以目前資料列後面的第十個資料列作為結束點。

延續先前範例程式碼26與其需求為例,若要正確的使用LAST_VALUE視窗相對位移函數,需要加入ROWS或RANGE子句來指定「視窗框架」,可以改寫為以下的範例程式碼,並請參考下圖34、35所示:

-- 使用 LAST_VALUE()視窗相對位移函數,加入ROWS或RANGE子句來指定「視窗框架」:用於指定其起始點和結束點,以進一步限制資料分割中的資料列。

-- 需求:以每一位客戶為資料分割單位,依據訂單日期(OrderDate)邏輯排序,找出最近一次的採購金額(Subtotal),並且對比到此客戶的每一筆採購金額資料。

-- 01_指定「視窗框架」引數:ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- 估計的子樹成本:0.046992

SELECT CustomerID, OrderDate, Subtotal,

LAST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) N'最近一次的採購金額(LAST_VALUE)'

FROM dbo.CReport1

ORDER BY CustomerID, OrderDate

GO

-- 02_指定「視窗框架」引數:RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- 估計的子樹成本:0.046992

SELECT CustomerID, OrderDate, Subtotal,

LAST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) N'最近一次的採購金額(LAST_VALUE)'

FROM dbo.CReport1

ORDER BY CustomerID, OrderDate

GO

範例程式碼27:認識LAST_VALUE()視窗相對位移函數,指定「視窗框架」引數:BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

image

圖34:使用LAST_VALUE()視窗相對位移函數,指定「視窗框架」引數:BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

image

圖35:使用LAST_VALUE()視窗相對位移函數,指定「視窗框架」引數:BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -檢視執行計畫

在範例程式碼27中,在使用LAST_VALUE()視窗相對位移函數,傳回排序結果集中的最後一個值。應該明確加入ROWS或RANGE子句來指定「視窗框架」引數為:BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,這是指定一個視窗,此視窗是從資料分割的第一個資料列作為起始點,而以資料分割的最後一個資料列作為結束點。

在使用LAST_VALUE()視窗相對位移函數時,也可以指定「視窗框架」引數為:BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING,這是指定一個視窗,此視窗從資料分割的目前資料列作為起始點,而以資料分割的最後一個資料列作為結束點。

請參考以下範例程式碼28以及下圖36所示:

-- 使用 LAST_VALUE()視窗相對位移函數,加入ROWS或RANGE子句來指定「視窗框架」:用於指定其起始點和結束點,以進一步限制資料分割中的資料列。

-- 需求:以每一位客戶為資料分割單位,依據訂單日期(OrderDate)邏輯排序,找出最近一次的採購金額(Subtotal),並且對比到此客戶的每一筆採購金額資料。

-- 01_指定「視窗框架」引數:ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- 估計的子樹成本:0.057764

SELECT CustomerID, OrderDate, Subtotal,

LAST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) N'最近一次的採購金額(LAST_VALUE)'

FROM dbo.CReport1

ORDER BY CustomerID, OrderDate

GO

-- 02_指定「視窗框架」引數:RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- 估計的子樹成本:0.057681

SELECT CustomerID, OrderDate, Subtotal,

LAST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) N'最近一次的採購金額(LAST_VALUE)'

FROM dbo.CReport1

ORDER BY CustomerID, OrderDate

GO

範例程式碼28:認識LAST_VALUE()視窗相對位移函數,指定「視窗框架」引數:BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

image

圖36:使用LAST_VALUE()視窗相對位移函數,指定「視窗框架」引數:BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -檢視執行計畫

在範例程式碼27、28中,在使用LAST_VALUE()視窗相對位移函數上,分別指定了兩種不同的「視窗框架」引數:

  • BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,其估計的子樹成本為:0.046992。
  • BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING,其估計的子樹成本為:0.057764。

整合FIRST_VALUE()與LAST_VALUE()這兩個視窗相對位移函數,分別求取最近一次的採購金額以及兩者的差異值,請參考以下範例程式碼29以及下圖37所示:

-- 使用 LAST_VALUE()視窗相對位移函數,加入ROWS或RANGE子句來指定「視窗框架」:用於指定其起始點和結束點,以進一步限制資料分割中的資料列。

-- 需求:以每一位客戶為資料分割單位,依據訂單日期(OrderDate)邏輯排序,找出最近一次的採購金額(Subtotal),並且對比到此客戶的每一筆採購金額資料。

-- 整合使用FIRST_VALUE()與LAST_VALUE()這兩個視窗相對位移函數,分別求取最近一次的採購金額以及兩者的差異值

SELECT CustomerID, OrderDate, Subtotal,

FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS N'最近一次的採購金額,取FIRST_VALUE',

(Subtotal - FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) AS N'與FIRST_VALUE的差異值',

LAST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS N'最近一次的採購金額,取LAST_VALUE',

(Subtotal - LAST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) AS N'與LAST_VALUE的差異值'

FROM dbo.CReport1

ORDER BY CustomerID, OrderDate

GO

範例程式碼29:整合使用FIRST_VALUE()與LAST_VALUE()這兩個視窗相對位移函數

image

圖37:整合使用FIRST_VALUE()與LAST_VALUE()這兩個視窗相對位移函數

以下再舉數個例子來說明FIRST_VALUE()與LAST_VALUE()視窗相對位移函數:

需求:查詢每一季的消費資料,並且計算最早一季以及最晚一季之間的差異值,請參考以下範例程式碼30以及下圖38與39所示:

-- 01_查詢每一季的消費資料

SELECT CustomerID, Year, Quarter, SUM(Subtotal) 'SubQuarter'

FROM

(

SELECT CustomerID, DATEPART(YEAR, OrderDate) 'Year', DATEPART(QUARTER, OrderDate) 'Quarter', Subtotal

FROM dbo.CReport1

) tq

GROUP BY CustomerID, Year, Quarter

ORDER BY 1,2

GO

-- 02_需求:查詢每一季的消費資料,並且計算最早一季以及最晚一季之間的差異值。

-- 使用FIRST_VALUE()與LAST_VALUE()視窗相對位移函數

WITH CTE1

AS (

SELECT CustomerID, Year, Quarter, SUM(Subtotal) 'SubQuarter'

FROM

(

SELECT CustomerID, DATEPART(YEAR, OrderDate) 'Year', DATEPART(QUARTER, OrderDate) 'Quarter', Subtotal

FROM dbo.CReport1

) tq

GROUP BY CustomerID, Year, Quarter)

SELECT CustomerID, Year, Quarter, SubQuarter N'本季消費',

FIRST_VALUE(SubQuarter) OVER (PARTITION BY CustomerID ORDER BY Year, Quarter) N'最早一季消費',

(SubQuarter - FIRST_VALUE(SubQuarter) OVER (PARTITION BY CustomerID ORDER BY Year, Quarter)) N'差異值',

LAST_VALUE(SubQuarter) OVER (PARTITION BY CustomerID ORDER BY Year, Quarter

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) N'最晚一季消費',

(SubQuarter - LAST_VALUE(SubQuarter) OVER (PARTITION BY CustomerID ORDER BY Year, Quarter

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) N'差異值'

FROM CTE1

ORDER BY CustomerID, Year, Quarter

GO

範例程式碼30:使用FIRST_VALUE()與LAST_VALUE()視窗相對位移函數:查詢每一季的消費資料,並且計算最早一季以及最晚一季之間的差異值

image

圖38:查詢每一季的消費資料

image

圖39:使用FIRST_VALUE()與LAST_VALUE()視窗相對位移函數:查詢每一季的消費資料,並且計算最早一季以及最晚一季之間的差異值

進一步使用ROWS子句,操作指定的前後幾筆資料列來運算。指定「視窗框架」引數:ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING,這會定義一個視窗,此視窗從資料分割的目前資料列後面之第二筆資料列為起始點,而以資料分割的最後一個資料列作為結束點。但在RANGE子句中不允許這項指定。請參考以下範例程式碼31以及下圖40與41所示:

-- 01_指定「視窗框架」引數:ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING

/*

這會定義一個視窗,此視窗從資料分割的目前資料列後面之第二筆資料列為起始點,而以資料分割的最後一個資料列作為結束點。

但在RANGE子句中不允許這項指定。

*/

SELECT CustomerID, OrderDate, Subtotal,

FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING) AS N'目前資料列後面的第二個資料列為起始點'

FROM dbo.CReport1

ORDER BY CustomerID, OrderDate

GO

-- 02_新增兩筆資料列

INSERT dbo.CReport1 VALUES

('ALFKI', '1998-07-07', NULL, 'Berlin'),

('ALFKI', '1998-08-08', '999', 'Berlin')

GO

-- 03_刪除這兩筆資料列

DELETE dbo.CReport1

WHERE (CustomerID ='ALFKI' AND OrderDate ='1998-07-07') OR

(CustomerID ='ALFKI' AND OrderDate ='1998-08-08')

GO

範例程式碼31:指定「視窗框架」引數:ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING

image

圖40:指定「視窗框架」引數:ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING

image

圖41:增加兩筆測試資料列,指定「視窗框架」引數:ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING

在範例程式碼31中,我們使用「視窗框架」引數:ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING,以此視窗從資料分割的目前資料列後面之第二筆資料列為起始點,也就是取得第三筆資料列來進行運算。

簡短語法

在使用ROWS或RANGE子句時,在語法<視窗框架範圍>內,若使用<window frame preceding>,則可以使用簡短語法來簡化程式碼,只需指定視窗框架的起始點,無須說明其視窗框架的結束點,而其將以CURRENT ROW為結束點。

舉例來說,若原本指定「視窗框架」引數:ROWS BETWEEN 1 PRECEDING AND CURRENT ROW,這是指定一個視窗,此視窗是從目前資料列前面的第一個資料列作為起始點,而以目前資料列作為結束點,此視窗函數可以操作的資料列視窗大小為兩筆資料列。可以使用簡短語法來改寫為:ROWS 1 PRECEDING,省略AND CURRENT ROW引數。

請參考以下範例程式碼32以及下圖42所示:

-- 指定「視窗框架」引數:ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

-- 簡短語法_指定「視窗框架」引數:ROWS 1 PRECEDING,省略AND CURRENT ROW引數。

SELECT CustomerID, OrderDate, Subtotal,

FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS N'前一筆資料列_FIRST_VALUE',

FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate

ROWS 1 PRECEDING) '簡短語法_前一筆資料列_FIRST_VALUE'

FROM dbo.CReport1

ORDER BY CustomerID, OrderDate

GO

範例程式碼32:指定「視窗框架」引數:簡短語法

image

圖42:指定「視窗框架」引數:簡短語法

注意事項

使用ROWS或RANGE子句來進行資料操作運算時,以指定「視窗框架」引數:ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING為例,這是指定一個視窗,此視窗是從目前資料列前面的第一個資料列作為起始點,而以目前資料列後面的第一個資料列作為結束點,此視窗函數可以操作的資料列視窗大小為三筆資料列。

若再搭配使用FIRST_VALUE()視窗相對位移函數來操作資料列,而目前資料列就是第一筆資料列時,也就是說,在此前面並沒有資料列,將會改取目前資料列之值來回傳。但這在實際的資料運算上是會有影響的,以前述的例子來說明,若是搭配AVG()平均值函數來運算時,其分母也會被減掉一個後來運算,平均值計算所使用的分母,也會有基數不正同的問題產生;這在使用LAST_VALUE()視窗相對位移函數來操作傳回排序結果集中的最後一個值時,也是會有前述的問題發生。請參考以下範例程式碼33以及下圖43所示:

-- 01_查詢每一個月的消費資料

SELECT CustomerID, Year, Month, SUM(Subtotal) 'SubMonth'

FROM

(

SELECT CustomerID, DATEPART(YEAR, OrderDate) 'Year', DATEPART(MONTH, OrderDate) 'Month', Subtotal

FROM dbo.CReport1

) tq

GROUP BY CustomerID, Year, Month

ORDER BY 1,2

GO

-- 02_指定「視窗框架」引數:ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

/*

使用FIRST_VALUE()與LAST_VALUE()視窗相對位移函數

指定「視窗框架」引數:ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

查詢運算:

本月消費、前一個月消費、下一個月消費以及每三個月的平均消費

遭遇問題:

若前一個月或是下一個月是沒有資料時,將會改以目前的資料列之值來回傳,這樣在實際運算上,是有問題的。

而平均值計算所使用的分母,也會有基數不正同的問題產生。

*/

WITH CTE1

AS (

SELECT CustomerID, Year, Month, SUM(Subtotal) 'SubMonth'

FROM

(

SELECT CustomerID, DATEPART(YEAR, OrderDate) 'Year', DATEPART(MONTH, OrderDate) 'Month', Subtotal

FROM dbo.CReport1

) tq

GROUP BY CustomerID, Year, Month)

SELECT CustomerID, Year, Month, SubMonth AS N'本月消費',

FIRST_VALUE(SubMonth) OVER (PARTITION BY CustomerID ORDER BY Year, Month

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS N'前一個月消費',

LAST_VALUE(SubMonth) OVER (PARTITION BY CustomerID ORDER BY Year, Month

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS N'下一個月消費',

AVG(SubMonth) OVER (PARTITION BY CustomerID ORDER BY Year, Month

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS N'每三個月的平均消費'

FROM CTE1

ORDER BY CustomerID, Year, Month

GO

範例程式碼33:指定「視窗框架」引數:簡短語法

image

圖43:沒有資料時,將會改以目前的資料列之值來回傳

在圖43中,以第一筆資料列來說明:

「本月消費」是:814.50,由於「前一個月消費」是沒有資料的,系統將會以目前資料列之值來回傳,而此筆資料列的「每三個月的平均消費」之分母基數會減去一,也就是以二為基數來計算:

  • 若分母為三時:(814.50 + 814.50 + 1208.00)/3 = 945.666666。
  • 若分母為二時:(814.50 + 1208.00)/2 = 1011.250000,此為指定「視窗框架」引數為:ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING的計算方式。


認識LAG

LAG視窗相對位移函數,可以用來操作目前資料列與指定的前面第幾筆資料列進行運算,傳回指定位移資料列的值或是預設值。也就是說,使用此相對位移函數,可以用來比較目前資料列的值,與前第幾筆資料列的值,而無需使用資料表的自我聯結方式來處理。

語法如下:

LAG ( scalar_expression [,offset] [,default])

OVER ( [ partition by 子句 ] order by子句 )

使用的引數:

  • scalar_expression:根據指定的相對位移值,所要傳回的資料值。這可以是傳回資料行、任何單一(純量)值的運算式,但不可以是其他分析函數。
  • offset:指定相對位移值,以目前資料列為基底,指定要向前取第幾筆資料列的數目。若未指定,其預設值是1。此相對位移值可以是資料行、子查詢、或是其他的運算式,但不可以是負值或分析函數。
  • default:若依據offset相對位移值所取回的scalar_expression之值是NULL時,將回傳會NULL值或是指定的預設值。如果沒有指定預設值,則會傳回NULL值。此default可以是資料行、子查詢、或是其他的運算式,但不可以是分析函數。此外,default的資料類型需要與scalar_expression相容。
  • PARTITON BY:將FROM子句產生的結果集,依據PARTITON BY分割為函數所要套用的資料分割群組。若未指定,此函數會將查詢結果集的所有資料列視為單一群組。
  • ORDER BY:在套用視窗相對位移函數之前,先使用order by子句來指定資料的邏輯排列順序,這是必要項目。

接下來,使用範例程式碼來說明LAG視窗相對位移函數的使用方式。

需求:依據訂單日期排序,以每一客戶為資料分割單位,找出要向前取的第幾筆資料列之值。

調整LAG視窗相對位移函數的引數:offset與default,了解其運作方式,請參考範例程式碼34以及下圖44、45所示:

/*

部分語法:LAG (scalar_expression [,offset] [,default])

offset:若是沒有指定offset,則相對位移值的預設值是1。

default:若依據offset相對位移值所取回的scalar_expression之值是NULL時,將回傳會NULL值或是指定的預設值。如果沒有指定預設值,則會傳回NULL值。

*/

-- 01_認識LAG視窗相對位移函數,向前取第一筆資料列

SELECT CustomerID, OrderDate, Subtotal,

LAG(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal)',

LAG(Subtotal, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 1)',

LAG(Subtotal, 1, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 1, 0)',

LAG(Subtotal, 1, 99999) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 1, 99999)',

LAG(Subtotal, 1, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 1, Subtotal)'

FROM dbo.CReport1

GO

-- 02_認識LAG視窗相對位移函數,向前取第二或第三筆資料列

SELECT CustomerID, OrderDate, Subtotal,

LAG(Subtotal, 2, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 2, 0)',

LAG(Subtotal, 2, 99999) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 2, 99999)',

LAG(Subtotal, 2, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 2, Subtotal)',

LAG(Subtotal, 3, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 3, 0)',

LAG(Subtotal, 3, 99999) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 3, 99999)',

LAG(Subtotal, 3, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LAG(Subtotal, 3, Subtotal)'

FROM dbo.CReport1

GO

範例程式碼34:認識LAG視窗相對位移函數

image

圖44:認識LAG視窗相對位移函數:向前取第一筆資料列

image

圖45:認識LAG視窗相對位移函數:向前取第二或第三筆資料列

在圖44中,第一筆資料列為例來說明:

  • LAG(Subtotal):沒有指定offset,則相對位移值的預設值是1,但沒有指定預設值。由於沒有已經前一筆資料列,所以傳回NULL值。
  • LAG(Subtotal, 1):指定offset相對位移值為1,但沒有指定預設值。由於沒有已經前一筆資料列,所以傳回NULL值。
  • LAG(Subtotal, 1, 0) :指定offset相對位移值為1,並且指定預設值為0。由於沒有已經前一筆資料列,所以傳回0值。
  • LAG(Subtotal, 1, 99999):指定offset相對位移值為1,並且指定預設值為99999。由於沒有已經前一筆資料列,所以傳回99999值。
  • LAG(Subtotal, 1, Subtotal):指定offset相對位移值為1,並且指定預設值為Subtotal,也就是與scalar_expression相同。由於沒有已經前一筆資料列,所以傳回目前資料列Subtotal的值。

以下提供一個使用LAG視窗相對位移函數的範例,需求是:依據訂單日期排序,以每一客戶為資料分割單位,找出要向前取的第幾筆資料列之值,並且計算其相比的差異值與成長率。請參考以下的範例程式碼35,以及下圖46、47以及48所示:

-- 01_使用LAG視窗相對位移函數:與前第幾筆資料列相比的差異

SELECT CustomerID, OrderDate, Subtotal,

LAG(Subtotal, 1, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate) N'前一筆',

(Subtotal - LAG(Subtotal, 1, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) N'與前一筆相比的差異值',

(Subtotal - LAG(Subtotal, 2, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) N'與前兩筆相比的差異值'

FROM dbo.CReport1

GO

-- 02_使用LAG視窗相對位移函數:與前一個月相比的差異

WITH CTE1

AS (

SELECT CustomerID, Year, Month, SUM(Subtotal) 'SubMonth'

FROM

(

SELECT CustomerID, DATEPART(YEAR, OrderDate) 'Year', DATEPART(MONTH, OrderDate) 'Month', Subtotal

FROM dbo.CReport1

) tq

GROUP BY CustomerID, Year, Month),

CTE2

AS (

SELECT CustomerID, Year, Month, SubMonth,

LAG(SubMonth, 1, 0) OVER (PARTITION BY CustomerID ORDER BY Year, Month) 'PreviousMonth'

FROM CTE1

)

SELECT CustomerID, Year, Month, SubMonth N'本月',PreviousMonth N'前一個月',

(SubMonth - PreviousMonth) N'前一個月相比的差異值',

(SubMonth - PreviousMonth) / IIF( PreviousMonth = 0, NULL, PreviousMonth) * 100 N'與前一個月相比的成長率(%)'

FROM CTE2

GO

-- 03_使用LAG視窗相對位移函數:與前一季相比的差異

WITH CTE1

AS (

SELECT CustomerID, Year, Quarter, SUM(Subtotal) 'SubQuarter'

FROM (

SELECT CustomerID, DATEPART(YEAR, OrderDate) 'Year', DATEPART(QUARTER, OrderDate) 'Quarter', Subtotal

FROM dbo.CReport1 ) tq

GROUP BY CustomerID, Year, Quarter) ,

CTE2

AS (

SELECT CustomerID, Year, Quarter, SubQuarter,

LAG(SubQuarter, 1, 0) OVER (PARTITION BY CustomerID ORDER BY Year, Quarter) 'PreviousQuarter'

FROM CTE1

)

SELECT CustomerID, Year, Quarter, SubQuarter N'本季',PreviousQuarter N'前一季',

(SubQuarter - PreviousQuarter) N'前一季相比的差異值',

(SubQuarter - PreviousQuarter) / IIF( PreviousQuarter = 0, NULL, PreviousQuarter) * 100 N'與前一季相比的成長率(%)'

FROM CTE2

GO

範例程式碼35:使用LAG視窗相對位移函數

image

圖46:使用LAG視窗相對位移函數:與前第幾筆資料列相比的差異

image

圖47:使用LAG視窗相對位移函數:與前一個月相比的差異

image

圖48:使用LAG視窗相對位移函數:與前一季相比的差異



認識LEAD


LEAD視窗相對位移函數,可以用來操作目前資料列與指定的後面第幾筆資料列進行運算,傳回指定位移資料列的值或是預設值。也就是說,使用此相對位移函數,可以用來比較目前資料列的值,與後面第幾筆資料列的值,而無需使用資料表的自我聯結方式來處理。

語法如下:

LEAD ( scalar_expression [,offset] [,default])

OVER ( [ partition by 子句 ] order by子句 )

由於LAG與LEAD這兩個視窗相對位移函數,一個是取前面第幾筆資料列的值,另外一個是取後面第幾筆資料列的值,互為反向函數,關於引數說明部分請參考LAG視窗相對位移函數小節所做的討論。

請參考以下的範例程式碼36、37,以及下圖49、50、51、52以及53所示:

/*

部分語法:LEAD (scalar_expression [,offset] [,default])

offset:若是沒有指定offset,則相對位移值的預設值是1。

default:若依據offset相對位移值所取回的scalar_expression之值是NULL時,將回傳會NULL值或是指定的預設值。如果沒有指定預設值,則會傳回NULL值。

*/

-- 01_認識LEAD視窗相對位移函數,向後取第一筆資料列

SELECT CustomerID, OrderDate, Subtotal,

LEAD(Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal)',

LEAD(Subtotal, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 1)',

LEAD(Subtotal, 1, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 1, 0)',

LEAD(Subtotal, 1, 99999) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 1, 99999)',

LEAD(Subtotal, 1, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 1, Subtotal)'

FROM dbo.CReport1

GO

-- 02_認識LEAD視窗相對位移函數,向後取第二或第三筆資料列

SELECT CustomerID, OrderDate, Subtotal,

LEAD(Subtotal, 2, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 2, 0)',

LEAD(Subtotal, 2, 99999) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 2, 99999)',

LEAD(Subtotal, 2, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 2, Subtotal)',

LEAD(Subtotal, 3, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 3, 0)',

LEAD(Subtotal, 3, 99999) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 3, 99999)',

LEAD(Subtotal, 3, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate) 'LEAD(Subtotal, 3, Subtotal)'

FROM dbo.CReport1

GO

範例程式碼36:認識LEAD視窗相對位移函數

image

圖49:認識LEAD視窗相對位移函數:向後取第一筆資料列

image

圖50:認識LEAD視窗相對位移函數:向後取第二或第三筆資料列

 

-- 01_使用LEAD視窗相對位移函數:與後面第幾筆資料列相比的差異

SELECT CustomerID, OrderDate, Subtotal,

LEAD(Subtotal, 1, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate) N'下一筆',

(Subtotal - LEAD(Subtotal, 1, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) N'與下一筆相比的差異值',

(Subtotal - LEAD(Subtotal, 2, Subtotal) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) N'與下面第二筆相比的差異值'

FROM dbo.CReport1

GO

-- 02_使用LEAD視窗相對位移函數:與下一個月相比的差異

WITH CTE1

AS (

SELECT CustomerID, Year, Month, SUM(Subtotal) 'SubMonth'

FROM

(

SELECT CustomerID, DATEPART(YEAR, OrderDate) 'Year', DATEPART(MONTH, OrderDate) 'Month', Subtotal

FROM dbo.CReport1

) tq

GROUP BY CustomerID, Year, Month),

CTE2

AS (

SELECT CustomerID, Year, Month, SubMonth,

LEAD(SubMonth, 1, 0) OVER (PARTITION BY CustomerID ORDER BY Year, Month) 'PreviousMonth'

FROM CTE1

)

SELECT CustomerID, Year, Month, SubMonth N'本月',PreviousMonth N'下一個月',

(SubMonth - PreviousMonth) N'與下一個月相比的差異值',

(SubMonth - PreviousMonth) / IIF( PreviousMonth = 0, NULL, PreviousMonth) * 100 N'與下一個月相比的成長率(%)'

FROM CTE2

GO

-- 03_使用LEAD視窗相對位移函數:與下一季相比的差異

WITH CTE1

AS (

SELECT CustomerID, Year, Quarter, SUM(Subtotal) 'SubQuarter'

FROM (

SELECT CustomerID, DATEPART(YEAR, OrderDate) 'Year', DATEPART(QUARTER, OrderDate) 'Quarter', Subtotal

FROM dbo.CReport1 ) tq

GROUP BY CustomerID, Year, Quarter) ,

CTE2

AS (

SELECT CustomerID, Year, Quarter, SubQuarter,

LEAD(SubQuarter, 1, 0) OVER (PARTITION BY CustomerID ORDER BY Year, Quarter) 'PreviousQuarter'

FROM CTE1

)

SELECT CustomerID, Year, Quarter, SubQuarter N'本季',PreviousQuarter N'下一季',

(SubQuarter - PreviousQuarter) N'與下一季相比的差異值',

(SubQuarter - PreviousQuarter) / IIF( PreviousQuarter = 0, NULL, PreviousQuarter) * 100 N'與下一季相比的成長率(%)'

FROM CTE2

GO

範例程式碼37:使用LEAD視窗相對位移函數

image

圖51:使用LEAD視窗相對位移函數:與後面第幾筆資料列相比的差異

image

圖52:使用LEAD視窗相對位移函數:與下一個月相比的差異

image

圖53:使用LEAD視窗相對位移函數:與下一季相比的差異



認識視窗次序函數

包含有:RANK()、NTILE()、DENSE_RANK()、ROW_NUMBER()等視窗次序函數。

建議參考以下部落格的文章:

SQL Server:認識「次序函數(Window Ranking Functions)」(1)

http://sharedderrick.blogspot.tw/2012/10/sql-serverwindow-ranking-functions1.html

SQL Server:認識「次序函數(Window Ranking Functions)」(2)

http://sharedderrick.blogspot.tw/2012/10/sql-serverwindow-ranking-functions2.html


結語

在本期文章中,介紹了認識視窗相對位移函數、認識ROWS或RANGE子句、認識LAG、認識LEAD等主題。使用這些相對位移函數,可以用來比較目前資料列的值,與前後第幾筆資料列的值,而無需使用資料表的自我聯結方式來處理。

Tags:

SQL Server資料庫 | 陳俊宇Derrick Chen

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List