当前位置：首页 > news >正文

SQL进阶——聚合函数与分组

news 2024/12/3 8:29:43

在数据库中，聚合函数用于对一组数据进行计算，生成一个单一的结果。例如，计算总和、平均值或获取最大值等操作。聚合函数非常有用，尤其是在进行数据分析、报表生成等场景时，它们允许开发者从大量数据中提取有价值的信息。

SQL中常见的聚合函数包括：COUNT()、SUM()、AVG()、MIN()和MAX()。与这些聚合函数一起使用的关键字是GROUP BY和HAVING，它们可以帮助开发者根据指定条件对数据进行分组，并对每个组应用聚合函数。此外，HAVING子句允许在分组后进行过滤，这一点与WHERE子句不同，WHERE用于在分组之前筛选数据，而HAVING则是在分组之后筛选数据。

本章将深入讲解如何使用聚合函数、GROUP BY和HAVING来进行数据的聚合、分组与筛选，并展示如何在实际应用中高效使用这些工具。

1. 聚合函数：COUNT(), SUM(), AVG(), MIN(), MAX()

1.1 COUNT()

COUNT()函数用于计算查询结果中某列的行数。它不仅用于计算非NULL值的个数，还可以用来计算结果集中的总行数。

SQL语法：

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

示例：

计算employees表中员工的总数：

SELECT COUNT(*) 
FROM employees;

说明：

COUNT(*)返回employees表中所有行的数量，即使某些列的值为NULL也会计算。
COUNT(column_name)仅计算非NULL值的行数。

1.2 SUM()

SUM()函数用于计算某列中所有数字的总和。通常用于财务、库存等需要计算总量的场景。

SQL语法：

SELECT SUM(column_name)
FROM table_name
WHERE condition;

示例：

计算sales表中所有销售额的总和：

SELECT SUM(amount)
FROM sales;

说明：

SUM(amount)返回sales表中所有销售额的总和。

1.3 AVG()

AVG()函数用于计算某列的平均值，通常用于统计分析中，提供数据的集中趋势。

SQL语法：

SELECT AVG(column_name)
FROM table_name
WHERE condition;

示例：

计算employees表中所有员工的平均工资：

SELECT AVG(salary)
FROM employees;

说明：

AVG(salary)返回所有员工工资的平均值。

1.4 MIN()

MIN()函数返回某列中的最小值，通常用于查找最低的价格、年龄等。

SQL语法：

SELECT MIN(column_name)
FROM table_name
WHERE condition;

示例：

查找employees表中最低的工资：

SELECT MAX(salary)
FROM employees;

说明：

MIN(salary)返回employees表中最低的工资值。

1.5 MAX()

MAX()函数返回某列中的最大值，通常用于查找最高的价格、年龄等。

SQL语法：

SELECT MAX(column_name)
FROM table_name
WHERE condition;

示例：

查找employees表中最高的工资：

SELECT MAX(salary)
FROM employees;

说明：

MAX(salary)返回employees表中最高的工资值。

2. 使用GROUP BY与HAVING进行分组与过滤

2.1 GROUP BY：数据分组

GROUP BY子句将查询结果中的数据按照某一列或多列的值进行分组。每个分组中包含相同值的行。通常，GROUP BY与聚合函数一起使用，以便对每个组执行聚合计算。

SQL语法：

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

示例：

计算每个部门的员工人数：

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

说明：

GROUP BY department_id将employees表中的数据按照department_id分组。
COUNT(*)计算每个部门中员工的数量。

2.2 使用多个列进行分组

有时需要根据多个列的组合对数据进行分组。可以通过在GROUP BY子句中列出多个列名来实现。

示例：

计算每个部门和职位的员工人数：

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

说明：

GROUP BY department_id, job_title将数据按照department_id和job_title的组合进行分组。

2.3 HAVING：对分组结果进行过滤

HAVING子句与WHERE子句类似，但HAVING是用于对分组后的数据进行过滤，而WHERE是在分组之前对原始数据进行过滤。因此，HAVING常与GROUP BY一起使用，以在聚合计算后进行进一步筛选。

SQL语法：

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING aggregate_function(column2) condition;

示例：

计算每个部门的员工人数，并筛选出员工人数大于10的部门：

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

说明：

HAVING COUNT(*) > 10对每个部门的员工人数进行筛选，仅返回员工人数大于10的部门。

2.4 WHERE与HAVING的区别

WHERE子句在GROUP BY之前过滤数据。
HAVING子句在GROUP BY之后过滤分组结果。

例如，以下查询将只考虑工资大于50000的员工，并计算每个部门的员工人数：

SELECT department_id, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department_id;

而如果我们使用HAVING，则会在分组之后进行筛选：

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

3. 聚合函数与分组的高级用法

3.1 聚合函数与JOIN的结合

在实际开发中，聚合函数与JOIN常常一起使用。例如，计算每个员工所在部门的总销售额，通常需要JOIN员工表和销售表。

示例：

假设有两个表：employees和sales，我们可以计算每个部门的总销售额。

SELECT e.department_id, SUM(s.amount)
FROM employees e
JOIN sales s ON e.id = s.employee_id
GROUP BY e.department_id;

说明：

JOIN将employees和sales表连接在一起，然后根据department_id进行分组，计算每个部门的总销售额。

3.2 使用CASE语句与聚合函数结合

CASE语句可以在聚合函数中使用，用于在计算过程中进行条件判断。例如，计算每个部门的高薪员工人数和低薪员工人数。

示例：

SELECT department_id, AVG(salary) AS avg_salary, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

说明：

CASE WHEN salary > 50000 THEN 1 END在salary大于50000时计为1，COUNT()函数将计算符合条件的行数。

3.3 分组与多层嵌套聚合

在某些场景中，可能需要对多层次的聚合结果进行进一步计算。例如，计算每个部门的平均工资，以及所有部门的总工资。

示例：

SELECT department_id, AVG(salary) AS avg_salary, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

然后，计算所有部门的总工资：

SELECT SUM(total_salary) AS all_departments_salary
FROM (SELECT department_id, SUM(salary) AS total_salaryFROM employeesGROUP BY department_id
) AS department_salaries;

说明：