mysql如何进行聚合查询-mysql教程-PHP中文网

答案：聚合查询通过聚合函数和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

登录后复制

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

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

查看详情

例如，如果

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），可以使得聚合查询只扫描部分分区，从而大大减少数据扫描量。但这属于架构层面的优化，实现起来比较复杂。