︿
Top

2017年12月16日 星期六

[SQL Server] Rank Function / Aggregate Function + Over ( Partition By ... Order By ... ) 自我練習


前言

長久以來, 對於 T-SQL 的 OVER ( PARTITION BY ... ORDER BY ...) 的語法, 一直沒有很清楚; 最近終於有時間, 可以稍微 STUDY 一下.

在找資料的過程中, 發現了 德瑞克：SQL Server 學習筆記 有寫了 2 篇關於 Rank Function 的文章, 剛好可以導入到 OVER 與 PARTITION BY 的用法.

茲分為以下幾個部份及實作步驟進行說明:







1.. 建立測試資料


CREATE DATABASE Cookies;
GO


USE Cookies;
GO
-- ===========================
-- 移除 Table
-- ===========================
DROP TABLE IF EXISTS [dbo].[Products];
GO
DROP TABLE IF EXISTS [dbo].[Categories]
GO
-- ===========================
-- 建立 Table
-- ===========================
CREATE TABLE [dbo].[Categories]
( [Id]               INT                        NOT NULL
, [Name]             NVARCHAR(30)  NOT NULL
CONSTRAINT PK_Catgories PRIMARY KEY ( [Id] )
);
GO
CREATE TABLE [dbo].[Products]
( [Id]               INT                        NOT NULL
, [Name]             NVARCHAR(30)  NOT NULL
, [Category]  INT                        NOT NULL
, [Price]            INT                        NOT NULL
  CONSTRAINT PK_Products PRIMARY KEY ( [Id] )
, CONSTRAINT FK_Categories FOREIGN KEY ( [Category] )
                           REFERENCES [dbo].[Categories] ( [Id] )
);
GO
-- ===========================
-- 新增測試資料
-- ===========================
INSERT INTO [dbo].[Categories]
VALUES ( 1, N'餅乾類' )
,      ( 2, N'蛋糕類' )
,      ( 3, N'奶酪類' )
;
GO
INSERT INTO [dbo].[Products]
VALUES ( 1, N'夏威夷豆塔', 1, 50 )
,      ( 2, N'堅果塔', 1, 55 )
,      ( 3, N'養生堅果', 1, 60 )
,      ( 4, N'乳酪塔', 1, 60 )
,      ( 5, N'檸檬塔', 1, 60 )
,      ( 6, N'草莓塔', 1, 65 )
,      ( 7, N'輕乳酪蛋糕(片)', 2, 65 )
,      ( 8, N'重乳酪蛋糕(片)', 2, 70 )
,      ( 9, N'抹茶奶酪', 3, 90 )
,      ( 10, N'草莓奶酪', 3, 85 )
,      ( 11, N'芒果奶酪', 3, 85 )
,      ( 12, N'紅豆奶酪', 3, 80 )
;
GO

2.. Rank Function + OVER (ORDER BY)


USE Cookies;
GO 
SELECT p.[Id], p.[Name], c.[Name], p.[Price]
,  ROW_NUMBER() OVER (ORDER BY p.Price DESC) AS "Row Number" 
,  RANK()       OVER (ORDER BY p.Price DESC) AS "Rank"
,  DENSE_RANK() OVER (ORDER BY p.Price DESC) AS "Dense Rank" 
,  NTILE(3)     OVER (ORDER BY p.Price DESC) AS "Quartile"
FROM [dbo].[Products] AS p  
   LEFT OUTER JOIN [dbo].[Categories] AS c ON p.[Category] = c.[Id]
;
GO

上述指令裡 ORDER BY p.Price DESC, 代表以 價格 由高而低排序後, 給予 '次序' 值; 但各個 Rank Function 在功能上, 有些許差異.
以下圖的執行結果而言:
ROW_NUMBER() 應該不用多作解釋
RANK() 遇到相同的值, 給同樣的 '次序'; 但下一個的值, 會是 ( 排在該值之前的所有個數 + 1), 例如: Price 為 80 那筆, 排在該值之前的所有個數 = 3, 所以其 RANK 的值是 4
DENSE_RANK() 遇到相同的值, 給同樣的 '次序'; 但下一個的值, 會是 ( 排在該值之前的次序值 + 1), 例如: Price 為 80 那筆, 排在該值之前的次序值 = 2, 所以其 RANK 的值是 3
NTILE(n) 將所有資料, 切分為 n 組, 萬一無法平均分組, SQL Server 會自行調整. 例如: 原來有 14 個值, 要分為 4 組; 會成為 4 + 4 + 3 + 3 的方式.





3.. Rank Function + OVER (PARTITION BY ... ORDER BY ...)


USE Cookies;
GO 
SELECT p.[Id], p.[Name], c.[Name], p.[Price]
,  ROW_NUMBER() OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "Row Number" 
,  RANK()       OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "Rank"
,  DENSE_RANK() OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "Dense Rank" 
,  NTILE(3)     OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "Quartile"
FROM [dbo].[Products] AS p  
   LEFT OUTER JOIN [dbo].[Categories] AS c ON p.[Category] = c.[Id]
;
GO

上述指令裡 PARTITION BY p.Category, 代表以 產品類別 作為分割的標的, 以本例而言, 會有 3 個分割.
在每一個分割裡進行 ORDER BY p.Price DESC, 代表以 價格 由高而低排序後, 給予 '次序' 值; 但各個 Rank Function 在功能上, 有些許差異. 
ROW_NUMBER() 應該不用多作解釋
RANK() 遇到相同的值, 給同樣的 '次序'; 但下一個的值, 會是 ( 排在該值之前的所有個數 + 1), 例如: Price 為 55 那筆, 排在該值之前的所有個數 = 4, 所以其 RANK 的值是 5
DENSE_RANK() 遇到相同的值, 給同樣的 '次序'; 但下一個的值, 會是 ( 排在該值之前的次序值 + 1), 例如: Price 為 55 那筆, 排在該值之前的次序值 = 2, 所以其 RANK 的值是 3
NTILE(n) 將所有資料, 切分為 n 組, 萬一無法平均分組, SQL Server 會自行調整. 例如: 第 3 個分割含有 4 筆資料, 以 2 + 1 + 1 的方式作分組.






4.. CASE A: Aggregate Function + OVER () 的部份


USE Cookies;
GO
SELECT p.[Id], p.[Name], c.[Name], p.[Price]
,  SUM(p.Price)   OVER () AS "SUM"
,  COUNT(p.Price) OVER () AS "COUNT"
,  AVG(p.Price)   OVER () AS "AVG"
,  MAX(p.Price)   OVER () AS "MAX"
,  MIN(p.Price)   OVER () AS "MIN"
FROM [dbo].[Products] AS p 
   LEFT OUTER JOIN [dbo].[Categories] AS c ON p.[Category] = c.[Id]
;
GO

這個例子是完全沒有作分割, 也沒有作排序的動作, 所以會是針對全部 12 筆的資料作彙總的處理.
總和: 825, 資料筆數: 12, 平均值: 68, 最大值: 90, 最小值: 50





5.. CASE B: Aggregate Function + OVER (PARTITION BY ...)


USE Cookies;
GO 
SELECT p.[Id], p.[Name], c.[Name], p.[Price]
,  SUM(p.Price)   OVER (PARTITION BY p.Category) AS "SUM"
,  COUNT(p.Price) OVER (PARTITION BY p.Category) AS "COUNT" 
,  AVG(p.Price)   OVER (PARTITION BY p.Category) AS "AVG"
,  MAX(p.Price)   OVER (PARTITION BY p.Category) AS "MAX"
,  MIN(p.Price)   OVER (PARTITION BY p.Category) AS "MIN"
FROM [dbo].[Products] AS p  
   LEFT OUTER JOIN [dbo].[Categories] AS c ON p.[Category] = c.[Id]
;
GO

這個例子是以 產品類別 作分割, 沒有作排序的動作, 所以會是針對各個分割裡的資料作彙總的處理.

6.. CASE C: Aggregate Function + OVER (PARTITION BY ...) 


USE Cookies;
GO
SELECT p.[Id], p.[Name], c.[Name], p.[Price]
,  SUM(p.Price)   OVER (PARTITION BY p.Category) AS "SUM"
,  COUNT(p.Price) OVER (PARTITION BY p.Category) AS "COUNT"
,  MAX(p.Price)   OVER (PARTITION BY p.Category ) AS "MAX"
,  MIN(p.Price)   OVER (PARTITION BY p.Category ) AS "MIN"
,  AVG(CAST(p.Price AS DECIMAL)) OVER (PARTITION BY p.Category) AS "AVG"
,  (CAST(p.[Price] AS DECIMAL) / SUM(p.Price) OVER (PARTITION BY p.Category) ) AS "PCT"
FROM [dbo].[Products] AS p
   LEFT OUTER JOIN [dbo].[Categories] AS c ON p.[Category] = c.[Id]
;
GO

這個例子是以 產品類別 作分割, 沒有作排序的動作, 所以會是針對各個分割裡的資料作彙總的處理.
加入了小數點的處理, 同時, 一併取得各筆資料的 價格 值, 佔所屬分割總和的百分比.這個百分比功能, 在一般實務上, 還蠻有用的.




7.. CASE D: Aggregate Function + OVER (PARTITION BY ... ORDER BY ...) 


USE Cookies;
GO
SELECT p.[Id], p.[Name], c.[Name], p.[Price]
,  SUM(p.Price)   OVER (PARTITION BY p.Category ORDER BY p.Id) AS "SUM"
,  COUNT(p.Price) OVER (PARTITION BY p.Category ORDER BY p.Id) AS "COUNT"
,  AVG(p.Price)   OVER (PARTITION BY p.Category ORDER BY p.Id) AS "AVG"
,  MAX(p.Price)   OVER (PARTITION BY p.Category ORDER BY p.Id) AS "MAX"
,  MIN(p.Price)   OVER (PARTITION BY p.Category ORDER BY p.Id) AS "MIN"
FROM [dbo].[Products] AS p 
   LEFT OUTER JOIN [dbo].[Categories] AS c ON p.[Category] = c.[Id]
;
GO

這個例子是以 產品類別 作分割, 且以 產品代號 作排序, 所以會是針對各個分割裡的資料作彙總的處理.
執行的結果, 可以看出有逐筆累進處理的狀況.
例如: 2 筆時的狀況
(1) SUM : 第2筆的合計值 = 第1筆價格 + 第2筆價格 = 50 + 55 = 105
(2) AVG : 第2筆的平均值 = 第2筆的合計值 / 第2筆的資料筆數 = 105 / 2 = 52.5 (無條件捨去) = 52
(3) MAX : 前2筆的最高價格 = 55
(4) MIN : 前2筆的最低價格 = 50
例如: 3 筆時的狀況
(1) SUM : 第3筆的合計值 = 第1筆價格 + 第2筆價格 + 第3筆價格 = 50 + 55 + 60 = 165
(2) AVG : 第3筆的平均值 = 第3筆的合計值 / 第3筆的資料筆數 = 165 / 3 = 55
(3) MAX : 前3筆的最高價格 = 60
(4) MIN : 前3筆的最低價格 = 50





8.. CASE E: Aggregate Function + OVER (PARTITION BY ... ORDER BY ...)


USE Cookies;
GO
SELECT p.[Id], p.[Name], c.[Name], p.[Price]
,  SUM(p.Price)   OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "SUM"
,  COUNT(p.Price) OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "COUNT"
,  AVG(p.Price)   OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "AVG"
,  MAX(p.Price)   OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "MAX"
,  MIN(p.Price)   OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS "MIN"
FROM [dbo].[Products] AS p 
   LEFT OUTER JOIN [dbo].[Categories] AS c ON p.[Category] = c.[Id]
;
GO

這個例子是以 產品類別 作分割, 且以 價格 作排序, 所以會是針對各個分割裡的資料作彙總的處理.
執行的結果, 可以看出有逐筆累進處理的狀況.
但請特別留意, 遇到 相同價格值 的處理方式, 以第2筆至第4筆而言, 其 價格 都是 60, 其 總和值 = 前4筆的總和 = 245, 且填入價格為 60 的每筆資料列.
其餘類推.




9.. 結論


經由上述的演練, 終於對  Rank Function / Aggregate Function + Over ( Partition By  ... Order By ... ) 有一些理解, 未來在寫 T-SQL 時, 針對需要作 Rank / Aggregate 的情境, 可有比較好的作法.

以 5.. Aggregate Function + OVER (PARTITION BY ...)  的例子而言, 以往的作法, 會先用 CTE (Comman Table Expression) 進行 GROUP BY p.Category, 再用 JOIN 的方式作處理. 如下述的程式碼及執行結果.

WITH ProductSummary
( CategoryId
, Total
, Cnt
, Average
, Max
, Min)
AS
( SELECT CategoryId = p.Category
    ,   Total = SUM(p.Price)
    ,   Cnt = COUNT(p.Price)
    ,   Average = AVG(p.Price)
    ,   Max = MAX(p.Price)
    ,   Min = Min(p.Price)    
  FROM [Products] AS p
  GROUP BY Category
)
SELECT p.[Id], p.[Name], c.[Name], p.[Price], ps.*
FROM [dbo].[Products] AS p 
   LEFT OUTER JOIN [dbo].[Categories] AS c ON p.[Category] = c.[Id]
   LEFT OUTER JOIN ProductSummary AS ps ON p.[Category] = ps.[CategoryId]
;


10.. 參考文件


1.. Microsoft Docs, "Ranking Functions (Transact-SQL)"
2.. 德瑞克：SQL Server 學習筆記, "SQL Server：認識「次序函數(Window Ranking Functions)」(1)"
3.. 德瑞克：SQL Server 學習筆記, "SQL Server：認識「次序函數(Window Ranking Functions)」(2)"


11.. 工商時間


筆者借本文一角, 推薦一家在台南的 公爵果子工房, 以豆塔, 堅果及法式布丁 為主要招牌產品, 最近更擴展到自動販賣機的散步甜點(位於神農街, 康樂街口). 
本文測試資料裡的產品項目及產品價格, 純屬展示用, 實際內容, 以 公爵果子工房 官網為準.



張貼者： 於
標籤： , , , , , ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)