MySQL - 第十三章 | 分組資料

資料分組

SQL聚集函數可用來匯總資料。這使我們能夠對行進行計數，計算和與平均數，獲得最大和最小值而不用檢索所有資料。目前為止的所有計算都是在表的所有資料或匹配特定的WHERE子句的資料上進行的。

但如果要返回每個供應商提供的產品數目怎麼辦?
或者返回只提供單項產品的供應商所提供的產品?
或返回提供10個以上產品的供應商怎麼辦?

這就是分組顯身手的時候了。分組允許把資料分為多個邏輯組，以便能對每個組進行聚集計算。

創建分組

分組是在SELECT語句的GROUP BY子句中建立的。

1
2
3
4
5
6
7
8
9
10

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

分析：上面的SELECT語句指定了兩個列，vend_id包含產品供應商的ID，num_prods為計算欄位(用COUNT(*)函數建立)。GROUP BY子句指示MySQL按vend_id排序並分組資料。這導致對每個vend_id而不是整個表計算num_prods一次。從輸出中可以看到，供應商1001有3個產品，供應商1002有2個產品，供應商1003有7個產品，而供應商1005有2個產品。

因為使用了GROUP BY，就不必指定要計算和估值的每個組了。系統會自動完成。GROUP BY子句指示MySQL分組資料，然後對每個組而不是 整個結果集進行聚集。

在具體使用GROUP BY子句前，需要知道一些重要的規定：

• GROUP BY子句可以包含任意數目的列。這使得能對分組進行嵌套，為資料分組提供更細緻的控制。
• 如果在GROUP BY子句中嵌套了分組，資料將在最後規定的分組上進行匯總。換句話說，在建立分組時，指定的所有列都一起計算(所以不能從個別的列取回資料)。
• GROUP BY子句中列出的每個列都必須是檢索列或有效的表達式(但不能是聚集函數)。如果在SELECT中使用表達式，則必須在GROUP BY子句中指定相同的表達式。不能使用別名。
• 除聚集計算語句外，SELECT語句中的每個列都必須在GROUP BY子句中給出。
• 如果分組列中具有NULL值，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。
• GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前。

提示：使用ROLLUP 使用WITHROLLUP關鍵字，可以得到每個分組以及每個分組匯總級別(針對每個分組)的值，如下所示:

1
2
3
4
5
6
7
8
9
10
11

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+
5 rows in set (0.00 sec)

過濾分組

除了能用GROUP BY分組資料外，MySQL還允許過濾分組，規定包括哪些分組，排除哪些分組。例如，可能想要列出至少有兩個訂單的所有顧客。為得出這種資料，必須基於完整的分組而不是個別的行進行過濾。

我們已經看到了WHERE子句的作用。但是，在這個例子中WHERE不能完成任務，因為WHERE過濾指定的是行而不是分組。事實上，WHERE沒有分組的概念。

那麼，不使用WHERE使用什麼呢?MySQL為此目的提供了另外的子句，那就是HAVING子句。HAVING非常類似於WHERE。事實上，目前為止所 學過的所有類型的WHERE子句都可以用HAVING來替代。唯一的差別是WHERE過濾行，而HAVING過濾分組。

提示：HAVING支持所有WHERE操作符 我們學習了WHERE子句的條件(包括通配符條件和帶多個操作符的子句)。所學過的有關WHERE的所有這些技術和選項都適用於HAVING。它們的句法是相同的，只是關鍵字有差別。

1
2
3
4
5
6
7

mysql> SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1 row in set (0.00 sec)

分析：這條SELECT語句的前3行類似於上面的語句。最後一行增加了HAVING子句，它過濾COUNT(*) >=2(兩個以上的訂單)的那些分組。

正如所見，這裡WHERE子句不起作用，因為過濾是基於分組聚集值而不是特定行值的。

提示：HAVING和WHERE的差別 這裡有另一種理解方法，WHERE在資料分組前進行過濾，HAVING在資料分組後進行過濾。這是一個重要的區別，WHERE排除的行不包括在分組中。這可能會改變計算值，從而影響HAVING子句中基於這些值過濾掉的分組。

那麼，有沒有在一條語句中同時使用WHERE和HAVING子句的需要呢? 事實上，確實有。假如想進一步過濾上面的語句，使它返回過去12個月內具有兩個以上訂單的顧客。為達到這一點，可增加一條WHERE子句，過濾出過去12個月內下過的訂單。然後再增加HAVING子句過濾出具有兩個以上訂單的分組。

為更好地理解，請看下面的例子，它列出具有2個(含)以上、價格 為10(含)以上的產品的供應商：

1
2
3
4
5
6
7
8

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

分析：這條語句中，第一行是使用了聚集函數的基本SELECT，它與前面的例子很相像。WHERE子句過濾所有prod_price至少為10的行。然後按vend_id分組資料，HAVING子句過濾計數為2或2以上的分組。如果沒有WHERE子句，將會多檢索出兩行(供應商1002，銷售的所有產品價格都在10以下;供應商1001，銷售3個產品，但只有一個產品的價格大 於等於10)

分組和排序

雖然GROUP BY和ORDER BY經常完成相同的工作，但它們是非常不同的。

表中列出的第一項差別極為重要。我們經常發現用GROUP BY分組的資料確實是以分組順序輸出的。但情況並不總是這樣，它並不是SQL規範所要求的。此外，用戶也可能會要求以不同於分組的順序排序。僅因為你以某種方式分組資料(獲得特定的分組聚集值)，並不表示你需要以相同的方式排序輸出。應該提供明確的ORDER BY子句，即使其效果等同於GROUP BY子句也是如此。

提示：不要忘記ORDER BY一般在使用GROUP BY子句時，應該也給出ORDER BY子句。這是保證資料正確排序的唯一方法。千萬不要僅依賴GROUP BY排序資料。

為說明GROUP BY和ORDER BY的使用方法，請看一個例子的SELECT語句類似於前面那些例子。它檢索總計訂單價格大於等於50的訂單的訂單號和總計訂單價格：

1
2
3
4
5
6
7
8
9
10

mysql> SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity * item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+
4 rows in set (0.01 sec)

為按總計訂單價格排序輸出，需要添加ORDER BY子句。

1
2
3
4
5
6
7
8
9
10

mysql> SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity * item_price) >= 50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.00 sec)

在這個例子中，GROUP BY子句用來按訂單號(order_num列)分組資料，以便SUM(*)函數能夠返回總計訂單價格。HAVING子句過濾資料，使得只返回總計訂單價格大於等於50的訂單。最後，用ORDER BY子句排序輸出。

SELECT子句順序

SELECT語句中子句的順序。下表以在SELECT語句中使用時必須遵循的次序：

結語

GROUP BY 和 ORDER BY 在SQL中其實是佔比滿重的，因為在資料查找都是需要做過分組和排序的，只要能夠靈活運用，可以解掉很多業務上的需求，對於這些和HAVING的使用都要特別好好理解必且使用上手。

註：以上參考了
MySQL必知必会 MySQL Crash Course