【MySQL基础-9】深入理解MySQL中的聚合函数

在数据库操作中，聚合函数是一类非常重要的函数，它们用于对一组值执行计算并返回单个值。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子句，我们可以实现更复杂的数据分组和过滤操作。掌握这些函数的使用方法和注意事项，将极大地提升我们在数据库操作中的效率和灵活性。