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

by Derrick 26. 二月 2013 11:00

前言

在前一篇文章中,介紹了認識視窗統計分佈函數、認識CUME_DIST、認識PERCENT_RANK、認識PERCENTILE_DISC、認識PERCENTILE_CONT等主題。

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

認識視窗相對位移函數

使用「視窗相對位移函數(Window Offset Functions)」,除了可以用於存取目前的資料列,也可以存取其他資料列。讓查詢程式可以操作資料列對資料列之間的比較運算,而無需使用資料表的自我聯結。

在SQL Server 2012版本中提供的視窗相對位移函數有:FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()等,但目前尚未支援NTH_VALUE相對位移函數。使用LAG和LEAD視窗相對位移函數,是可以操作當前的資料列及其位移量的資料列。使用FIRST_VALUE和LAST_VALUE視窗相對位移函數,可以用在操作「視窗框架」內的位移量之資料列。

也就是說,利用視窗相對位移函數,不但可以操作目前的資料列與其他資料列的比較計算,也可以處理「視窗框架(Window Frame)」內的第一筆或是最後一筆資料列。

注意事項

FIRST_VALUE和LAST_VALUE視窗相對位移函數是用在操作「視窗框架」內的位移量之資料列,重要的是要記得去指定「視窗框架」之選項,而其預設值是:RANGE BETWEEN UNBOUND PRECEDING AND CURRENT ROW。


認識FIRST_VALUE


FIRST_VALUE視窗相對位移函數可以用來傳回排序結果集中的第一個值。

其語法如下:

FIRST_VALUE ( scalar_expression )

OVER ( [ partition by 子句 ] order by 子句 [ rows 或 range 子句 ] )

使用的引數:

  • scalar_expression:要傳回的資料值。這可以是結果為單一值的資料行、子查詢或其他任意的運算式。但不可以是其他分析函數。
  • PARTITON BY:將FROM子句產生的結果集,依據PARTITON BY分割為函數所要套用的資料分割群組。若未指定,此函數會將查詢結果集的所有資料列視為單一群組。
  • ORDER BY:在套用視窗相對位移函數之前,先使用order by子句來指定資料的邏輯排列順序,這是必要項目。
  • ROWS或RANGE:用來指定「視窗框架」的起始點和結束點,藉此進一步限制資料分割中的資料列。

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

需求:找出每一位客戶最多的採購金額(Subtotal)資料,將此最多採購金額的資料,對比到此客戶的每一筆採購金額,並且還須計算目前此筆資料列與最多的採購金額之差異。

以下先示範先前版本的作法:使用資料表的自我聯結,請參考範例程式碼23以及下圖25、26以及27所示:

-- 需求:找出每一位客戶最多的採購金額(Subtotal)資料,將此最多的採購金額之資料,對比到此客戶的每一筆採購金額,並且還須計算目前此筆資料列與最多的採購金額之差異。

-- 示範先前版本的作法:使用資料表的自我聯結

-- 00_查詢資料表:CReport1,830 筆資料列

USE Northwind_Dev

GO

SELECT * FROM dbo.CReport1

ORDER BY 1

GO

-- 01_先找出每一位客戶的最多採購金額之資料,89 筆資料列

SELECT CustomerID, MAX(Subtotal) MaxSubtotal

FROM dbo.CReport1

GROUP BY CustomerID

GO

-- 02_使用資料表的自我聯結:

-- 將此最多採購金額的資料,對比到此客戶的每一筆採購金額,830 筆資料列

SELECT c.CustomerID, c.OrderDate, c.Subtotal, cr.MaxSubtotal

FROM dbo.CReport1 c INNER JOIN

(

SELECT CustomerID, MAX(Subtotal) MaxSubtotal

FROM dbo.CReport1

GROUP BY CustomerID ) cr

ON c.CustomerID = cr.CustomerID

GO

-- 03_最後,還須計算目前此筆資料列與最多的採購金額之差異

-- 耗用的子樹成本是:0.0480235

SELECT c.CustomerID, c.OrderDate, c.Subtotal, cr.MaxSubtotal, (c.Subtotal-cr.MaxSubtotal) N'差異量'

FROM dbo.CReport1 c INNER JOIN

(

SELECT CustomerID, MAX(Subtotal) MaxSubtotal

FROM dbo.CReport1

GROUP BY CustomerID ) cr

ON c.CustomerID = cr.CustomerID

ORDER BY CustomerID, Subtotal DESC

GO

範例程式碼23:使用自我聯結,操作每一筆資料,比對最多的採購金額,並且計算其差異

image

圖25:找出每一位客戶最多採購金額的資料,對比到此客戶的每一筆採購金額

image

圖26:找出每一位客戶最多採購金額的資料,對比到此客戶的每一筆採購金額,並計算差異量

image

圖27:資料表的自我聯結-檢視其執行計畫以及估計的子樹成本

在範例程式碼23與圖25、26以及27中,可以觀察到使用資料表的自我聯結方式來處理需求,其所估計的子樹成本是:0.0480235。

以下是改用FIRST_VALUE()視窗相對位移函數的方式來操作資料,請參考範例程式碼24以及圖28與29所示:

-- 01_使用 FIRST_VALUE()視窗相對位移函數,找出每一位客戶的最多採購金額之資料,並且對比到此客戶的每一筆採購金額

SELECT CustomerID, OrderDate, Subtotal,

FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY Subtotal DESC) MaxSubtotal

FROM dbo.CReport1

GO

-- 02_範例:搭配CTE,計算目前此筆資料列與最多的採購金額之差異

-- 耗用的子樹成本是:0.0338471

WITH CTE1

AS(

SELECT CustomerID, OrderDate, Subtotal,

FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY Subtotal DESC) MaxSubtotal

FROM dbo.CReport1 )

SELECT *, (CTE1.Subtotal/CTE1.MaxSubtotal * 100) N'差異量'

FROM CTE1

GO

範例程式碼24:認識FIRST_VALUE()視窗相對位移函數,操作每一筆資料,比對最多的採購金額,並且計算其差異

image

圖28:認識FIRST_VALUE()視窗相對位移函數:找出每一位客戶最多採購金額的資料,對比到此客戶的每一筆採購金額,並計算差異量

image

圖29:認識FIRST_VALUE()視窗相對位移函數-檢視其執行計畫以及估計的子樹成本

在範例程式碼24與圖28與29中,可以觀察到改用FIRST_VALUE()視窗相對位移函數方式來執行,其所耗用的子樹成本是:0.0338471,比起先前所使用的資料表自我聯結,其語法相對簡單,而且其耗用的子樹成本也是更加節省。

以下提供一個使用FIRST_VALUE()視窗相對位移函數的範例,需求:找出每一位客戶的最多以及最少採購金額之資料,並且對比到此客戶的每一筆採購金額。請參考以下的範例程式碼25,以及下圖30所示:

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

-- 找出每一位客戶的最多以及最少採購金額之資料,並且對比到此客戶的每一筆採購金額

SELECT CustomerID, OrderDate, Subtotal,

FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY Subtotal DESC) MaxSubtotal,

FIRST_VALUE(Subtotal) OVER (PARTITION BY CustomerID ORDER BY Subtotal ASC) MinSubtotal

FROM dbo.CReport1

GO

範例程式碼25:使用FIRST_VALUE()視窗相對位移函數,找出每一位客戶的最多以及最少採購金額之資料,並且對比到此客戶的每一筆採購金額

image

圖30:使用FIRST_VALUE()視窗相對位移函數:找出每一位客戶的最多以及最少採購金額之資料,並且對比到此客戶的每一筆採購金額

在範例程式碼25中,再使用一次FIRST_VALUE()視窗相對位移函數,並且改變排序方式,就達成操作資料的需求。


認識LAST_VALUE


LAST_VALUE視窗相對位移函數可以用來傳回排序結果集中的最後一個值。

其語法如下:

LAST_VALUE ( scalar_expression )

OVER ( [ partition by 子句 ] order by 子句 [ rows 或 range 子句 ] )

使用的引數:

  • scalar_expression:要傳回的資料值。這可以是結果為單一值的資料行、子查詢或其他任意的運算式。但不可以是其他分析函數。
  • PARTITON BY:將FROM子句產生的結果集,依據PARTITON BY分割為函數所要套用的資料分割群組。若未指定,此函數會將查詢結果集的所有資料列視為單一群組。
  • ORDER BY:在套用視窗相對位移函數之前,先使用order by子句來指定資料的邏輯排列順序,這是必要項目。
  • ROWS或RANGE:用來指定「視窗框架」的起始點和結束點,藉此進一步限制資料分割中的資料列。

注意事項:

  • 若是直接看SQL Server 2012線上說明對FIRST_VALUE()與LAST_VALUE視窗相對位移函數的解釋,會誤認為此兩個視窗相對位移函數應該是互為反向功能,請參考下圖31所示。
  • 但實則不然,這兩個視窗相對位移函數是用在操作「視窗框架」內的位移量之資料列,還會受到「視窗框架」選項的影響。因此,重要的是要記得去指定「視窗框架」之選項,而其預設值是:RANGE BETWEEN UNBOUND PRECEDING AND CURRENT ROW。

 

image

圖31:SQL Server 2012線上說明對FIRST_VALUE()與LAST_VALUE視窗相對位移函數的解釋

在圖31中,依據SQL Server 2012線上說明內的解釋:

  • LAST_VALUE:傳回 SQL Server 2012 中一組經過排序之值的最後一個值。
  • FIRST_VALUE:傳回 SQL Server 2012 中排序值集的第一個值。

但這應該還需加入對「視窗框架」的選項之說明,不然,十分容易被誤解。

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

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

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

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

-- 01_若使用 FIRST_VALUE()視窗相對位移函數,依據訂單日期(OrderDate)邏輯排序(DESC)

SELECT CustomerID, OrderDate, Subtotal,

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

FROM dbo.CReport1

GO

-- 02_若是使用 LAST_VALUE()視窗相對位移函數,依據訂單日期(OrderDate)邏輯排序(ASC),但結果集卻是?

-- 但沒有指定ROWS或RANGE子句

SELECT CustomerID, OrderDate, Subtotal,

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

FROM dbo.CReport1

GO

範例程式碼26:認識LAST_VALUE()視窗相對位移函數,找出每一位客戶,找出最近一次的採購金額(Subtotal),並且對比到此客戶的每一筆採購金額資料

image

圖32:使用FIRST_VALUE()視窗相對位移函數-找出最近一次的採購金額(Subtotal)

image

圖33:使用LAST_VALUE()視窗相對位移函數-找出最近一次的採購金額(Subtotal)

在上圖32與33中,可以觀察到使用FIRST_VALUE()視窗相對位移函數,可以取得正確的資料值。但使用LAST_VALUE視窗相對位移函數時,卻看似傳回目前此筆資料列的採購金額之值,為何會是如此呢?

這是因為受到「視窗框架」的影響,而其預設值是:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,這是指定一個視窗,此視窗從資料分割的第一個資料列作為起始點,而以資料分割的目前資料列作為結束點。所以,使用LAST_VALUE視窗相對位移函數將會是取得目前此筆資料列的採購金額之值。

接下來,我們需要進一步去指定ROWS或RANGE子句來操作資料列。


結語

在本期文章中,介紹了認識視窗相對位移函數、認識FIRST_VALUE、認識LAST_VALUE等主題。在下一期的文章裡,我們將繼續討論指定ROWS或RANGE子句以及其他SQL視窗函數。

Tags:

SQL Server資料庫 | 陳俊宇Derrick Chen

新增評論




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






NET Magazine國際中文電子雜誌

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

月分類Month List