SQL 達人的工作現場攻略筆記 - CH-18 GROUP BY 與 PARTITION BY

大家了解兩者的差異嗎?

group by: 對檢索結果的保留行進行單純分組，返回聚合之後的組的數據統計值的記錄。劃分為多組，每組返回一行。
partition by: 返回分組裡的每一條數據，在應用關聯的視窗函數前確定行集的分區和排序，然後，視窗函數將計算每一行的值。

SQL 篩選各種資料時，最基本操作就是某種準備替資料分組。其實除了使用 SQL 之外，日常生活中整理資料、查詢資料時，替資料建群組。

而在 SQL 中具有建立群組功能就是 GROUP BY 跟 PARTITION BY。這兩種方式都能利用資料表指定的 KEY 來分割資料，而兩者不同地方在於 GROUP BY 會再分割後，將資料彙整為一列，而 PARTITION BY 是將每一欄位取出整理。

透過下列團隊成員資料表說明兩者差異。

Teams

member	team	age
郁文	A	28
瑋乃	A	19
銘仁	A	23
靚儀	B	40
勝朋	B	29
美美	C	30
建國	D	28
崇恩	D	28
連雅	D	24
光城	D	22

若以 GROUP BY 與 PARTITION BY 處理這張資料表，可寫出取得團隊資訊的查詢。不管使用哪個陳述句，Teams 資料表都會先分割部分集合，再以SUM函數彙整或 RANK 排出順位。

SELECT member,team,age,
RANK() OVER(PARTITION BY team ORDER BY age DESC) rn,
DENSE_RANK()  OVER(PARTITION BY team ORDER BY age DESC) dense_rn,
ROW_NUMBER()  OVER(PARTITION BY team ORDER BY age DESC) row_num
FROM Teams
ORDER BY team, rn

結果:

member	team	age	rn	dense_rn	row_num
郁文	A	28	1	1	1
銘仁	A	23	2	2	2
瑋乃	A	19	3	3	3
靚儀	B	40	1	1	1
勝朋	B	29	2	2	2
美美	C	30	1	1	1
建國	D	28	1	1	1
崇恩	D	28	1	1	2
連雅	D	24	3	2	3
光城	D	22	4	3	4

根據書中 286 頁圖片，使用圖形代表集合，是為了呈現分割，經過仔細觀察後發現

1
2
3

每一個都不是空集合
部分集合總和等於分割前的集合
隨機從中挑選兩個集合出來，不會有共通得部分。

由於部分集合本來就是從資料表分割而來，所以不會有空集合，且部分集合的總和當然會是分割之的母體集合，所以不會有成員因為分割而失蹤。此外也不會有同時屬於兩個部分集合的成員。每一位成員必然隸屬一個集合。也就是說 GROUP BY 與 PARTITION BY 可將各成員分割至團隊得函數。

結論

SQL 與 RDB 採用許多集合論以及群的成果，或許內容有點抽象，但也因為抽象內容，所以才廣泛應用。

參考資料:

SQL 達人的工作現場攻略筆記。