文章目錄
- 五種常用的聚合函數
- 什麼是聚合函數
- AVG和SUM函數
- MIN和MAX函數
- COUNT函數
- 有關COUNT的效率問題
- GROUP BY
- 基本實作
- 使用多個列分組
- GROUP BY中使用WITH ROLLUP
- HAVING
- HAVING的使用
- WHERE和HAVING的對比
五種常用的聚合函數
什麼是聚合函數
聚合函數作用于一組資料，并對一組資料傳回一個值。
聚合函數不能嵌套調用。比如不能出現類似“AVG(SUM(字段名稱))”形式的調用。
AVG和SUM函數
AVG函數：求平均值
SUM函數：求總和
AVG / SUM ：隻适用于數值類型的字段（或變量）
例如：
SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees; 結果：
MIN和MAX函數
MIN函數：求最小值
MAX函數：求最大值
可以對任意資料類型的資料使用 MIN 和 MAX 函數。
例如：
SELECT MAX(salary),MIN(salary)
FROM employees;
SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees; 結果：
既然在ORDER BY中字元串可以排序，那麼顯然字元串是有大有小的，在MySQL中會根據字元串的ASCII碼值進行大小的比較。（先比較第一個字元，如果相同則繼續往後比較，如此繼續下去）
COUNT函數
COUNT函數：計算指定字段在查詢結構中出現的個數
注意：計算指定字段出現的個數時，是不計算NULL值的。
例如：
SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees ; 結果：
注：employees表中一共有107條員工的資訊，有些員工的相關字段會有NULL值
公式：AVG = SUM / COUNT
SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees; 結果：
由此我們可知：SUM函數、AVG函數是不會把NULL值計算進去的
有關COUNT的效率問題
如何需要統計表中的記錄數，使用COUNT(*)、COUNT(1)、COUNT(具體字段) 哪個效率更高呢？
如果使用的是MyISAM 存儲引擎，則三者效率相同，都是O(1)
如果使用的是InnoDB 存儲引擎，則三者效率：COUNT(*) = COUNT(1)> COUNT(字段)
GROUP BY
基本實作
作用：将表中的資料分成若幹組
文法：
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column]; 明确：GROUP BY 聲明在FROM後面、WHERE後面，ORDER BY 前面、LIMIT前面
使用多個列分組
例如：
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ; 結果：
注意：SELECT中出現的非組函數的字段必須聲明在GROUP BY 中。反之，GROUP BY中聲明的字段可以不出現在SELECT中。
GROUP BY中使用WITH ROLLUP
使用
WITH ROLLUP
關鍵字之後，在所有查詢出的分組記錄之後增加一條記錄，該記錄計算查詢出的所有記錄的總和，即統計記錄數量。
例如：
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP; 結果：
注意：當使用ROLLUP時，不能同時使用ORDER BY子句進行結果排序，即ROLLUP和ORDER BY是互相排斥的。
HAVING
HAVING的使用
HAVING的作用：過濾
使用要求：
- 如果過濾條件中使用了聚合函數，則必須使用HAVING來替換WHERE。否則，報錯。
- HAVING 必須聲明在 GROUP BY 的後面。
- 開發中，我們使用HAVING的前提是SQL中使用了GROUP BY。
例如：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000; WHERE和HAVING的對比
差別1：WHERE 可以直接使用表中的字段作為篩選條件，但不能使用分組中的計算函數作為篩選條件；HAVING 必須要與 GROUP BY 配合使用，可以把分組計算的函數和分組字段作為篩選條件。
這決定了，在需要對資料進行分組統計的時候，HAVING 可以完成 WHERE 不能完成的任務。這是因為，在查詢文法結構中，WHERE 在 GROUP BY 之前，是以無法對分組結果進行篩選。HAVING 在 GROUP BY 之後，可以使用分組字段和分組中的計算函數，對分組的結果集進行篩選，這個功能是 WHERE 無法完成的。另外，WHERE排除的記錄不再包括在分組中。
差別2：如果需要通過連接配接從關聯表中擷取需要的資料，WHERE 是先篩選後連接配接，而 HAVING 是先連接配接後篩選。
這一點，就決定了在關聯查詢中，WHERE 比 HAVING 更高效。因為 WHERE 可以先篩選，用一個篩選後的較小資料集和關聯表進行連接配接，這樣占用的資源比較少，執行效率也比較高。HAVING 則需要先把結果集準備好，也就是用未被篩選的資料集進行關聯，然後對這個大的資料集進行篩選，這樣占用的資源就比較多，執行效率也較低。
開發中的選擇：
WHERE 和 HAVING 也不是互相排斥的，我們可以在一個查詢裡面同時使用 WHERE 和 HAVING。包含分組統計函數的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發揮了 HAVING 可以使用包含分組統計函數的查詢條件的優點。當資料量特别大的時候，運作效率會有很大的差别。
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;