mysql如何进行聚合查询

2025-9-20 23:40

|

14

|

数据库

2639 字

|

11 分钟

答案：聚合查询通过聚合函数和GROUP BY对数据分组汇总，HAVING过滤分组结果，需注意NULL值处理及索引优化以提升性能。

mysql如何进行聚合查询

MySQL聚合查询，简而言之，就是对一组数据进行汇总计算，比如统计数量、求和、平均值、最大值或最小值。它不是简单地返回每一行数据，而是将多行数据“压缩”成一行或几行摘要信息，这在数据分析和报表生成中简直是不可或缺的工具。可以说，没有聚合查询，我们对数据的洞察力会大打折扣。

解决方案

要进行聚合查询，核心在于使用SQL的聚合函数（如

COUNT()

,

SUM()

,

AVG()

,

MIN()

,

MAX()

）配合

GROUP BY

子句。

GROUP BY

的作用是将具有相同值的行分组，然后对每个组应用聚合函数。如果不需要分组，聚合函数会作用于整个结果集。

我们来看一个常见的场景：假设有一个订单表

orders

，里面有

order_id

,

customer_id

,

amount

,

order_date

等字段。

-- 示例表结构 CREATE TABLE orders (     order_id INT PRIMARY KEY AUTO_INCREMENT,     customer_id INT,     amount DECIMAL(10, 2),     order_date DATE );  -- 示例数据 INSERT INTO orders (customer_id, amount, order_date) VALUES (101, 100.50, '2023-01-05'), (102, 250.00, '2023-01-05'), (101, 75.20, '2023-01-06'), (103, 300.00, '2023-01-07'), (102, 120.80, '2023-01-07'), (101, 150.00, '2023-01-08'), (103, 50.00, '2023-01-08');

现在，如果我想知道每个客户的总消费金额和订单数量，我会这么写：

SELECT     customer_id,     SUM(amount) AS total_spent,     COUNT(order_id) AS total_orders FROM     orders GROUP BY     customer_id;

这条SQL语句会根据

customer_id

将订单分组，然后对每个组内的

amount

求和，并统计

order_id

的数量。结果会是每个客户ID对应一行，显示其总消费和总订单数。

如果我只想看总消费超过200的客户，这时

HAVING

子句就派上用场了。

WHERE

子句是在分组前过滤行，而

HAVING

子句是在分组后过滤组。

SELECT     customer_id,     SUM(amount) AS total_spent,     COUNT(order_id) AS total_orders FROM     orders GROUP BY     customer_id HAVING     SUM(amount) > 200;

这展示了聚合查询的基本骨架：

SELECT

聚合函数和分组字段，

FROM

表，

GROUP BY

分组字段，以及可选的

HAVING

进行分组后过滤。

聚合查询中

GROUP BY

和

HAVING

子句的正确姿势是什么？

这真的是初学者，乃至一些经验丰富的开发者也容易混淆的地方。我记得我刚接触SQL的时候，总是搞不清

WHERE

和

HAVING

到底什么时候用。简单来说，

WHERE

是对原始行进行筛选，在数据分组之前执行。它能过滤掉不符合条件的单行数据，这些数据根本就不会参与到后续的分组和聚合计算中。

而

HAVING

则是在数据经过

GROUP BY

分组并聚合之后，对分组结果进行筛选。它通常用于过滤那些聚合函数计算出的值不满足特定条件的组。

举个例子，假设我们想找出在2023年1月7日之后有订单，并且总消费超过100的客户。

如果我写成这样：

SELECT     customer_id,     SUM(amount) AS total_spent FROM     orders WHERE     order_date > '2023-01-07'  -- 筛选2023-01-07之后的订单 GROUP BY     customer_id HAVING     SUM(amount) > 100;         -- 筛选总消费大于100的客户

这里的

WHERE

子句会先排除掉所有在1月7日及之前的订单。然后，剩下的订单会按

customer_id

分组，计算每个客户在1月7日之后的所有订单的总金额。最后，

HAVING

再从这些分组中选出总金额大于100的客户。这种组合使用非常常见，也最能体现两者的区别和协作。

一个常见的错误是尝试在

WHERE

子句中使用聚合函数，比如

WHERE SUM(amount) > 100

。MySQL会报错，因为它在处理

WHERE

时，还没有进行分组和聚合。记住，

WHERE

看的是单行数据，

HAVING

看的是分组后的聚合结果。

如何处理聚合查询中的空值（NULL）以及常见陷阱？

NULL

值在聚合查询中确实是个需要特别留意的点，它不是零，也不是空字符串，而是一种“未知”的状态。不同的聚合函数对

NULL

的处理方式不一样，这直接影响你的计算结果。

大多数聚合函数，比如

SUM()

,

AVG()

,

MIN()

,

MAX()

，在计算时会自动忽略

NULL

值。这意味着，如果一个列中包含

NULL

，这些

NULL

值不会被计入总和、平均值、最大值或最小值。

Copysmith

Copysmith是一款面向企业的 ai 内容创建解决方案

27

查看详情

例如，如果

orders

表中

amount

字段有些是

NULL

：

INSERT INTO orders (customer_id, amount, order_date) VALUES (104, NULL, '2023-01-09'), (104, 80.00, '2023-01-09');

当我们计算

SUM(amount)

时，

NULL

会被跳过，只会计算

80.00

。

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING customer_id = 104; -- 结果是 80.00

然而，

COUNT()

函数的处理方式有所不同：

```
COUNT(*)
```
：会统计所有行，包括那些包含
```
NULL
```
值的行。它统计的是行的数量。
```
COUNT(column_name)
```
：只会统计
```
column_name
```
列中非

NULL

值的行数。

这个区别非常重要。如果你想统计某个字段实际有多少条非空记录，就用

COUNT(column_name)

。如果你想知道一个组里有多少条记录，不管它们字段是不是

NULL

，就用

COUNT(*)

。

常见陷阱：

误解

AVG()

结果： 如果
```
AVG(column_name)
```
计算的列包含
```
NULL
```
，它会忽略
```
NULL
```
值，只计算非
```
NULL
```
值的平均数。这可能不是你期望的“所有记录的平均值”，而是“所有有值的记录的平均值”。如果你想把
```
NULL
```
当作
```
0
```
来计算平均值，你需要用
```
COALESCE(column_name, 0)
```
或
```
IFNULL(column_name, 0)
```
来转换
```
NULL
```
值。
```
-- 将NULL视为0计算平均值 SELECT customer_id, AVG(COALESCE(amount, 0)) FROM orders GROUP BY customer_id;
```
COUNT()

的选择： 总是要明确你是想统计“总行数”还是“某个非空字段的行数”。比如，统计有多少个订单（
```
COUNT(order_id)
```
或
```
COUNT(*)
```
都行，因为
```
order_id
```
是主键不可能为
```
NULL
```
），和统计有多少个订单的
```
amount
```
字段有值（
```
COUNT(amount)
```
），结果可能不同。

聚合查询的性能优化策略有哪些，尤其是在大数据量下？

在大数据量下，聚合查询的性能问题是常态。我个人在处理上亿级别的数据时，经常被慢查询折磨。优化聚合查询，我觉得主要有几个方向：

利用索引： 这是最基本也是最重要的。
```
WHERE
```
子句中使用的列，以及
```
GROUP BY
```
子句中使用的列，都应该考虑建立索引。
- ```
WHERE
```
  条件中的索引可以帮助MySQL快速筛选出需要参与聚合的行，减少处理的数据量。
- ```
GROUP BY
```
  列上的索引可以加速分组过程。MySQL在执行
```
GROUP BY
```
  时，通常需要对数据进行排序（如果不是已经有序的话）才能进行分组。如果
```
GROUP BY
```
  的列上有索引，并且查询优化器能够利用它，可以避免额外的排序操作，或者至少能让排序更快。
- 创建复合索引时，要注意列的顺序。如果
```
GROUP BY a, b
```
  ，那么
```
(a, b)
```
  的复合索引会比
```
(b, a)
```
  更有效。
避免全表扫描，缩小数据集： 在执行聚合之前，尽量用
```
WHERE
```
子句过滤掉尽可能多的不相关数据。数据量越小，聚合的速度就越快。
- 比如，只查询最近一周或一个月的订单，而不是所有历史订单。
- 如果可以，将大的聚合查询拆分成小的、可管理的查询。
使用覆盖索引（Covering Index）： 如果
```
SELECT
```
列表中的所有列（包括聚合函数内部的列和
```
GROUP BY
```
的列）都能从索引中获取，而不需要回表查询实际数据行，那么查询性能会大大提升。
- 例如，对于
```
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;
```
  ，如果有一个
```
(customer_id, amount)
```
  的复合索引，MySQL可以直接从索引中读取
```
customer_id
```
  和
```
amount
```
  进行聚合，而无需访问表数据。
合理利用子查询和临时表： 有时，复杂的聚合逻辑可以通过分解成多个步骤来优化。
- 先用一个子查询或CTE（Common Table Expression）生成一个较小的、预聚合的数据集，然后再对这个数据集进行最终的聚合。
- 例如，如果需要跨多个表进行复杂的聚合，可以先通过
```
JOIN
```
  生成一个中间结果，然后将这个结果存储到临时表，再对临时表进行聚合。虽然增加了IO，但如果原始表很大，这种方式可能更快。
优化

HAVING

子句： 尽量将可以放在
```
WHERE
```
子句中的条件移到
```
WHERE
```
中。
```
WHERE
```
先过滤，减少了
```
GROUP BY
```
的工作量。
```
HAVING
```
是在分组后才过滤，效率相对较低。
硬件和配置优化： 这属于数据库层面的优化，比如增加内存、使用更快的磁盘（SSD）、调整MySQL的缓冲区大小（如
```
innodb_buffer_pool_size
```
）等，这些都能直接影响查询性能。
垂直分区或水平分区（分库分表）： 对于超大数据量，单一表可能无法满足性能需求。将数据按某种规则进行分区（例如按时间或客户ID），可以使得聚合查询只扫描部分分区，从而大大减少数据扫描量。但这属于架构层面的优化，实现起来比较复杂。

在我实际工作中，我发现最有效的往往是组合拳：先确保

WHERE

和

GROUP BY

字段有合适的索引，然后检查是否能通过

WHERE

进一步缩小数据集，最后考虑是否能通过覆盖索引避免回表。这些步骤通常能解决大部分的性能瓶颈。

大家都在看：

mysql如何恢复整个数据库 mysql如何保证数据完整性 mysql有哪些主要特点 mysql如何在ubuntu上通过apt安装 mysql如何使用inner join

mysql 大数据工具区别 sql语句性能瓶颈聚合函数 sql mysql 架构 NULL count select 字符串 table 数据库数据分析性能优化

ai

text=ZqhQzanResources

解决方案

聚合查询中 GROUP BY 和 HAVING 子句的正确姿势是什么？

如何处理聚合查询中的空值（NULL）以及常见陷阱？

聚合查询的性能优化策略有哪些，尤其是在大数据量下？

大家都在看：

推荐文章

聚合查询中

GROUP BY

和

HAVING

子句的正确姿势是什么？