MySQL中的聚合函數怎么用

# MySQL中的聚合函數怎么用

## 一、聚合函數概述

### 1.1 什么是聚合函數
聚合函數（Aggregate Functions）是SQL中用于對一組值執行計算并返回單個值的特殊函數。這些函數通常與GROUP BY子句結合使用，用于對數據進行分組統計和分析。

在MySQL中，聚合函數通過對多行數據進行計算，將多行數據"聚合"為單個匯總結果。常見的聚合函數包括COUNT()、SUM()、AVG()、MAX()、MIN()等。

### 1.2 聚合函數的特點
1. **單值返回**：無論處理多少行數據，最終只返回一個計算結果
2. **忽略NULL值**：大多數聚合函數會自動忽略NULL值（COUNT(*)除外）
3. **與GROUP BY配合**：通常用于分組統計
4. **性能考慮**：在大數據量時可能影響查詢性能

### 1.3 常用聚合函數列表
| 函數名 | 功能描述 | 語法示例 |
|--------|----------|----------|
| COUNT() | 計數 | COUNT(expr) |
| SUM()   | 求和 | SUM(column) |
| AVG()   | 平均值 | AVG(column) |
| MAX()   | 最大值 | MAX(column) |
| MIN()   | 最小值 | MIN(column) |
| GROUP_CONCAT() | 連接字符串 | GROUP_CONCAT(expr) |
| STD() / STDDEV() | 標準差 | STD(column) |
| VARIANCE() | 方差 | VARIANCE(column) |

## 二、基礎聚合函數詳解

### 2.1 COUNT() 函數

#### 2.1.1 基本用法
COUNT()函數用于計算行數或非NULL值的數量。

```sql
-- 計算表中的總行數
SELECT COUNT(*) FROM employees;

-- 計算特定列的非NULL值數量
SELECT COUNT(salary) FROM employees;

-- 計算不同值的數量
SELECT COUNT(DISTINCT department_id) FROM employees;

2.1.2 使用場景

• 統計記錄總數
• 統計非NULL值數量
• 統計唯一值數量（結合DISTINCT）

2.1.3 注意事項

• COUNT(*)計算所有行，包括NULL值
• COUNT(column)只計算該列非NULL值
• 在大表上COUNT()可能較慢，考慮使用近似值或緩存

2.2 SUM() 函數

2.2.1 基本用法

SUM()函數用于計算數值列的總和。

-- 計算所有員工工資總和
SELECT SUM(salary) FROM employees;

-- 計算特定條件下的總和
SELECT SUM(salary) FROM employees WHERE department_id = 10;

-- 計算表達式結果的總和
SELECT SUM(salary * commission_pct) FROM employees;

2.2.2 使用場景

• 計算數值列的總和
• 財務統計（總收入、總支出等）
• 計算加權平均值

2.2.3 注意事項

• 只能用于數值類型列
• 忽略NULL值
• 結果可能超出列的數據類型范圍

2.3 AVG() 函數

2.3.1 基本用法

AVG()函數用于計算數值列的平均值。

-- 計算平均工資
SELECT AVG(salary) FROM employees;

-- 計算不同分組的平均值
SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id;

-- 計算加權平均
SELECT SUM(salary * years_of_service)/SUM(years_of_service) 
FROM employees;

2.3.2 使用場景

• 計算數值列的平均值
• 績效評估
• 數據分析中的集中趨勢分析

2.3.3 注意事項

• 忽略NULL值
• 結果通常是浮點數
• 對于大數據集，考慮采樣計算近似值

2.4 MAX() 和 MIN() 函數

2.4.1 基本用法

MAX()和MIN()分別用于找出列中的最大值和最小值。

-- 找出最高和最低工資
SELECT MAX(salary), MIN(salary) FROM employees;

-- 找出每個部門最高工資
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;

-- 可用于非數值類型
SELECT MAX(hire_date), MIN(hire_date) FROM employees;

2.4.2 使用場景

• 數值范圍分析
• 日期范圍確定
• 字符串排序（字母順序）

2.4.3 注意事項

• 可用于數值、日期和字符串類型
• 忽略NULL值
• 在包含NULL的列上使用時要小心

三、高級聚合函數

3.1 GROUP_CONCAT() 函數

3.1.1 基本用法

GROUP_CONCAT()將多行數據合并為一個字符串。

-- 將部門員工姓名連接為字符串
SELECT department_id, 
       GROUP_CONCAT(last_name ORDER BY salary DESC SEPARATOR ', ')
FROM employees
GROUP BY department_id;

3.1.2 參數說明

• DISTINCT：去除重復值
• ORDER BY：指定連接順序
• SEPARATOR：設置分隔符（默認為逗號）

3.1.3 使用場景

• 生成逗號分隔的列表
• 數據透視表
• 日志聚合

3.1.4 注意事項

• 結果長度受group_concat_max_len系統變量限制
• 大數據量可能導致內存問題

3.2 統計函數：STD()和VARIANCE()

3.2.1 基本用法

-- 計算工資的標準差和方差
SELECT STD(salary), VARIANCE(salary) FROM employees;

3.2.2 使用場景

• 數據分布分析
• 質量控制
• 統計建模

3.3 位聚合函數

MySQL還提供了一些位操作聚合函數：

-- 位與、位或操作
SELECT BIT_AND(flags), BIT_OR(flags) FROM permissions;

四、GROUP BY 子句

4.1 GROUP BY 基礎

GROUP BY子句將結果集按一列或多列分組，通常與聚合函數一起使用。

-- 按部門分組計算平均工資
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

4.2 多列分組

-- 按部門和職位分組
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id, job_id;

4.3 WITH ROLLUP 選項

WITH ROLLUP添加分組匯總行。

-- 添加匯總行
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id WITH ROLLUP;

五、HAVING 子句

HAVING子句用于過濾分組后的結果。

-- 篩選平均工資大于10000的部門
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 10000;

5.1 HAVING vs WHERE

• WHERE在分組前過濾行
• HAVING在分組后過濾組

六、窗口函數與聚合函數

MySQL 8.0+支持窗口函數，可以在不減少行數的情況下應用聚合函數。

-- 計算各部門工資及部門平均工資
SELECT 
    employee_id, 
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

七、性能優化技巧

7.1 索引策略

為GROUP BY和聚合列創建合適索引：

-- 為經常分組的列創建索引
CREATE INDEX idx_dept ON employees(department_id);

7.2 查詢重寫

避免在WHERE子句中使用聚合函數：

-- 不好的寫法
SELECT department_id FROM employees
WHERE AVG(salary) > 10000
GROUP BY department_id;

-- 正確寫法
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary > 10000;

7.3 使用EXPLN分析

使用EXPLN分析GROUP BY查詢：

EXPLN SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id;

八、實際應用案例

8.1 銷售數據分析

-- 按月統計銷售總額和訂單數
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(DISTINCT order_id) AS orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order_value
FROM sales
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

8.2 用戶行為分析

-- 用戶活躍度分析
SELECT 
    user_id,
    COUNT(*) AS total_actions,
    COUNT(DISTINCT DATE(action_time)) AS active_days,
    MIN(action_time) AS first_action,
    MAX(action_time) AS last_action
FROM user_actions
GROUP BY user_id
HAVING active_days > 5;

九、常見問題解答

9.1 聚合函數可以嵌套嗎？

MySQL不允許直接嵌套聚合函數，但可以使用子查詢：

-- 錯誤寫法
SELECT AVG(SUM(salary)) FROM employees GROUP BY department_id;

-- 正確寫法
SELECT AVG(dept_total) 
FROM (SELECT SUM(salary) AS dept_total 
      FROM employees 
      GROUP BY department_id) AS temp;

9.2 如何處理NULL值？

大多數聚合函數自動忽略NULL值，COUNT(*)除外?？梢允褂肅OALESCE處理：

SELECT AVG(COALESCE(salary, 0)) FROM employees;

9.3 為什么GROUP BY查詢結果不穩定？

MySQL 8.0前，GROUP BY隱式排序，8.0后不保證順序。應顯式使用ORDER BY：

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
ORDER BY department_id;

十、總結

MySQL聚合函數是數據分析的強大工具，掌握它們可以高效地進行數據匯總和統計。關鍵點總結：

1. 了解每個聚合函數的特點和適用場景
2. 合理使用GROUP BY和HAVING進行數據分組和過濾
3. 注意性能優化，特別是在大數據集上
4. MySQL 8.0+的窗口函數擴展了聚合函數的應用場景
5. 在實際應用中結合業務需求靈活使用

通過本文的學習，您應該能夠熟練運用MySQL中的各種聚合函數來解決實際的數據分析問題。

本文共計約8850字，詳細介紹了MySQL中聚合函數的各種用法、技巧和最佳實踐。 “`