在數據庫操作中，聚合函數是一類非常重要的函數，它們用于對一組值執行計算并返回單個值。MySQL提供了多種聚合函數，如COUNT、SUM、AVG、MIN和MAX等。這些函數在數據分析和報表生成中扮演著關鍵角色。本文將深入探討這些聚合函數的使用方法、注意事項以及一些高級技巧。
1. 聚合函數概述
聚合函數主要用于對一組值進行計算，并返回一個單一的值。常見的聚合函數包括：
COUNT()：計算行數。SUM()：計算數值列的總和。AVG()：計算數值列的平均值。MIN()：找出數值列的最小值。MAX()：找出數值列的最大值。
這些函數通常與GROUP BY子句一起使用，以便對分組后的數據進行聚合計算。
2. 常用聚合函數詳解
2.1 COUNT()
COUNT()函數用于計算表中的行數。它可以用于計算所有行或滿足特定條件的行。
-- 計算表中的總行數
SELECT COUNT(*) FROM employees;-- 計算特定條件下的行數
SELECT COUNT(*) FROM employees WHERE department = 'Sales';
2.2 SUM()
SUM()函數用于計算數值列的總和。
-- 計算所有員工的工資總和
SELECT SUM(salary) FROM employees;-- 計算特定部門的工資總和
SELECT SUM(salary) FROM employees WHERE department = 'Engineering';
注意： SUM函數用于求和，只能用于數字類型，字符類型的統計結果為0，日期類型統計結果是毫秒數相加。
2.3 AVG()
AVG()函數用于計算數值列的平均值。
-- 計算所有員工的平均工資
SELECT AVG(salary) FROM employees;-- 計算特定部門的平均工資
SELECT AVG(salary) FROM employees WHERE department = 'Marketing';
2.4 MIN()
MIN()函數用于找出數值列的最小值。
-- 找出所有員工中的最低工資
SELECT MIN(salary) FROM employees;-- 找出特定部門的最低工資
SELECT MIN(salary) FROM employees WHERE department = 'HR';
2.5 MAX()
MAX()函數用于找出數值列的最大值。
-- 找出所有員工中的最高工資
SELECT MAX(salary) FROM employees;-- 找出特定部門的最高工資
SELECT MAX(salary) FROM employees WHERE department = 'Finance';
3. 聚合函數與GROUP BY
GROUP BY子句用于將結果集按一個或多個列進行分組。聚合函數通常與GROUP BY一起使用，以便對每個分組進行聚合計算。
-- 按部門分組，計算每個部門的員工數
SELECT department, COUNT(*) FROM employees GROUP BY department;-- 按部門分組，計算每個部門的平均工資
SELECT department, AVG(salary) FROM employees GROUP BY department;
4. 聚合函數與HAVING
HAVING子句用于過濾分組后的結果集。與WHERE子句不同，HAVING可以用于過濾聚合函數的結果。
-- 找出平均工資大于5000的部門
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;-- 找出員工數超過10人的部門
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
5. 聚合函數的注意事項
- NULL值處理：聚合函數通常忽略
NULL值。例如，COUNT(column_name)不會計算NULL值。 - 性能考慮：在大數據集上使用聚合函數可能會影響性能，尤其是在沒有適當索引的情況下。
- 數據類型：確保聚合函數應用于正確的數據類型。例如，
SUM()和AVG()應應用于數值列。
6. 高級技巧
6.1 使用DISTINCT
可以在聚合函數中使用DISTINCT關鍵字，以便只對唯一值進行計算。
-- 計算不同部門的數量
SELECT COUNT(DISTINCT department) FROM employees;-- 計算不同工資的總和
SELECT SUM(DISTINCT salary) FROM employees;
6.2 嵌套聚合函數
在某些情況下，可以嵌套使用聚合函數。
-- 計算每個部門的平均工資，然后找出這些平均工資中的最大值
SELECT MAX(avg_salary) FROM (SELECT AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_avg;
6.3 使用窗口函數
MySQL 8.0及以上版本支持窗口函數，可以在不分組的情況下進行聚合計算。
-- 計算每個員工的工資以及所在部門的平均工資
SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department) AS avg_department_salary FROM employees;
7. 總結
聚合函數是MySQL中非常強大的工具，能夠幫助我們輕松地對數據進行匯總和分析。通過結合GROUP BY和HAVING子句，我們可以實現更復雜的數據分組和過濾操作。掌握這些函數的使用方法和注意事項，將極大地提升我們在數據庫操作中的效率和靈活性。
