Like Share Discussion Bookmark Smile

J.J. Huang   2020-06-20   MySQL   瀏覽次數:

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經常完成相同的工作,但它們是非常不同的。

ORDER BY GROUP 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語句中使用時必須遵循的次序:

子句 說明 是否必須使用
SELECT 要返回的列或表達式
FROM 從中檢索資料的表 僅在從表選擇資料時使用
WHERE 行級過濾
GROUP BY 分組說明 僅在按組計算聚集時使用
HAVING 組級過濾
ORDER BY 輸出排序順序
LIMIT 要檢索的行數

結語

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

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