Like Share Discussion Bookmark Smile

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

MySQL - 第十二章 | 匯總資料

聚集函數

我們經常需要匯總資料而不用把它們實際檢索出來,為此MySQL提供了專門的函數。使用這些函數,MySQL查詢可用於檢索資料,以便分析和報表生成。這種類型的檢索例子有以下幾種。

  • 確定表中行數(或者滿足某個條件或包含某個特定值的行數)。
  • 獲得表中行組的和。
  • 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。

上述例子都需要對表中資料(而不是實際資料本身)匯總。因此,返回實際表資料是對時間和處理資源的一種浪費。
重復一遍,實際想要的是匯總訊息。

為方便這種類型的檢索,MySQL給出了5個聚集函數。這些函數能進行上述羅列的檢索。

術語:聚集函數(aggregate function) 運行在行組上,計算和返回單個值的函數。

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

AVG()函數

AVG()通過對表中行數計數併計算特定列值之和,求得該列的平均值。
AVG()可用來返回所有列的平均值,也可以用來返回特定列或行的平均值。

注意:只用於單個列AVG()只能用來確定特定數值列的平均值,而且列名必須作為函數參數給出。為了獲得多個列的平均值,必須使用多個AVG()函數。

說明:NULL值 AVG()函數忽略列值為NULL的行。

1
2
3
4
5
6
7
mysql> SELECT AVG(prod_price) AS avg_price FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.00 sec)

分析:此SELECT語句返回值avg_Price,它包含products表中所有產品的平均價格。

1
2
3
4
5
6
7
mysql> SELECT AVG(prod_price) AS avg_price FROM products Where vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.00 sec)

分析:此WHERE子句僅過濾出vend_id為1003的產品,因此avg_price中返回的值只是該供應商的產品的平均值。

COUNT()函數

COUNT()函數進行計數。可利用COUNT()確定表中行的數目或符合特定條件的行的數目。

COUNT()函數有兩種使用方式:

  • 使用COUNT(*)對表中行的數目進行計數,不管表列中包含的是空值(NULL)還是非空值。
  • 使用COUNT(column)對特定列中具有值的行進行計數,忽略NULL值。

說明:NULL值 如果指定列名,則指定列的值為空的行被COUNT()函數忽略,但如果COUNT()函數中用的是星號(*),則不忽略。

1
2
3
4
5
6
7
mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)

分析:利用COUNT(*)對所有行計數,不管行中各列有什麼值。

1
2
3
4
5
6
7
mysql> SELECT COUNT(cust_email) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

分析:使用COUNT(cust_email)對cust_email列中有值的行進行計數。cust_email的計數為3(表示5個客戶中只有3個客戶有電子郵件地址)。

MAX()函數

MAX()返回指定列中的最大值。

提示:對非數值資料使用MAX() 雖然MAX()一般用來找出最大的數值或日期值,但MySQL允許將它用來返回任意列中的最大值,包括返回文本列中的最大值。在用於文本資料時,如果資料按相應的列排序,則MAX()返回最後一行。

說明:NULL值 MAX()函數忽略列值為NULL的行。

1
2
3
4
5
6
7
mysql> SELECT MAX(prod_price) AS max_price FROM products;
+-----------+
| max_price |
+-----------+
| 55.00 |
+-----------+
1 row in set (0.00 sec)

分析:MAX()返回products表中最貴的物品的價格。

MIN()函數

MIN()的功能正好與MAX()功能相反,它返回指定列的最小值。與MAX()一樣,MIN()要求指定列名。

提示:對非數值資料使用MIN() MIN()函數與MAX()函數類似,MySQL允許將它用來返回任意列中的最小值,包括返回文本列中的最小值。在用於文本資料時,如果資料按相應的列排序,則MIN()返回最前面的行。

說明:NULL值 MIN()函數忽略列值為NULL的行。

1
2
3
4
5
6
7
mysql> SELECT MIN(prod_price) AS max_price FROM products;
+-----------+
| max_price |
+-----------+
| 2.50 |
+-----------+
1 row in set (0.00 sec)

分析:MIN()返回products表中最便宜物品的價格。

SUM()函數

SUM()用來返回指定列值的和(總計)。

提示:在多個列上進行計算 利用標準的算術操作符,所有聚集函數都可用來執行多個列上的計算。

說明:NULL值 SUM()函數忽略列值為NULL的行。

1
2
3
4
5
6
7
mysql> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
1 row in set (0.01 sec)

分析:函數SUM(quantity)返回訂單中所有物品數量之和,WHERE子句保證只統計某個物品訂單中的物品。

SUM()也可以用來合計計算值。另外合計每項物品的item_price * quantity,得出總的訂單金額。

1
2
3
4
5
6
7
mysql> SELECT SUM(item_price * quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 149.87 |
+---------------+
1 row in set (0.00 sec)

分析:函數SUM(item_price * quantity)返回訂單中所有物品價錢之和,WHERE子句同樣保證只統計某個物品訂單中的物品。

聚集不同值

以上5個聚集函數都可以如下使用:

  • 對所有的行執行計算,指定ALL參數或不給參數(因為ALL是默認行為)。
  • 只包含不同的值,指定DISTINCT參數。

提示:ALL為默認 ALL參數不需要指定,因為它是默認行為。如果不指定DISTINCT,則假定為ALL。

注意:如果指定列名,則DISTINCT只能用於COUNT()。DISTINCT不能用於COUNT(*),因此不允許使用COUNT(DISTINCT),否則會產生錯誤。類似地,DISTINCT必須使用列名,不能用於計算或表達式。

提示:將DISTINCT用於MIN()和MAX()雖然DISTINCT從技術上可用於MIN()和MAX(),但這樣做實際上沒有價值。一個列中的最小值和最大值不管是否包含不同值都是相同的。

組合聚集函數

目前為止的所有聚集函數例子都只涉及單個函數。但實際上SELECT語句可根據需要包含多個聚集函數。

1
2
3
4
5
6
7
mysql> 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;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)

分析:單條SELECT語句執行了4個聚集計算,返回4個值(products表中物品的數目,產品價格的最高、最低以及平均)。

提示:取別名 在指定別名以包含某個聚集函數的結果時,不應該使用表中實際的列名。雖然這樣做並非不合法,但使用唯一的名字會使你的SQL更易於理解和使用(以及將來容易排除故障)。

結語

這些函數平常其實使用上就很頻繁,需要特別注意的就是「函數忽略列值為NULL的行」的部分。常常會不小心就忽略了這個問題,已導致與自己認知不一致的問題。

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