MariaDB 学习总结（八）：聚合函数及分组查询

一、聚合函数概述

聚合函数是对一组值执行计算并返回单一值的函数，常用于数据统计和分析场景。MariaDB 提供了丰富的聚合函数，以下是常用的几种：

1. 常用聚合函数

• COUNT()：计数
• SUM()：求和
• AVG()：平均值
• MAX()：最大值
• MIN()：最小值
• GROUP_CONCAT()：连接字符串
• STDDEV() / STDDEV_POP()：标准差
• VARIANCE() / VAR_POP()：方差

二、基本聚合函数使用

1. COUNT() 函数

-- 计算表中的记录数
SELECT COUNT(*) FROM employees;

-- 计算某列非NULL值的数量
SELECT COUNT(department_id) FROM employees;

-- 计算不重复值的数量
SELECT COUNT(DISTINCT department_id) FROM employees;

2. SUM() 函数

-- 计算某列总和
SELECT SUM(salary) FROM employees;

-- 带条件的求和
SELECT SUM(salary) FROM employees WHERE department_id = 10;

3. AVG() 函数

-- 计算平均值
SELECT AVG(salary) FROM employees;

-- 计算非NULL值的平均值
SELECT AVG(commission_pct) FROM employees;

4. MAX() 和 MIN() 函数

-- 最大值和最小值
SELECT MAX(salary), MIN(salary) FROM employees;

-- 日期最大值
SELECT MAX(hire_date) FROM employees;

三、分组查询 (GROUP BY)

GROUP BY 子句将结果集按一个或多个列的值分组，通常与聚合函数一起使用。

1. 基本分组

-- 按部门分组计算平均工资
SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id;

2. 多列分组

-- 按部门和职位分组
SELECT department_id, job_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id, job_id;

3. 分组后筛选 (HAVING)

HAVING 子句用于对分组后的结果进行筛选，类似于 WHERE 但作用于分组后的数据。

-- 筛选平均工资大于8000的部门
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary > 8000;

4. 分组排序

-- 按部门平均工资降序排列
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

四、高级聚合函数

1. GROUP_CONCAT() 函数

将分组中的值连接成字符串，常用于生成逗号分隔的列表。

-- 列出每个部门的员工姓名
SELECT department_id, GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ')
FROM employees
GROUP BY department_id;

2. 窗口函数 (MariaDB 10.2+)

虽然严格来说不是聚合函数，但窗口函数常与聚合一起使用。

-- 计算每个员工的工资及其所在部门的平均工资
SELECT 
    first_name, 
    salary,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
FROM employees;

五、注意事项

1. NULL 值处理：大多数聚合函数忽略 NULL 值，COUNT(*) 除外
2. 性能考虑：在大表上使用 GROUP BY 可能很耗资源，确保有适当的索引
3. HAVING vs WHERE：
   • WHERE 在分组前过滤行
   • HAVING 在分组后过滤组
4. GROUP BY 与 DISTINCT：有时可以用 DISTINCT 替代简单的 GROUP BY 查询

六、实践示例

-- 统计各部门人数、平均工资、最高和最低工资
SELECT 
    department_id,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary,
    GROUP_CONCAT(last_name ORDER BY salary DESC SEPARATOR ', ') as employees
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING employee_count > 3
ORDER BY avg_salary DESC;

通过掌握这些聚合函数和分组查询技术，您可以有效地从 MariaDB 数据库中提取汇总信息和统计数据，为业务分析和决策提供支持。