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

登录后复制

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

白月生产企业订单管理系统GBK2.0 Build 080807

请注意以下说明:1、本程序允许任何人免费使用。2、本程序采用PHP+MYSQL架构编写。并且经过ZEND加密，所以运行环境需要有ZEND引擎支持。3、需要售后服务的，请与本作者联系，联系方式见下方。4、本程序还可以与您的网站想整合，可以实现用户在线服务功能，可以让客户管理自己的信息，可以查询自己的订单状况。以及返点信息等相关客户利益的信息。这个功能可提高客户的向心度。安装方法：1、解压本系统，放在

查看详情

例如，如果

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