1 使用聚集函數(shù)匯總數(shù)據(jù)（AVG、COUNT、MAX、MIN、SUM）
我們經(jīng)常需要匯總數(shù)據(jù)而不用把它們都檢索出來(lái)。比如：
- 確定表中行數(shù)（或者滿足某個(gè)條件或包含某個(gè)特定值的行數(shù)）。
- 獲得表中某些行的和。
- 找出表列（或所有行或者某些特定行）的最大值、最小值、平均值。
聚集函數(shù)（aggregate function）：對(duì)某些行運(yùn)行的函數(shù)，計(jì)算并返回一個(gè)值。
SQL給了5個(gè)聚集函數(shù)。
| 函數(shù) | 說(shuō)明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行數(shù) |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值的總和 |
1.1 AVG()函數(shù)
AVG()函數(shù)通過(guò)對(duì)表中行數(shù)計(jì)數(shù)并計(jì)算其列值之和，求得該列的平均值。AVG()可用來(lái)返回所有列的平均值，也可以返回特定列或行的平均值。
計(jì)算Products表中所有產(chǎn)品的平均價(jià)格：
SELECT AVG(prod_price) AS avg_price
FROM Products;
計(jì)算特定供應(yīng)商DLL01所提供產(chǎn)品的平均價(jià)格：
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
注意：AVG()只能用來(lái)確定特定數(shù)值列的平均值，而且列名必須作為函數(shù)參數(shù)給出。為了獲得多個(gè)列的平均值，必須使用多個(gè)AVG()函數(shù)，如AVG(num1)、AVG(num2)、AVG(num3)等。
AVG()函數(shù)在計(jì)算時(shí)，會(huì)忽略列值為NULL的行。
1.2 COUNT()函數(shù)
COUNT()函數(shù)進(jìn)行計(jì)數(shù)，可確定表中行的數(shù)目或符合特定條件的行的數(shù)目。
COUNT()函數(shù)有兩種使用方式：
- 使用COUNT(*)對(duì)表中行的數(shù)目進(jìn)行計(jì)數(shù)，不管表列中包含的是空值NULL還是非空值。
- 使用COUNT(column)對(duì)特定列中具有值的行進(jìn)行計(jì)數(shù)，忽略NULL值。
計(jì)算Customers表中顧客的總數(shù)：
SELECT COUNT(*) AS num_cust
FROM Customers;
只計(jì)算有Email的顧客：
SELECT COUNT(cust_email) AS num_cust
FROM Customers;
1.3 MAX()函數(shù)
MAX()函數(shù)返回指定列中的最大值，要求必須指定列名。
計(jì)算Products表中最貴物品的價(jià)格：
SELECT MAX(prod_price) AS max_price
FROM Products;
雖然MAX()一般用來(lái)找出最大的數(shù)值或日期值，但許多DBMS允許將它用來(lái)返回任意列中的最大值，包括返回文本列中的最大值。用于文本數(shù)據(jù)時(shí)，MAX()返回按該列排序后的最后一行。
MAX()函數(shù)會(huì)忽略列值為NULL的行。
1.4 MIN()函數(shù)
MIN()函數(shù)返回指定列中的最小值，要求必須指定列名。
計(jì)算Products表中最便宜物品的價(jià)格：
SELECT MIN(prod_price) AS min_price
FROM Products;
雖然MIN()一般用來(lái)找出最小的數(shù)值或日期值，但許多DBMS允許將它用來(lái)返回任意列中的最小值，包括返回文本列中的最小值。用于文本數(shù)據(jù)時(shí)，MIN()返回按該列排序后最前面的行。
MIN()函數(shù)會(huì)忽略列值為NULL的行。
1.5 SUM()函數(shù)
SUM()函數(shù)用來(lái)返回指定列值的和（總計(jì)）。
計(jì)算訂單號(hào)為20005的所購(gòu)物品的總數(shù)：
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;
計(jì)算訂單號(hào)為20005的所購(gòu)物品，花費(fèi)的總金額：
SELECT SUM(item_price * quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;
SUM()函數(shù)會(huì)忽略列值為NULL的行。
1.6 聚集不同的值
- 對(duì)所有行執(zhí)行計(jì)算，指定ALL參數(shù)或者不指定參數(shù)（ALL是默認(rèn)行為）。
- 只包含不同的值，指定DISTINCT參數(shù)。
計(jì)算特定供應(yīng)商DLL01提供的產(chǎn)品的不同價(jià)格的平均值，也就是排除相同價(jià)格的產(chǎn)品。
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
在使用了DISTINCT之后，發(fā)現(xiàn)avg_price比較高，是因?yàn)橛卸鄠€(gè)產(chǎn)品具有相同的較低價(jià)格，排除它們，提高了平均價(jià)格。
注意：如果指定列名，則DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。DISTINCT必須使用列名，不能用于計(jì)算或表達(dá)式。
有些DBMS支持對(duì)查詢結(jié)果子集進(jìn)行計(jì)算的TOP和TOP PERCENT。
1.7 組合聚集函數(shù)
計(jì)算Products表中物品的數(shù)目，產(chǎn)品價(jià)格的最大值、最小值以及平均值。
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;
2 分組數(shù)據(jù)（GROUP BY、HAVING）
2.1 創(chuàng)建分組（GROUP BY）
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
在使用GROUP BY子句前，需要知道一些重要的規(guī)定。
- GROUP BY子句可以包含任意數(shù)目的列，因而可以對(duì)分組進(jìn)行嵌套，更細(xì)致地進(jìn)行數(shù)據(jù)分組。
- 如果在GROUP BY子句中嵌套了分組，數(shù)據(jù)將在最后指定的分組上進(jìn)行匯總。換句話說(shuō)，在建立分組時(shí)，指定的所有列都一起計(jì)算，不能從個(gè)別的列取回?cái)?shù)據(jù)。
- GROUP BY子句中列出的每一列都必須是檢索列或有效的表達(dá)式（但不能是聚集函數(shù)）。如果在SELECT中使用表達(dá)式，則必須在GROUP BY子句中指定相同的表達(dá)式，不能使用別名。
- 大多數(shù)SQL實(shí)現(xiàn)不允許GROUP BY列帶有長(zhǎng)度可變的數(shù)據(jù)類型（如文本或備注型字段）。
- 除聚集計(jì)算語(yǔ)句外，SELECT語(yǔ)句中的每一列都必須在GROUP BY子句中給出。
- 如果分組列中包含具有NULL值的行，則NULL將作為一個(gè)分組返回。如果列中有多行NULL值，它們將分為一組。
- GROUP BY子句必須出現(xiàn)在WHERE子句之后，ORDER BY子句之前。
有的SQL實(shí)現(xiàn)允許根據(jù)SELECT列表中的位置指定GROUP BY的列，例如，GROUP BY 2,1可表示按選擇的第二個(gè)列分組，然后再按第一個(gè)列分組。
2.2 過(guò)濾分組（HAVING）
除了能用GROUP BY分組數(shù)據(jù)外，SQL還允許過(guò)濾分組。規(guī)定包括哪些分組，排除哪些分組。例如，你可能想要列出至少有兩個(gè)訂單的所有顧客。為此，必須基于完整的分組而不是個(gè)別的行進(jìn)行過(guò)濾。
在這個(gè)例子中WHERE不能完成任務(wù)，因?yàn)閃HERE過(guò)濾指定的是行而不是分組。事實(shí)上，WHERE沒(méi)有分組的概念。
那么，不使用WHERE使用什么呢？SQL為此提供了另一個(gè)子句，就是HAVING子句。HAVING非常類似于WHERE，唯一的區(qū)別是，WHERE過(guò)濾行，而HAVING過(guò)濾分組。
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
HAVING過(guò)濾了COUNT(*)>=2（兩個(gè)以上訂單）的那些分組。
WHERE子句在這里不起作用，因?yàn)檫^(guò)濾是基于分組聚集值，而不是特定行的值。
WHERE在數(shù)據(jù)分組前進(jìn)行過(guò)濾，HAVING在數(shù)據(jù)分組后進(jìn)行過(guò)濾。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
這條語(yǔ)句中，第一行是使用了聚集函數(shù)的基本SELECT語(yǔ)句，很像前面的例子。WHERE子句過(guò)濾所有prod_price至少為4的行，然后按vend_id分組數(shù)據(jù)，HAVING子句過(guò)濾計(jì)數(shù)為2或2以上的分組。如果沒(méi)有WHERE子句，就會(huì)多檢索一行（供應(yīng)商DLL01，銷售4個(gè)產(chǎn)品，價(jià)格都在4以下）。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 2;
注意：使用HAVING時(shí)應(yīng)該結(jié)合GROUP BY子句，而WHERE子句用于標(biāo)準(zhǔn)的行級(jí)過(guò)濾。
2.3 分組和排序（GROUP BY、ORDER BY）
GROUP BY和ORDER BY經(jīng)常完成相同的工作，但它們非常不同。
| ORDER BY | GROUP BY |
|---|---|
| 對(duì)產(chǎn)生的輸出排序 | 對(duì)行分組，但輸出可能不是分組的順序 |
| 任意列都可以使用（甚至非選擇的列也可以使用） | 只可能使用選擇列或表達(dá)式列，而且必須使用每個(gè)選擇列表達(dá)式 |
| 不一定需要 | 如果與聚集函數(shù)一起使用列（或表達(dá)式），則必須使用 |
一般在使用GROUP BY子句時(shí)，應(yīng)該也給出ORDER BY子句。這是保證數(shù)據(jù)正確排序的唯一方法，千萬(wàn)不要僅依賴GROUP BY排序數(shù)據(jù)。
檢索包含三個(gè)或更多物品的訂單號(hào)和訂購(gòu)物品的數(shù)目。
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;
要按訂購(gòu)物品的數(shù)目排序輸出，需要添加ORDER BY子句。
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
2.4 SELECT子句順序
SELECT子句及其順序
| 子句 | 說(shuō)明 | 是否必須 |
|---|---|---|
| SELECT | 要返回的列或表達(dá)式 | 是 |
| FROM | 從中檢索數(shù)據(jù)的表 | 僅在從表選擇數(shù)據(jù)時(shí)使用 |
| WHERE | 行級(jí)過(guò)濾 | 否 |
| GROUP BY | 分組說(shuō)明 | 僅在按組計(jì)算聚集時(shí)使用 |
| HAVING | 組級(jí)過(guò)濾 | 否 |
| ORDER BY | 輸出排序順序 | 否 |
如果您發(fā)現(xiàn)文中有不清楚或者有問(wèn)題的地方，請(qǐng)?jiān)谙路皆u(píng)論區(qū)留言，我會(huì)根據(jù)您的評(píng)論，更新文中相關(guān)內(nèi)容，謝謝！
