新手學SQL Server 2012「SQL視窗函數(SQL Windows Function)」(1)

by Derrick 10. 一月 2013 11:00

在先前的版本上,對於商業報表上常見的分析需求,例如:進一步的資料分群排名、「累加值(running totals)」等的統計運算,多半是利用SQL Server Reporting Services或Microsoft Excel等前台工具來完成。

前述的這些查詢需求,需以特定的資料區間為單位進行運算,而後進行逐筆的遞移來橫跨整個資料結果集,這種運算方式,因為具備逐筆滑動結果集視窗的特性,若要在後台的資料庫系統上,使用Transact-SQL來處理,這可能需要藉由數個複雜的資料操作,例如:使用資料表的「自我聯結(Self-joins) 」、暫存資料表、CTE或是「資料指標(Cursor)」等物件,進行組合出所需要的報表結果。

如今,在SQL Server 2012版本上,可以使用「SQL視窗(SQL Windows)」與「SQL視窗函數(SQL Windows Function)」的方式,來處理這類複雜的報表分析需求。

 

認識「SQL視窗」

在SQL Server 2012版本上,增強了「SQL視窗(SQL Windows)」的區間運算能力,讓你能夠逐筆操作與滑動結果集視窗,更容易對資料集合進行精確與複雜的存取作業。

在這次版本中,提供了數個「SQL視窗函數(SQL Windows Function)」,讓你可以在資料列與資料列彼此之間,進行繁複的運算,例如:進一步的資料分群排名、「移動平均值(moving averages)」、「累計彙總(cumulative aggregates)」、「累加值(running totals)」或是「每組前N個結果(a top N per group results)」、同期比較、成長率、統計運算等的查詢,這些在商業報表的分析上,皆是常見的需求。為了要達成前述的需求,你將需要學習撰寫查詢來定義所需的「SQL視窗」、結果集、資料列,並搭配使用OVER次子句來定義「視窗函數」的結果集,而無需使用複雜的資料表聯結、自我聯結等。

為了要運用Transact-SQL所提供的「SQL視窗」以及「視窗函數」的功能,你必須使用OVER次子句來建立以及處理操作結果集視窗。此外,我們可能需要使用PARTITION BY選項來建立「資料分割(partition)」,藉此進一步操作資料區間內的資料列應被如何執行運算,並且可以搭配「視窗框架(Window Frame)」選項設定起始與邊界的資料列。

因此,了解OVER字句、PARTITION BY選項以及「視窗框架」選項彼此之間的關係,會是使用「SQL視窗」的基石。請參考下圖1所示:

01_SQL視窗的元件

圖1:認識SQL視窗的元件

在圖1中,請參考以下的說明:

1. 在套用「視窗函數」之前,先由最外圈的OVER子句,來指定對查詢結果集進行相關的資料分群和排序方式。

2. 依據PARTITION BY子句的設定,將查詢結果集分成為數個「資料分割」,並將「視窗函數」套用至各個「資料分割」上。

3. 最後,還可以再利用ROW或RANGE子句,就是建立「視窗框架」,指定「資料分割」內的起始點和結束點。

請繼續參考後續更進一步的討論。

認識OVER子句

OVER子句是在套用相關的視窗函數之前,用於決定如何處理結果集內的「資料分割」與排序方式。因此,可以用來定義資料列所屬的「視窗框架」、「結果集」,並且搭配「視窗函數」來進一步資料處理。在使用OVER子句上,其包含了「資料分割」、排序以及「視窗框架」等應用。

OVER子句的語法如下:

OVER ( [ <PARTITION BY 子句> ]

[ <ORDER BY 子句> ]

[ <ROWS 或 RANGE 子句> ]

)

當然也可以單獨使用OVER子句,讓「視窗函數」可以套用處理每一筆資料列。若搭配PARTITION BY次子句一起使用,OVER子句將以各個「資料分割」區間為單位,各自處理所指定的作業。

認識視窗資料分割

將查詢結果集分成數個「資料分割(partition)」。而所使用的視窗函數,將會分別套用至每個「資料分割」,並且針對每個「資料分割」重新開始計算。此外,必須要先有使用OVER子句後,才能使用PARTITION BY子句。若是沒有使用「資料分割」功能,系統會將查詢結果集內的所有資料列視為單一群組。例如:對資料行CustomerID,使用PARTITION BY子句,執行分群組的作業。

舉例來說,請參考以下的範例程式碼片段:

 

<視窗函數>() OVER(PARTITION BY 產品類別)

上述的範例程式碼,使用PARTITION BY子句,依據產品類別為資料分群的區間單位,建立所需的視窗資料分割。

若是沒有使用PARTITION BY子句,ORDER BY子句會對整個結果集建立單一個「資料分割」,也就是將查詢結果集內的所有資料列視為單一群組。

認識視窗框架與排序

使用ROW或RANGE子句,可用於在各個「資料分割」內建立「視窗框架(Window frame)」,讓可以指定「資料分割」內要處理的資料列其起始點和結束點之位置。如此一來,就可以自行規範要存取第幾筆到第幾筆的資料列,或是跳躍去處理所指定的資料列,藉此進一步的操作資料列。若要使用「視窗框架」,必須先在OVER子句內,搭配使用ORDER BY次子句。

你可以將「視窗框架」,想像成如同移動視窗一般,而且可以讓你在結果集上,自行要指定處理的起始點和結束點之邊界進行滑動。

舉例來說,如果需要使用到「視窗框架」,對「資料分割」內的資料列由第一筆處理到目前這一筆(例如:建立一個移動視窗,來處理「累加值」),請參考以下的流程:

1. 先使用OVER子句,搭配PARTITON BY次子句來定義所需的「資料分割」。

2. 在OVER子句內,繼續定義ORDER BY次子句,設計是以何者為「第一筆資料列」。

3. 加入ROWS BETWEEN次子句,使用UNBOUNDED PRECEDING關鍵字來指定「視窗框架」是從「資料分割」的第一個資料列為起始點。最後再加入CURRENT ROW關鍵字來指定「視窗框架」是以目前的資料列作為結束點。

注意事項:

由於OVER子句所傳回的結果集,其並未設定排序,所以,可以利用ORDER BY次子句對「視窗框架」執行此項操作。

而一般所使用的ORDER BY子句,是用來對最後的結果集執行排序作業,決定其顯示順序,這兩者是不同的功用。

 

認識視窗函數

在SQL Server 2012版本所提供的「視窗函數」,可以應用在「SQL視窗」、結果集、資料列等地方,這都需要利用OVER子句來搭配使用這些「視窗函數」。依照其運算特性,可以分成為以下類型,請參考表格1所示:

視窗函數類型

可使用的函數

彙總(Aggregate)

包含有:AVG()、MIN()、SUM()、COUNT()、MAX()等彙總函數。

次序(Ranking)

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

統計分佈(Distribution)

包含有:CUME_DIST()、PERCENTILE_CONT()、PERCENTILE_DISC()、PERCENT_RANK()等統計分佈函數。

相對位移(Offset)

包含有:LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()等相對位移函數。

表格1:SQL視窗函數的分類

在表格1中,是將「視窗函數」依據其運算特性分成為四類,但是在SQL Server線上說明文件中,卻是將「統計分佈函數」與「相對位移函數」合併成為一類:「分析函數(Analytic functions)」。

在使用「SQL視窗」情境時,這些「視窗函數」都需要倚賴OVER子句來處理結果集,也可以進一步搭配「資料分割」以及「視窗框架」來操作細部的資料列。

 

認識視窗彙總函數

「視窗彙總函數」是類似於一般所使用的「彙總函數」。基本上,「彙總函數」會根據一組值來執行計算,再傳回單一值。但是在「視窗彙總函數」上,是使用OVER子句來操作結果集,而不是使用GROUP BY子句來對結果集執行分組查詢作業。

在支援「SQL視窗」上,「視窗彙總函數」包含了「資料分割」、排序、「視窗框架」等功能。使用「視窗彙總函數」時,不一定需要使用到排序,除非有需要使用「視窗框架」時,就應該搭配排序。

接下來,我們使用範例程來說明「視窗彙總函數」,請先執行以下範例程式碼1來建立範例資料表:CReport1,以及下圖1所示:

-- 建立範例資料表:CReport1

USE Northwind_Dev

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CReport1]') AND type in (N'U'))

DROP TABLE [dbo].[CReport1]

GO

CREATE TABLE [dbo].[CReport1](

[CustomerID] [char](5) NULL,

[OrderDate] [date] NULL,

[Subtotal] [money] NULL,

[ShipCity] [nvarchar](15) NULL

) ON [PRIMARY]

GO

INSERT dbo.CReport1

SELECT CustomerID , OrderDate, Subtotal, ShipCity

FROM Orders o INNER JOIN

(SELECT OrderID, SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS Subtotal

FROM dbo.[Order Details]

GROUP BY OrderID) od

ON o.OrderID = od.OrderID

ORDER BY 1

GO

-- 查詢資料表:CReport1,830 筆

SELECT * FROM dbo.CReport1

ORDER BY 1,2

GO

範例程式碼1:建立範例資料表:CReport1

02_查詢資料表:CReport1 [1]

圖2:查詢資料表:CReport1

若需求是依據資料行CustomerID為分群的單位,計算出每一位客戶的相關彙總查詢報表。

在先前的版本中,可能需要使用到多個「子查詢(SubQuery)」以及搭配彙總函數來達成需求,請參考以下的範例程式碼2,以及下圖3、4所示:

/*

使用「子查詢(SubQuery)」來顯示彙總報表

需求:依據資料行CustomerID為分群的單位,計算出每一位客戶的相關彙總查詢報表,顯示SUM、AVG、COUNT、MAX與MIN等彙總運算。

-- 成本:0.227324

*/

SELECT a.CustomerID, a.OrderDate, a.Subtotal,

(SELECT SUM(b.Subtotal) FROM dbo.CReport1 b

WHERE a.CustomerID =b.CustomerID GROUP BY b.CustomerID) N'總數',

(SELECT AVG(b.Subtotal) FROM dbo.CReport1 b

WHERE a.CustomerID =b.CustomerID GROUP BY b.CustomerID) N'平均',

(SELECT COUNT(b.Subtotal) FROM dbo.CReport1 b

WHERE a.CustomerID =b.CustomerID GROUP BY b.CustomerID) N'筆數',

(SELECT MAX(b.Subtotal) FROM dbo.CReport1 b

WHERE a.CustomerID =b.CustomerID GROUP BY b.CustomerID) N'最大',

(SELECT MIN(b.Subtotal) FROM dbo.CReport1 b

WHERE a.CustomerID =b.CustomerID GROUP BY b.CustomerID) N'最小'

FROM dbo.CReport1 a

GROUP BY a.CustomerID, a.OrderDate, a.Subtotal

GO

範例程式碼2:使用「子查詢(SubQuery)」搭配彙總函數

03_使用多個「子查詢(SubQuery)」以及搭配彙總函數 [1]

圖3:檢視執行結果集-使用「子查詢(SubQuery)」搭配彙總函數

04_使用「子查詢(SubQuery)」_執行計畫 [1]

圖4:檢視執行計畫-使用「子查詢(SubQuery)」搭配彙總函數

在範例程式碼2與圖3、4中,可以觀察到此複雜的子查詢其所耗用的子樹成本是:0.227324。

以下是改用「視窗彙總函數」的方式,請參考範例程式碼3以及圖5、6所示:

/*

下列範例會使用OVER子句來搭配「視窗彙總函數」,傳回查詢所有的資料列

在這個範例中,使用 OVER 子句比使用子查詢來衍生彙總值更有效率。

需求:依據資料行CustomerID為分群的單位,計算出每一位客戶的相關彙總查詢報表,顯示SUM、AVG、COUNT、MAX與MIN等彙總運算。

-- 成本:0.046992

*/

SELECT CustomerID, OrderDate, Subtotal,

SUM(Subtotal) OVER (PARTITION BY CustomerID) N'總數',

AVG(Subtotal) OVER (PARTITION BY CustomerID) N'平均',

COUNT(Subtotal) OVER (PARTITION BY CustomerID) N'筆數',

MAX(Subtotal) OVER (PARTITION BY CustomerID) N'最大',

MIN(Subtotal) OVER (PARTITION BY CustomerID) N'最小'

FROM dbo.CReport1

GO

範例程式碼3:使用OVER子句來搭配「視窗彙總函數」

 

05_檢視執行結果集-使用OVER子句來搭配「視窗彙總函數」 [1]

圖5:檢視執行結果集-使用OVER子句來搭配「視窗彙總函數」

06_檢視執行計畫-使用OVER子句來搭配「視窗彙總函數」 [1]

圖6:檢視執行計畫-使用OVER子句來搭配「視窗彙總函數」

在範例程式碼3與圖5、6中,可以觀察到改用「視窗彙總函數」方式來執行,其所耗用的子樹成本是:0.046992,比起先前所使用的複雜子查詢,其語法相對簡單,而且其耗用的子樹成本也是更加節省。

以下提供一個使用「視窗彙總函數」的範例,需求:依據資料行CustomerID為分群的單位,計算查詢出每一位客戶的總銷售額(SUM),以及其所佔用總銷售額的比率。請參考以下的範例程式碼4,以及下圖7所示:

/*

需求:依據資料行CustomerID為分群的單位,計算查詢出每一位客戶的總銷售額(SUM),以及其所佔用總銷售額的比率

*/

SELECT CustomerID, OrderDate, Subtotal,

SUM(Subtotal) OVER (PARTITION BY CustomerID) N'總銷售額',

Subtotal /SUM(Subtotal) OVER (PARTITION BY CustomerID)* 100 N'比率(%)'

FROM dbo.CReport1

GO

範例程式碼4:使用「視窗彙總函數」-計算查詢出總銷售額(SUM),以及其所佔用總銷售額的比率

07_檢視執行結果集-使用「視窗彙總函數」-計算查詢出總銷售額(SUM),以及其所佔用總銷售額的比率 [1]

圖7:檢視執行結果集-使用「視窗彙總函數」-計算查詢出總銷售額(SUM),以及其所佔用總銷售額的比率

結語

在本期文章中,介紹了認識「SQL視窗」、認識OVER子句、認識視窗資料分割、認識視窗框架與排序、認識視窗函數、以及說明如何使用視窗彙總函數等主題。在下一期的文章裡,我們將繼續討論其他視窗函數。

Tags:

SQL Server資料庫 | 陳俊宇Derrick Chen

評論 (48) -

cours de theatre
cours de theatre United States
2017/9/30 下午 06:12:34 #

Enjoyed every bit of your blog post. Awesome.

回覆

can ho vung tau
can ho vung tau United States
2017/10/7 上午 12:40:01 #

Thanks again for the article.Really looking forward to read more. Keep writing.

回覆

can ho bien vung tau
can ho bien vung tau United States
2017/10/9 下午 06:28:21 #

Really appreciate you sharing this article post.Really looking forward to read more. Much obliged.

回覆

Nathan Coombe
Nathan Coombe United States
2017/10/10 下午 10:24:20 #

Thanks so much for the article post.Thanks Again. Really Great.

回覆

Buy illegal backlinks
Buy illegal backlinks United States
2017/10/12 下午 09:07:07 #

Great article post.Really looking forward to read more. Want more.

回覆

look here
look here United States
2017/10/14 下午 04:23:20 #

This is one awesome post.Thanks Again.

回覆

dragon city hack latest version
dragon city hack latest version United States
2017/10/15 下午 03:52:26 #

I appreciate you sharing this article. Keep writing.

回覆

pop over to this site
pop over to this site United States
2017/10/17 下午 03:10:30 #

Thanks so much for the post.Thanks Again. Really Great.

回覆

sletrokor review
sletrokor review United States
2017/10/17 下午 08:42:26 #

I cannot thank you enough for the blog article. Really Great.

回覆

see this
see this United States
2017/10/19 下午 06:43:37 #

I am so grateful for your blog article.

回覆

vung tau melody
vung tau melody United States
2017/10/21 上午 03:59:28 #

Thanks for sharing, this is a fantastic article post.Really looking forward to read more. Will read on...

回覆

carte grise en ligne
carte grise en ligne United States
2017/10/21 上午 07:37:47 #

I really liked your blog article.Really thank you! Will read on...

回覆

website designing company in Delhi India
website designing company in Delhi India United States
2017/10/24 下午 02:32:12 #

Thanks-a-mundo for the post.Really looking forward to read more. Cool.

回覆

EZ Battery Reconditioning Review
EZ Battery Reconditioning Review United States
2017/10/30 上午 10:59:27 #

Awesome post. Cool.

回覆

scam
scam United States
2017/11/1 上午 11:13:12 #

Thank you for your blog.Really thank you! Will read on...

回覆

phenocal
phenocal United States
2017/11/1 下午 06:42:16 #

Really enjoyed this blog post.Really looking forward to read more. Cool.

回覆

phentaslim
phentaslim United States
2017/11/3 下午 02:25:34 #

I think this is a real great post.Much thanks again. Will read on...

回覆

avocat quebec
avocat quebec United States
2017/11/16 下午 08:55:34 #

Hey, thanks for the article.Really looking forward to read more. Really Cool.

回覆

swimwear
swimwear United States
2017/11/24 上午 12:29:20 #

A big thank you for your article.Thanks Again. Awesome.

回覆

Chad Boonswang and Jeffrey Goodman
Chad Boonswang and Jeffrey Goodman United States
2017/11/26 下午 08:33:51 #

I truly appreciate this article.Really looking forward to read more.

回覆

Chad Boonswang SEO
Chad Boonswang SEO United States
2017/11/27 上午 02:44:57 #

Really appreciate you sharing this blog post.Much thanks again. Really Great.

回覆

fake car wreckers
fake car wreckers United States
2017/11/29 下午 07:08:12 #

Really informative blog.Really thank you! Much obliged.

回覆

porno
porno United States
2017/12/1 下午 07:14:20 #

Thanks so much for the blog. Will read on...

回覆

commercial real estate loan
commercial real estate loan United States
2017/12/3 上午 07:25:06 #

Really enjoyed this blog post.Really thank you! Much obliged.

回覆

free porn app
free porn app United States
2017/12/5 下午 12:16:31 #

Very informative blog post.Really thank you! Great.

回覆

Major thankies for the article.Thanks Again. Awesome.

回覆

I am so grateful for your blog post.Much thanks again. Awesome.

回覆

Maryrose Idiart
Maryrose Idiart United States
2017/12/14 上午 11:55:20 #

This is one awesome article post.Much thanks again. Keep writing.

回覆

Hanukkah
Hanukkah United States
2017/12/15 上午 01:26:08 #

Thanks so much for the blog post.Really thank you! Keep writing.

回覆

tips lose weight
tips lose weight United States
2017/12/17 上午 02:53:23 #

Thank you ever so for you article post.Much thanks again.

回覆

Awesome article post.Much thanks again. Keep writing.

回覆

Enterprise
Enterprise United States
2017/12/17 下午 08:04:45 #

Major thankies for the post.Much thanks again. Cool.

回覆

canon drivers
canon drivers United States
2017/12/23 上午 06:09:46 #

Thanks-a-mundo for the post.Really thank you! Awesome.

回覆

Darwin Horan
Darwin Horan United States
2017/12/23 下午 04:38:12 #

Thanks for the article.Thanks Again. Much obliged.

回覆

Thanks for sharing, this is a fantastic article.Really thank you! Great.

回覆

hp driver
hp driver United States
2017/12/25 下午 06:11:25 #

Very informative article.Really thank you! Fantastic.

回覆

I really like and appreciate your article post.Thanks Again. Will read on...

回覆

A big thank you for your post.Thanks Again. Cool.

回覆

SOCCER HIGHLIGHTS
SOCCER HIGHLIGHTS United States
2017/12/26 下午 03:35:40 #

Major thanks for the blog article.Thanks Again.

回覆

canon drivers
canon drivers United States
2017/12/27 下午 10:20:57 #

I cannot thank you enough for the blog post.Really thank you! Awesome.

回覆

drivers hp
drivers hp United States
2018/1/2 下午 12:54:12 #

I cannot thank you enough for the blog post.Really thank you! Really Great.

回覆

like it
like it United States
2018/1/2 下午 09:02:47 #

Thank you ever so for you article.Really looking forward to read more. Really Cool.

回覆

printer drivers
printer drivers United States
2018/1/3 下午 12:15:00 #

Enjoyed every bit of your blog article.Really looking forward to read more.

回覆

best bitcoin casino
best bitcoin casino United States
2018/1/4 下午 11:07:19 #

I am so grateful for your article post. Keep writing.

回覆

hp driver
hp driver United States
2018/1/5 下午 07:49:26 #

Great, thanks for sharing this article.Really thank you! Fantastic.

回覆

FBA
FBA United States
2018/1/6 上午 11:54:11 #

Major thankies for the post.Really looking forward to read more. Really Cool.

回覆

新增評論




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






NET Magazine國際中文電子雜誌

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

月分類Month List