数据库中常见的聚合函数

目录

一、聚合函数是什么

二、常见的聚合函数

1、SUM()

2、AVG()

3、MAX()

4、MIN()

5、COUNT()

6、GROUP_CONCAT() / STRING_AGG()

7、STDDEV() / STDDEV_SAMP()

8、VAR_POP() / VARIANCE()

三、聚合函数的作用

1、数据汇总:

2、统计分析:

3、数据分组:

四、聚合函数的好处

1、高效性:

2、简洁性:

3、灵活性:

4、可读性:

五、注意事项

1、NULL 值处理:

2、数据类型:

3、子查询中的聚合函数:

4、窗口函数:

5、性能考虑:

6、结果精度:

六、结合 GROUP BY 使用聚合函数

七、总结

一、聚合函数是什么

聚合函数 是一种特殊的 SQL 函数，用于对一组值执行计算，并返回单个值。这些函数通常用于汇总和分析数据，帮助用户从大量数据中提取有意义的信息

二、常见的聚合函数

常见的聚合函数有：SUM()，AVG()，MAX()，MIN()，COUNT()，GROUP_CONCAT() (MySQL)，STDDEV()，STDDEV_SAMP()，VAR_POP()，VARIANCE()

1、SUM()

作用: 计算数值列的总和。

语法: SUM(column_name)

示例：

     SELECT SUM(salary) AS total_salary FROM employees; 

2、AVG()

作用: 计算数值列的平均值。

语法: AVG(column_name)

示例：

     SELECT AVG(age) AS average_age FROM employees;

3、MAX()

作用: 返回数值列中的最大值。

语法: MAX(column_name)

示例:

     SELECT MAX(price) AS max_price FROM products;

4、MIN()

作用: 返回数值列中的最小值。

语法: MIN(column_name)

示例：

     SELECT MIN(quantity) AS min_quantity FROM inventory;

5、COUNT()

作用: 计算某列中非空值的数量。

语法: COUNT(column_name) 或 COUNT(*)

示例:

     SELECT COUNT(employee_id) AS employee_count FROM employees;
     SELECT COUNT(*) AS total_records FROM orders;

6、GROUP_CONCAT() / STRING_AGG()

作用: 将多行数据合并成一个字符串。

MySQL: GROUP_CONCAT(column_name [SEPARATOR separator])

PostgreSQL: STRING_AGG(column_name, delimiter)

示例:

     -- MySQL
     SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ') AS employees
     FROM employees
     GROUP BY department;

     -- PostgreSQL
     SELECT department, STRING_AGG(employee_name, ', ') AS employees
     FROM employees
     GROUP BY department;
     

7、STDDEV() / STDDEV_SAMP()

作用: 计算标准差。

MySQL: STDDEV(column_name)

PostgreSQL: STDDEV(column_name)

示例:

     SELECT STDDEV(salary) AS salary_stddev FROM employees;

8、VAR_POP() / VARIANCE()

作用: 计算方差。

MySQL: VARIANCE(column_name)

PostgreSQL: VAR_POP(column_name)

示例:

     SELECT VARIANCE(salary) AS salary_variance FROM employees;

三、聚合函数的作用

1、数据汇总:

通过对一组数据进行求和、平均、最大值、最小值等操作，简化数据表示。

2、统计分析:

提供统计数据，如平均工资、最高价格、最低库存等，帮助进行业务分析和决策。

3、数据分组:

结合 GROUP BY 子句，可以对数据进行分组，并对每个组分别计算聚合值。

四、聚合函数的好处

1、高效性:

数据库引擎优化了聚合函数的性能，能够快速处理大规模数据。

2、简洁性:

使用聚合函数可以使 SQL 查询更加简洁明了，减少手动计算的需求。

3、灵活性:

可以根据不同的业务需求灵活地组合和嵌套聚合函数。

4、可读性:

清晰地表达了数据的计算意图，便于他人理解和维护。

五、注意事项

1、NULL 值处理:

大多数聚合函数会自动忽略 NULL 值，除非使用 COUNT(*) 来计数所有行。

示例:

     SELECT SUM(salary) AS total_salary FROM employees; -- 忽略 NULL 值
     SELECT COUNT(*) AS total_records FROM employees; -- 包括 NULL 值

2、数据类型:

确保聚合函数应用于适当的数据类型。例如，SUM() 和 AVG() 适用于数值类型。

示例:

     SELECT SUM(quantity) AS total_quantity FROM inventory; -- quantity 应为数值类型

3、子查询中的聚合函数:

聚合函数可以在子查询中使用，以获取更复杂的计算结果。

示例:

     SELECT department, MAX(avg_salary) AS highest_avg_salary
     FROM (
         SELECT department, AVG(salary) AS avg_salary
         FROM employees
         GROUP BY department
     ) AS dept_salaries
     GROUP BY department;
     

4、窗口函数:

窗口函数允许在不使用 GROUP BY 的情况下执行类似聚合的操作。

示例:

     SELECT 
         employee_id, 
         department, 
         salary, 
         AVG(salary) OVER (PARTITION BY department) AS avg_department_salary
     FROM employees;

5、性能考虑:

对于大数据集，聚合函数可能会消耗较多资源。可以通过索引和优化查询来提高性能。

示例:

     CREATE INDEX idx_employee_department ON employees(department);

6、结果精度:

特别是在计算平均值和标准差时，要注意结果的精度问题。可以使用更高精度的数据类型来避免误差。

示例:

     SELECT AVG(CAST(salary AS DECIMAL(10, 2))) AS average_salary FROM employees;

六、结合 GROUP BY 使用聚合函数

聚合函数通常与 GROUP BY 子句一起使用，以便对数据进行分组并计算每个组的聚合值。

示例:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department

七、总结

聚合函数是数据库查询中非常强大和有用的工具，可以帮助你快速汇总和分析数据。熟练掌握这些函数及其用法，将大大提高你的数据分析能力和效率。以下是一些关键点总结：

作用: 数据汇总、统计分析、数据分组。

好处: 高效性、简洁性、灵活性、可读性。

注意事项: NULL 值处理、数据类型、子查询、窗口函数、性能考虑、结果精度。