3 聚合與排序
3-1 對錶進行聚合查詢
聚合函式
通過SQL對資料進行 操作或計算時需要使用函式。
計算表中全部資料行數時，可以使用COUNT函式。
COUNT : 計算表中的記錄數（行數）。
SUM : 計算表中數值列的資料合計值。
AVG : 計算表中數值列的資料平均值。
MAX ：計算表中任意列中資料的最大值。
MIN ：計算表中任意列中資料的最小值。
如上所示，用於合計的函式稱為聚合函式或者集合函式。本書中統稱為聚合函式。所謂聚合，就是將多行彙總為一行。
函式這個詞，與我們在學校課上學到的意思是一樣的，就像是輸入某個值就能輸出相應結果的盒子一樣。
|
計算表中資料的行數 |
|
/* COUNT 計算表中的記錄數（行數） sum 計算表中數值列的資料合計值 avg 計算表中數值列的資料平均值 max 求出表中任意列中資料的最大值 min 求出表中任意列中資料的最小值 */
— 此處的輸入值稱為引數或者parameter，輸出值稱為返回值
SELECT COUNT(*) FROM shohin;
SELECT COUNT(shiire_tanka) FROM shohin; |
即使對同一張表使用COUNT函式，輸入的引數不同得到的結果也會不同。
星號對於COUNT函式來說是獨有的，其他函式並不能將星號作為引數（如果使用星號會出錯）。
|
計算NULL以外資料的行數 |
|
SELECT COUNT(shiire_tanka) FROM shohin; |
對於COUNT函式來說，引數列不同計算的結果也會發生變化。
|
法則3-1 |
|
COUNT函式的結果根據引數的不同而不同，COUNT（*） 會得到包含NULL的資料行數，而COUNT（<列名>）會得到NULL之外的資料行數。 |
|
計算合計值 |
|
SELECT SUM(hanbai_tanka) FROM shohin; SELECT SUM(shiire_tanka) FROM shohin; |
|
法則3-2 |
|
聚合函式會將NULL排除在外。但COUNT（*）例外，並不會排除NULL。 |
|
計算平均值 |
|
— （值的合計）/（值的個數）就是平均值的計算公式 如果遇到空的情況會先剔除null 值的合計和值的個數都需要去除。 SELECT AVG(hanbai_tanka), AVG(shiire_tanka) FROM shohin; |
|
計算最大值和最小值 |
|
SELECT MAX(hanbai_tanka), MIN(shiire_tanka) FROM shohin;
SELECT MAX(torokubi), MIN(torokubi) FROM shohin; |
|
法則3-3 |
|
MAX/MIN函式幾乎適用於所有資料型別的列。SUM/AVG函式只適用於數值型別的列。 |
|
使用聚合函式刪除重複值（關鍵字DISTINCT） |
|
SELECT COUNT(DISTINCT shohin_bunrui) FROM shohin; — 請注意，這時DISTINCT必須寫在括號中。這是因為必須要在計算行數之前刪除shohin_bunrui列中的重複資料。 SELECT SUM(hanbai_tanka), SUM(DISTINCT hanbai_tanka) FROM shohin; SELECT DISTINCT COUNT(shohin_bunrui) FROM shohin;
— DISTINCT不僅限於COUNT函式，所有的聚合函式都可以使用。 |
|
法則3-4 |
|
想要計算值的種類時，可以在COUNT函式的引數中使用DISTINCT。 |
|
法則3-5 |
|
在聚合函式的引數中使用DISTINCT，可以刪除重複資料。 |
3-2 對錶進行分組
目前為止，我們看到的聚合函式的使用方法，無論是否包含NULL，無論是否刪除了重複資料，都是針對表中的所有資料進行的聚合處理。下面，我們先把表分成幾組，然後再進行聚合處理。
|
GROUP BY子句 |
|
/* SELECT <列名1>, <列名2>, …… FROM <表名> GROUP BY <列名1>, <列名2>, ……; */ SELECT shohin_bunrui, COUNT(*) FROM shohin; SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui; |
這樣，GROUP BY子句就像切蛋糕那樣將表進行分組。在GROUP BY子句中指定的列稱為聚合鍵或者分組列。
當然，GROUP BY子句也和SELECT子句一樣，可以通過逗號分隔指定多列。
|
法則3-6 |
|
GROUP BY就像是切分表的一把刀。 |
|
法則3-7 |
|
SQL子句的順序不能改變，也不能互換位置。 |
子句的書寫順序（暫定） ：SELECT > FROM > WHERE > GROUP BY
|
聚合鍵中包含NULL的情況 |
|
SELECT shiire_tanka, COUNT(*) FROM shohin GROUP BY shiire_tanka; — 當聚合鍵中包含null時，也會將null作為一組特定的資料 這裡的null 大家可以理解為“不確定” SELECT shiire_tanka, COUNT(*) FROM shohin GROUP BY shiire_tanka; |
從結果我們可以看出，當聚合鍵中包含NULL時，也會將NULL作為一組特定的資料。這裡的NULL，大家可以理解為“不確定”。
|
法則3-8 |
|
聚合鍵中包含NULL時，在結果中會以“不確定”行（空行）的形式表現出來。 |
|
使用WHERE子句時GROUP BY的執行結果 |
|
/* SELECT <列名1>, <列名2>, …… FROM <表名> WHERE … GROUP BY <列名1>, <列名2>, ……; */
SELECT shiire_tanka, COUNT(*) FROM shohin WHERE shohin_bunrui = `衣服`;
SELECT shiire_tanka, COUNT(*) FROM shohin WHERE shohin_bunrui = `衣服` GROUP BY shiire_tanka; |
像這樣使用WHERE子句進行聚合處理時，會先根據WHERE子句指定的過濾條件進行過濾，然後再進行聚合處理。
GROUP BY和WHERE並用時SELECT語句的執行順序 ：FROM > WHERE > GROUP BY > SELECT
|
與聚合函式和GROUP BY子句有關的常見錯誤 |
|
— 其他dbms可能報錯 SELECT shohin_mei, shiire_tanka, COUNT(*) FROM shohin GROUP BY shiire_tanka; SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui; SELECT shohin_bunrui AS sb, COUNT(*) FROM shohin GROUP BY sb;
SELECT shohin_bunrui, COUNT(*) FROM shohin WHERE COUNT(2) GROUP BY shohin_bunrui; |
|
法則3-9 |
|
使用GROUP BY子句時，SELECT子句中不能出現聚合鍵之外的列名。 |
|
法則3-10 |
|
在GROUP BY子句中不能使用SELECT子句中定義的別名。 |
|
法則3-11 |
|
GROUP BY子句結果的顯示是無序的。 |
|
法則3-12 |
|
只有SELECT子句和HAVING子句（以及ORDER BY子句）中能夠使用聚合函式。 |
|
DISTINCT和GROUP BY |
|
— DISTINCT和GROUP BY能夠實現相同的功能 SELECT DISTINCT shohin_bunrui FROM shohin; SELECT shohin_bunrui FROM shohin GROUP BY shohin_bunrui; — 除次之外，它們還都會把NULL作為一個獨立的結果返回，對多列使用時也會得到完全相同的結果。其實不僅處理結果相同，執行速度也基本差不多，到底應該使用哪一個呢？ 但其實這個問題本身就是本末倒置的，我們應該考慮的是該SELECT語句是否滿足需求。選擇的標準其實非常簡單，在“想要刪除選擇結果中的重複記錄”時使用DISTINCT，在“想要計算聚合結果”時使用GROUP BY。 不適用COUNT等聚合函式，而只使用GROUP BY子句的SELECT語句，會讓人覺得非常奇怪。難免使人產生“到底為什麼要對錶進行分組呢？這樣做有必要嗎？”等疑問。
|
3-3 為聚合結果指定條件
|
HVING子句 |
|
/* SELECT <列名1>, <列名2>, …… FROM <表名> GROUP BY <列名1>, <列名2>, …… HAVING <分組結果對應的條件>; */ SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui; SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui HAVING COUNT(*) = 2; SELECT shohin_bunrui, AVG(hanbai_tanka) FROM shohin GROUP BY shohin_bunrui; SELECT shohin_bunrui, AVG(hanbai_tanka) FROM shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) >= 2500; |
說到指定條件，估計大家都會首先想到WHERE子句。但是，WHERE子句只能指定記錄（行）的條件，而不能用來指定組的條件（例如，“資料行數為2行”或者“平均值為500等”）。
HAVING子句必須寫在GROUP BY子句之後。其在DBMS內部的執行順序也排在GROUP BY子句之後。
使用HAVING子句時SELECT語句的順序 ：SELECT > FROM > WHERE > GROUP BY > HAVING
|
法則3-13 |
|
HAVING子句要寫在GROUP BY子句之後。 |
|
HAVING子句的構成要素 |
|
— HAVING子句中能夠使用的3種要素：常數/聚合函式/GROUP BY子句中指定的列名（即聚合鍵）
SELECT shohin_mei, shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_mei HAVING shohin_mei = `圓珠筆`; |
|
相當於HAVING子句，更適合寫在WHERE子句中的條件 |
|
SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui HAVING shohin_bunrui = `衣服`;
SELECT shohin_bunrui, COUNT(*) FROM shohin WHERE shohin_bunrui = `衣服` GROUP BY shohin_bunrui; |
也許有的讀者已經發現了，有些條件既可以寫在HAVING子句當中，有可以寫在WHERE子句當中。這些條件就是聚合鍵所對應的條件。
如果僅從結果來看的話，確實如此。但筆者卻認為，聚合鍵所對應的條件還是應該書寫在WHERE子句之中。
根本原因是where子句和having子句的作用不同，如前所述，having子句是用來指定“組”的條件的，因此，“行”所對應的條件還是應該寫在where子句當中，這樣一來，書寫出的select語句不但可以分清兩者各自的功能，理解起來也更容易
Where子句 = 指定行所對應的條件
Having子句 = 指定組所對應的條件
其次where執行速度快一點
通常情況下，為了得到相同的結果，將條件寫在WHERE子句中要比寫在HAVING子句中的處理速度更快，返回結果所需時間更短。
為了理解其中原因，就要從DBMS的內部執行機制來考慮。使用COUNT函式等對錶中的資料進行聚合操作時，DBMS內部就會進行排序處理。排序處理會大大增加機器的負擔，此即所謂高負荷的處理。因此，只有儘可能減少排序的行數，才能增加處理速度。
通過WHERE子句指定條件時，由於排序之前就對資料進行了過濾，所以能夠減少排序的資料量。但HAVING子句是在排序之後才對資料進行分組的，因此與在WHERE子句中指定條件比起來，需要排序的資料量就會多得多。雖然DBMS的內部處理不盡相同，但是對於排序處理來說，基本上都是一樣的。
此外，WHERE子句更具速度優勢的另一個理由是，可以對WHERE子句指定條件所對應的列建立索引，這樣也可以大幅度提高處理速度。建立索引是一種非常普遍的提高DBMS效能的方法，效果也十分明顯，這對WHERE子句來說也十分有利。
|
法則3-14 |
|
聚合鍵所對應的條件不應該書寫在HAVING子句當中，而應該書寫在WHERE子句當中。 |
3-4 對查詢結果進行排序
通常，從表中抽取資料時，如果沒有特別指定順序，最終排列順序便無從得知。即使是同一條SELECT語句，每次執行時排列順序很可能發生改變。
但是不進行排序，很可能出現結果混亂的情況。這時，便需要通過在SELECT語句末尾新增ORDER BY子句來明確指定排列順序。
|
ORDER BY子句 |
|
— SELECT <列名1>, <列名2>, …… FROM <表名> ORDER BY <排序基準列1>, <排序基準列2>, …;
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin;
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka; |
不論何種情況，ORDER BY子句都需要寫在SELECT語句的末尾。這是因為對資料行進行排序的操作必須在結果即將返回時執行。ORDER BY子句中書寫的列名稱為排序鍵。
子句的書寫順序 ：SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
|
法則3-15 |
|
ORDER BY子句通常寫在SELECT語句的末尾。 |
|
指定升序（ASC）和降序（DESC） |
|
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka DESC;
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka ASC; |
由於ASC和DESC這兩個關鍵字是以列為單位指定的，所以可以同時指定一個列為升序，指定其他列為降序。
|
法則3-16 |
|
未指定ORDER BY子句中排列順序時會預設使用升序進行排列。 |
|
指定多個排序鍵 |
|
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka, shohin_id; |
這樣一來，就可以在ORDER BY子句中同時指定多個排序鍵了。會優先使用左側的鍵，如果該列存在相同值的話，會接著參考右側的鍵。當然，也可以同時使用3個以上的排序鍵。
|
NULL的順序 |
|
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY shiire_tanka; |
不能對NULL使用比較運算子，也就是說，不能對NULL和數字進行排序。也不能與字串和日期比較大小。因此，使用含有NULL的列作為排序鍵時，NULL會在結果的開頭或末尾彙總顯示。
究竟是在開頭顯示還是在末尾顯示，並沒有特殊規定。某些DBMS中可以指定NULL在開頭或末尾顯示，希望大家對自己使用的DBMS的功能研究以下。
|
法則3-17 |
|
排序鍵中包含NULL時，會在開頭或末尾進行彙總。 |
|
在排序鍵中使用顯示用別名 |
|
SELECT shohin_id AS id, shohin_mei, hanbai_tanka AS ht, shiire_tanka FROM shohin ORDER BY ht, id; |
不能在GROUP BY子句中使用的別名，為什麼可以在ORDER BY子句中使用呢？這是因為SQL語句在DBMS內部的執行順序被掩蓋起來了。
使用HAVING子句時SELECT語句執行順序 ：FROM > WHERE >GROUP BY > HAVING > SELECT > ORDER BY
這只是一個粗略的總結，雖然具體的執行順序根據DBMS的不同而不同，但是大家有這樣一個大致的印象就可以了。一定要記住SELECT子句的執行順序在GROUP BY子句之後，ORDER BY子句之前。因此，在執行GROUP BY子句時，SELECT語句中定義的別名無法被識別。對於在SELECT子句之後執行的ORDER BY子句來說，就沒有這樣的問題了。
|
法則3-18 |
|
在ORDER BY子句中可以使用SELECT子句中定義的別名。 |
ORDER BY 子句中可以使用存在於表中，但並不包含在SELECT子句中的列。除此之外，還可以使用聚合函式。
|
ORDER BY子句中可以使用的列 |
|
SELECT shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY shohin_id;
SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui ORDER BY COUNT(*); |
|
法則3-19 |
|
在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函式。 |
|
不要使用列編號 |
|
— ORDER BY 子句中可以使用列的編號，列編號是指SELECT子句中的列按照從左到右的順序進行排列時所對應的編號（1，2，3，…）。 不建議使用編號 程式碼閱讀起來比較難 該排序功能將來會被刪除
— 使用列名 SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka DESC, shohin_id;
— 使用列編號 SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY 3 DESC, 1; |
雖然列編號使用起來非常方便，但我們並不推薦使用，原因有以下兩點 ：第一，程式碼閱讀起來比較難；第二，這也是根本問題。實際上，在SQL-92中已經明確指出該排序功能將來會被刪除。
|
法則3-20 |
|
在ORDER BY子句中不要使用列編號。 |