SQL中的聚合函数使用总结

本文详细解释了SQL中聚合函数的正确用法及其与WHERE和HAVING子句的区别。通过对比WHERE与HAVING的不同作用,阐述了聚合函数为何不能出现在WHERE子句中,并介绍了聚合函数的常见应用场景。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一般在书写sql的是时候很多时候会误将聚合函数放到where后面作为条件查询,事实证明这样是无法执行的,执行会报【此处不允许使用聚合函数】异常。为什么会报异常呢?其原因很简单:

  • having放在group by 的后面
  • group by 后面只能放非聚合函数的列
  • where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。
  • having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。

那聚合函数在什么情况下使用或者应该处在sql文中的哪个位置呢
聚合函数只能在以下位置作为表达式使用:

  • select 语句的选择列表(子查询或外部查询);
  •  compute 或 compute by 子句;
  • having 子句;

其实在诸多实际运用中,聚合函数更多的是辅助group by 使用,但是只要我们牢记where的作用对象只是行,只是用来过滤数据作为条件使用。

常见的几个聚合函数

  • 求个数:count
  • 求总和:sum
  • 求最大值:max
  • 求最小值:min
  • 求平均值:avg

当然还有其他类型的聚合函数,可能随着对应sql server不同,支持的种类也不一样。

<think>我们正在讨论SQL聚合函数。根据引用[1][2][3],聚合函数用于对一组值执行计算并返回单一的值。常见的聚合函数包括:COUNT(), SUM(), AVG(), MAX(), MIN()等。它们通常与GROUP BY子句一起使用,用于对数据进行分组统计。 引用[1]提到:聚合函数的难点在于精确、准确的使用方法,使用方法前还需要考虑该方法支持的数据类型。比如sum(),此方法仅用于数字列。 引用[2]给出了聚合函数的基本语法:select 列名+聚合函数 from 库名 group by 列名 having +聚合函数; 引用[3]强调了GROUP BY改变了SQL的思维方式:按组处理,而不是逐行处理,因此必须使用聚合函数或GROUP BY的列来选择值。 另外,引用[4]虽然讲的是Flink SQL自定义聚合函数,但其中提到自定义聚合函数至少需要createAccumulator、accumulate、getValue这三个方法,这可以帮助我们理解聚合函数的内部机制(但标准SQL中我们主要关注内置函数)。 下面我将详细介绍SQL聚合函数使用方法。 ## SQL聚合函数详解 ### 一、常用聚合函数列表 1. **COUNT()**: 计算行数(或非NULL值的个数)。 - `COUNT(*)`:统计所有行数(包括NULL)。 - `COUNT(列名)`:统计指定列中非NULL值的个数。 2. **SUM()**: 计算数值列的总和(忽略NULL)。 3. **AVG()**: 计算数值列的平均值(忽略NULL)。 4. **MAX()**: 返回列中的最大值(适用于数值、字符串、日期等类型)。 5. **MIN()**: 返回列中的最小值(适用于数值、字符串、日期等类型)。 6. **GROUP_CONCAT()** (MySQL特有):将组内的字符串连接成一个字符串。 7. **STDDEV()**, **VARIANCE()**:标准差和方差(数值列)。 ### 二、基本使用语法 聚合函数通常与`GROUP BY`子句一起使用,用于分组统计。如果没有`GROUP BY`,则整个表作为一个组。 #### 示例1:整个表作为一组 ```sql -- 计算员工表的总人数 SELECT COUNT(*) AS total_employees FROM employees; -- 计算员工表中工资总和、平均工资、最高工资、最低工资 SELECT SUM(salary) AS total_salary, AVG(salary) AS avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees; ``` #### 示例2:按部门分组统计 ```sql -- 按部门分组,统计每个部门的员工人数、工资总和、平均工资 SELECT department_id, COUNT(*) AS employee_count, SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM employees GROUP BY department_id; ``` ### 三、GROUP BY与HAVING子句 - `GROUP BY`:将数据按指定列的值分组,然后对每组应用聚合函数。 - `HAVING`:用于过滤分组后的结果(类似于WHERE,但用于聚合条件)。 ```sql -- 查询平均工资超过10000的部门 SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 10000; -- HAVING后面跟聚合条件 ``` 注意:`WHERE`子句在分组前过滤行,而`HAVING`在分组后过滤组。 ### 四、注意事项 1. **数据类型匹配**:聚合函数对数据类型有要求。例如,`SUM()`和`AVG()`只能用于数值列,而`MAX()`和`MIN()`可用于数值、字符串、日期等[^1]。 2. **NULL值处理**:聚合函数忽略NULL值(`COUNT(*)`除外)。 3. **非分组列**:在SELECT子句中,除了聚合函数和GROUP BY列,其他列不能直接出现(除非它们在功能上依赖于GROUP BY列)。 - 错误示例(在MySQL的严格模式下会报错): ```sql -- 错误:department_name不在GROUP BY子句中,也不是聚合函数 SELECT department_id, department_name, COUNT(*) FROM employees GROUP BY department_id; ``` - 修正:如果department_name与department_id一一对应,可以加入GROUP BY(或使用函数如ANY_VALUE): ```sql SELECT department_id, ANY_VALUE(department_name), COUNT(*) FROM employees GROUP BY department_id; ``` 或者(在支持功能依赖的数据库如MySQL 5.7+的宽松模式下可能允许,但不推荐依赖此特性)[^3]。 4. **性能优化**:聚合操作可能涉及全表扫描,对大型表应确保在GROUP BY列上建立索引。 ### 五、进阶用法:多列分组 可以按多个列分组,此时分组依据是这些列的组合值。 ```sql -- 按部门和职位分组统计 SELECT department_id, job_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary FROM employees GROUP BY department_id, job_id; ``` ### 六、聚合函数与DISTINCT 聚合函数可以与`DISTINCT`结合使用,以计算不同值的聚合。 ```sql -- 计算不同部门的数量(非NULL的department_id) SELECT COUNT(DISTINCT department_id) FROM employees; -- 计算每个部门中不同职位的数量 SELECT department_id, COUNT(DISTINCT job_id) AS distinct_jobs FROM employees GROUP BY department_id; ``` ## 总结 SQL聚合函数是数据分析的核心工具,通过`GROUP BY`实现分组统计,用`HAVING`对分组结果进行过滤。使用时需注意数据类型、NULL值处理以及非分组列的处理规则[^1][^2][^3]。 §§相关问题§§ 1. 如何在SQL中同时使用多个聚合函数? 2. 聚合函数中COUNT(1)和COUNT(*)有什么区别? 3. 为什么在GROUP BY查询中不能直接使用非分组列? 4. 如何优化包含GROUP BY和聚合函数的大数据量查询? 5. 除了常见的聚合函数SQL还支持哪些高级聚合函数(如窗口函数中的聚合)?</think>### SQL聚合函数详解 SQL聚合函数用于对一组值执行计算并返回单一的值,常用于数据统计和分析。以下是核心聚合函数使用方法: #### 一、常用聚合函数列表 1. **COUNT()** - 统计行数或非NULL值数量 - 示例: ```sql SELECT COUNT(*) AS total_users FROM users; -- 统计所有行数 SELECT COUNT(email) AS valid_emails FROM users; -- 统计非NULL的邮箱数 ``` 2. **SUM()** - 计算数值列的总和(仅适用于数值类型)[^1] - 示例: ```sql SELECT SUM(salary) AS total_payroll FROM employees; ``` 3. **AVG()** - 计算数值列的平均值 - 示例: ```sql SELECT AVG(score) AS average_score FROM exam_results; ``` 4. **MAX()/MIN()** - 返回列的最大值/最小值(支持数值、日期、字符串) - 示例: ```sql SELECT MAX(temperature) AS max_temp, MIN(temperature) AS min_temp FROM weather_data; ``` 5. **GROUP_CONCAT()** (MySQL特有) - 将分组内的值连接成字符串 - 示例: ```sql SELECT department, GROUP_CONCAT(name) AS employees FROM staff GROUP BY department; ``` #### 二、核心使用规则 1. **基本语法结构** ```sql SELECT 列名, 聚合函数(列名) FROM 表名 GROUP BY 列名 HAVING 聚合函数(列名) 条件; ``` - `GROUP BY`:定义分组依据(必选)[^3] - `HAVING`:过滤聚合结果(类似`WHERE`但作用于分组后)[^2] 2. **典型工作流程** 整张表 → 按字段值分组 → 每组做聚合 → 输出每组统计结果[^3] #### 三、使用示例 1. **单字段分组统计** ```sql -- 统计每个部门的平均工资 SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; ``` 2. **多字段分组统计** ```sql -- 统计每个部门+职级的最高工资 SELECT department, job_level, MAX(salary) AS max_salary FROM employees GROUP BY department, job_level; ``` 3. **HAVING过滤分组** ```sql -- 筛选平均工资>10000的部门 SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 10000; -- 注意:不可用列别名 ``` #### 四、注意事项 1. **数据类型匹配** - `SUM()/AVG()`仅适用于数值类型[^1] - `MAX()/MIN()`支持数值、日期、字符串 2. **NULL值处理** 所有聚合函数自动忽略NULL值(`COUNT(*)`除外) 3. **性能优化** - 对`GROUP BY`列建立索引加速分组 - 避免`SELECT *`,仅选择必要列 4. **与普通列混用限制** `SELECT`中的聚合列必须出现在`GROUP BY`中[^3]: ```sql -- 错误示例(name未在GROUP BY中) SELECT department, name, AVG(salary) FROM employees GROUP BY department; ``` #### 五、高级应用 1. **嵌套聚合** ```sql -- 计算各部门最高平均工资 SELECT MAX(avg_salary) FROM ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ) AS dept_avg; ``` 2. **结合DISTINCT** ```sql -- 统计不重复客户数量 SELECT COUNT(DISTINCT customer_id) FROM orders; ``` > 关键点:聚合函数将多行数据"折叠"成单行统计值,必须与`GROUP BY`配合使用(整表聚合时可省略)[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值