學習SQL【4】-聚合與排序
隨著表中記錄（數據行）的不斷積累，存儲數據逐漸增加，有時我們可能希望計算出這些數據的合計值或者平均值等，這個時候就需要使用SQL語句的匯總操作等方法。
一：對錶進行聚合排序
1：聚合函數
通過SQL對數據進行某種操作或計算時需要使用函數。SQL有五種常用的函數：
● COUNT：計算表中數據的行數（記錄數）。
● SUM：計算表中數值列中數據的合計數。● AVG：計算表中數值列中數據的平均值。● MAX：計算表中數值列中數據的最大值。● MIN：計算表中數值列中數據的最小值。如上所示，用於匯總的函數成為聚合函數或者聚集函數。接下來，我們仍然使用之前創建的Product表進行函數的學習，Product表的結構和內容如下：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-09-11 0003 | 運動T衫 | 衣服 | 4000 | 2800 | 0004 | 菜刀 | 廚房用具 | 3000 | 2800 | 2017-09-20 0005 | 高壓鍋 | 廚房用具 | 6800 | 5000 | 2017-01-15 0006 | 叉子 | 廚房用具 | 500 | | 2017-09-20 0007 | 擦菜板 | 廚房用具 | 880 | 790 | 2016-04-28 0008 | 圓珠筆 | 辦公用品 | 100 | | 2017-11-11(8 行記錄)
2：計算表中數據的行數
使用COUNT函數時，輸入表的列，就能輸出數據行數：
例如，計算全部數據的行數：SELECT COUNT(*) FROM Product;
執行結果：
count------- 8(1 行記錄)
COUNT（）中的星號，代表全部列的意思。函數的輸入值稱為參數，輸出值稱為返回值。
3：計算NULL之外的數據的行數
SELECT COUNT(purchase_price) FROM Product;
執行結果：
count------- 6(1 行記錄)
對於COUNT函數來說，參數列不同，計算結果也會不同。COUNT(*)會得到包含NULL的數據行數，而COUNT(<列名>)會得到NULL之外的數據行數。
4：計算合計值
計算合計值需要使用SUM函數
例，計算銷售單價的合計值：SELECT SUM(sale_price) FROM Product;
執行結果：
sum------- 16780(1 行記錄)
例如，計算銷售單價和進貨單價的合計值：
SELECT SUM(sale_price), SUM(purchase_price) FROM Product;
執行結果：
sum | sum-------+------- 16780 | 12210(1 行記錄)
注釋：
聚合函數會將NULL排除在外，但COUNT(*)例外，並不會排除NULL。
5：計算平均值
計算平均值需要使用AVG函數
例，計算銷售單價的平均值：SELECT AVG(sale_price) FROM Product;
執行結果：
avg----------------------- 2097.5000000000000000(1 行記錄)
例如，計算銷售單價和進貨單價的平均值：
SELECT AVG(sale_price), AVG(purchase_price) FROM Product;
執行結果：
avg | avg-----------------------+----------------------- 2097.5000000000000000 | 2035.0000000000000000(1 行記錄)
6：計算最大值和最小值
想要計算出多條記錄的最大值或最小值，可以分別使用MAX和MIN函數。
例，計算銷售單價的最大值和進貨單價的最小值：
SELECT MAX(sale_price), MIN(purchase_price) FROM Product;
執行結果：
max | min------+----- 6800 | 320(1 行記錄)
例如，計算登記日期的最大值和最小值：
SELECT MAX(regist_date), MIN(regist_date) FROM Product;
執行結果：
max | min------------+------------ 2017-11-11 | 2016-04-28(1 行記錄)
MAX/MIN函數幾乎適用於所有數據類型的列。而SUM/AVG函數只適用於數值類型的列。
7：使用聚合函數刪除重複值（關鍵字DISTINCT）
例，計算除去重複數據後的數據行數：
SELECT COUNT(DISTINCT product_type) FROM Product;
執行結果：
count------- 3(1 行記錄)
想要計算值的種類時，可以在COUNT函數的參數中使用DISTINCT。
在聚合函數的參數中使用DISTINCT，可以刪除重複數據。二：對錶進行分組
1：GROUP BY子句
使用GROUP BY子句可以像切蛋糕那樣將表分割。GROUP BY子句的語法結構如下：
SELECT <列名1>, <列名2>,... FROM <表名> GROUP BY <列名1>, <列名2>,...;
例如，按照商品種類統計數據行數：
SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;
執行結果：
product_type | count--------------+------- 衣服 | 2 辦公用品 | 2 廚房用具 | 4(3 行記錄)
● 在GROUP BY 子句中指定的列稱為聚合鍵。
● 子句的書寫順序（暫定）：SELECT→FROM→ WHERE→ GROUP BY● SQL子句的順序不能更改，也不能相互替換。2：聚合鍵中包含NULL的情況
例，按照進貨單價統計數據行數：
SELECT purchase_price, COUNT(*) FROM Product GROUP BY purchase_price;
執行結果：
purchase_price | count----------------+------- | 2 320 | 1 500 | 1 5000 | 1 2800 | 2 790 | 1(6 行記錄)
聚合鍵中包含NULL時，在結果中會以「不確定」行（空行）的形式顯示出來。
3：使用WHERE子句和GROUP BY 子句的執行結果
例，同時使用WHERE子句和GROUP BY子句：
SELECT purchase_price, COUNT(*) FROM Product WHERE product_type = 衣服 GROUP BY purchase_price;
執行結果：
purchase_price | count----------------+------- 500 | 1 2800 | 1(2 行記錄)
4：與聚合函數和GROUP BY 子句有關的常見錯誤
● 使用GROUP BY 子句時，SELECT 子句中不能出現聚合鍵之外的列名。
● 在GROUP BY 子句中不能使用SELECT子句中定義的別名。● GROUP BY 子句結果的顯示是無序的。● 只有SLEECT子句、GROUP BY 子句和HAVING子句中能夠使用聚合函數，WHERE 子句中不能使用聚合函數。
三：為聚合結果指定條件
1：HAVING子句
對集合指定條件可以使用HAVING子句。HAVING子句的語法如下：
SELECT <列名1>, <列名2>,... FROM <表名> GROUP BY <列名1>, <列名2>,... HAVING <分組結果對應的條件>；
注釋：
HAVING子句要寫在GROUP BY 子句之後。
例，從按照商品種類進行分組後的結果中，取出「包含的數據行數為2行」的組：SELECT product_type, COUNT(*) FROM Product GROUP BY product_type HAVING COUNT(*) = 2;
執行結果：
product_type | count--------------+------- 衣服 | 2 辦公用品 | 2(2 行記錄)
不使用HAVING子句的情況
SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;
執行結果：
product_type | count--------------+------- 衣服 | 2 辦公用品 | 2 廚房用具 | 4(3 行記錄)
2：HAVING子句的構成要素
HAVING子句中能夠使用的3中要素如下：
● 常數● 聚合函數● GROUP BY 子句中 指定的列名（聚合鍵）3：相比於HAVING子句，更適合於寫在WHERE子句中的條件
我們會發現，有些條件既可以寫在HAVING子句中，也可以寫在WHERE 子句中，而且結果是一樣的。這些條件就是聚合鍵所對應的條件。
例，將條件寫在HAVING 子句中：SELECT product_type, COUNT(*) FROM Product GROUP BY product_type HAVING product_type = 衣服;
執行結果：
product_type | count--------------+------- 衣服 | 2(1 行記錄)
將條件書寫在WHERE子句中的情況
SELECT product_type, COUNT(*) FROM Product WHERE product_type = 衣服 GROUP BY product_type;
執行結果：
product_type | count--------------+------- 衣服 | 2(1 行記錄)
兩者結果完全相同，但是，從執行速度來講，將條件寫在WHERE 子句中要比寫在HAVING子句中的處理速度要快。所以，聚合鍵所對應的條件應該書寫在WHERE 子句中。
四：對查詢結果進行排序
1：ORDER BY子句
使用ORDER BY 子句可對查詢結果進行排序，ORDER BY子句的語法：
SELECT <列名1>, <列名2>,... FROM <表名> ORDER BY <排列基準1>, <排列基準2>,....;
例，按照銷售單價由低到高（升序）進行排序：
SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY sale_price;
執行結果：
product_id | product_name | sale_price | purchase_price------------+--------------+------------+---------------- 0008 | 圓珠筆 | 100 | 0006 | 叉子 | 500 | 0002 | 打孔器 | 500 | 320 0007 | 擦菜板 | 880 | 790 0001 | T衫 | 1000 | 500 0004 | 菜刀 | 3000 | 2800 0003 | 運動T衫 | 4000 | 2800 0005 | 高壓鍋 | 6800 | 5000(8 行記錄)
● ORDER BY子句中書寫的列名稱為排序鍵。
● ORDER BY 子句通常寫在SELECT語句的末尾。2：指定升序或降序
降序排列時，可使用DESC關鍵字。
例，按照銷售單價由高到低（降序）進行排序：SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY sale_price DESC;
執行結果：
product_id | product_name | sale_price | purchase_price------------+--------------+------------+---------------- 0005 | 高壓鍋 | 6800 | 5000 0003 | 運動T衫 | 4000 | 2800 0004 | 菜刀 | 3000 | 2800 0001 | T衫 | 1000 | 500 0007 | 擦菜板 | 880 | 790 0002 | 打孔器 | 500 | 320 0006 | 叉子 | 500 | 0008 | 圓珠筆 | 100 |(8 行記錄)
注釋：
未指定ORDER BY 子句中排列順序時會默認以升序排列。
3：指定多個排序鍵
可以在ORDER BY 子句中指定多個排序鍵，規則是優先使用左側的鍵，如果該列存在相同的值，再接著參考右側的鍵。
例，按照銷售單價和商品編號的升序進行排序：SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY sale_price, product_id;
執行結果：
product_id | product_name | sale_price | purchase_price------------+--------------+------------+---------------- 0008 | 圓珠筆 | 100 | 0002 | 打孔器 | 500 | 320 0006 | 叉子 | 500 | 0007 | 擦菜板 | 880 | 790 0001 | T衫 | 1000 | 500 0004 | 菜刀 | 3000 | 2800 0003 | 運動T衫 | 4000 | 2800 0005 | 高壓鍋 | 6800 | 5000(8 行記錄)
4：NULL 的排序
例，按照進貨單價的升序進行排序：
SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY purchase_price;
執行結果：
product_id | product_name | sale_price | purchase_price------------+--------------+------------+---------------- 0002 | 打孔器 | 500 | 320 0001 | T衫 | 1000 | 500 0007 | 擦菜板 | 880 | 790 0003 | 運動T衫 | 4000 | 2800 0004 | 菜刀 | 3000 | 2800 0005 | 高壓鍋 | 6800 | 5000 0006 | 叉子 | 500 | 0008 | 圓珠筆 | 100 |(8 行記錄)
如上所示，排序鍵包含NULL時，會在開頭或者末尾進行匯總。
5：幾點關於ORDER BY子句的事項
● 在ORDER BY 子句中可以使用SELECT子句中定義的別名。
● 在ORDER BY 子句中可以使用SLEECT子句中為使用的列和聚合函數。● 在ORDER BY 子句中不要使用列編號。路漫漫其修遠兮，吾將上下而求索。
每天學習一點點，每天進步一點點。
推薦閱讀：
※OCP-1Z0-051 第十題
※《基於oracle學習SQL語言》學習筆記——第三章 單行函數
※SQL速覽
※SQL默示錄（一）
※索引列只要參與了計算, 查詢就會不走索引, 為什麼 MySQL 不對這種情況進行優化?