MySQL修炼手册4：分组与聚合：GROUP BY与HAVING的应用

写在开头

MySQL数据库的强大功能为我们提供了丰富的数据处理工具，其中GROUP BY与HAVING的应用使得数据的分组与聚合变得更加灵活和高效。在本篇博客中，我们将深入研究GROUP BY与HAVING的基础知识，并通过实际案例，展示它们在数据分析中的强大威力。

创建水果销售表

首先，为了更好地演示GROUP BY与HAVING的应用，我们创建了一个水果销售表fruit_sales，包含字段：id（销售记录ID）、fruit_name（水果名称）、sales_year（销售年份）、sales_quantity（销售量）、sales_revenue（销售额）。下面是一些示例数据：

CREATE TABLE fruit_sales (
    id INT PRIMARY KEY,
    fruit_name VARCHAR(50),
    sales_year INT,
    sales_quantity INT,
    sales_revenue DECIMAL(10, 2)
);

INSERT INTO fruit_sales (id, fruit_name, sales_year, sales_quantity, sales_revenue) VALUES
(1, 'Apple', 2022, 100, 150.00),
(2, 'Orange', 2022, 120, 180.00),
(3, 'Banana', 2022, 80, 90.00),
(4, 'Apple', 2023, 150, 225.00),
(5, 'Orange', 2023, 90, 108.00),
(6, 'Banana', 2023, 120, 144.00),
(7, 'Apple', 2024, 80, 120.00),
(8, 'Orange', 2024, 110, 132.00),
(9, 'Banana', 2024, 100, 120.00);

运行下面语句，查询所有数据。

SELECT
	* 
FROM
	fruit_sales;

查询所有数据：

1 GROUP BY子句基础

1.1 基本的GROUP BY语法

GROUP BY子句是MySQL中用于对结果进行分组的关键。通过以下语法，我们可以按照指定列的值将结果分组：

SELECT fruit_name
FROM fruit_sales
GROUP BY fruit_name;

1.2 在SELECT中使用聚合函数

与GROUP BY结合使用聚合函数，可以对每个分组进行更详细的统计分析：

SELECT
	fruit_name,
	AVG( sales_quantity ) AS avg_quantity,
	SUM( sales_revenue ) AS total_revenue 
FROM
	fruit_sales 
GROUP BY
	fruit_name;

2 常见聚合函数

2.1 COUNT函数：统计行数

COUNT函数用于统计指定列中的行数，适用于对数据集大小进行直观认识。在这里介绍三种常见的写法：
写法1：count(字段)

SELECT COUNT(id) AS total_sales

写法2：count(1)

SELECT COUNT(1) AS total_sales

写法3：count(*)

SELECT COUNT(*) AS total_sales

2.2 SUM函数：求和

通过SUM函数，我们可以计算某一列的总和，比如计算水果的总销售额：

SELECT
	SUM( sales_revenue ) AS total_revenue 
FROM
	fruit_sales;

2.3 AVG函数：求平均值

AVG函数用于计算某一列的平均值，例如计算水果的平均销售数量：

SELECT
	AVG( sales_quantity ) AS avg_quantity 
FROM
	fruit_sales;

2.4 其他常见函数

MAX()： 用于获取某列的最大值。

SELECT MAX(sales_quantity) FROM fruit_sales;

MIN()： 用于获取某列的最小值。

SELECT MIN(sales_quantity) FROM fruit_sales;

GROUP_CONCAT()： 用于将组内的值连接成字符串。

SELECT
	GROUP_CONCAT( fruit_name ) fruits_category
FROM
	fruit_sales 
GROUP BY
	sales_year;

STD() 和 STDDEV()： 分别用于计算总体标准差和样本标准差。

SELECT
	std( sales_quantity ) sales_quantity_std
FROM
	fruit_sales;
	
	SELECT
	stddev( sales_quantity ) sales_quantity_stddev
FROM
	fruit_sales;

VAR_POP() 和 VAR_SAMP()： 分别用于计算总体方差和样本方差。

SELECT
	var_pop( sales_quantity ) sales_quantity_var
FROM
	fruit_sales;
	
SELECT
	var_samp( sales_quantity ) sales_quantity_varsamp
FROM
	fruit_sales;

3 GROUP BY子句的应用

在MySQL中，GROUP BY子句是进行分组操作的关键工具，它允许我们按照指定的列对结果进行分类，从而进行更加细致的数据分析。接下来，我们将深入研究GROUP BY子句的不同应用场景，并列举一些实际的例子。

3.1 对结果进行分组

基本的GROUP BY语法已经在前文中介绍过，这里我们来看一些更具体的例子。

3.1.1 按照年份分组统计水果销售量

SELECT sales_year, SUM(sales_quantity) AS total_quantity
FROM fruit_sales
GROUP BY sales_year;

这个查询会将销售表按照年份分组，计算每年的总销售量。

3.1.2 按照水果种类和年份分组统计销售额

SELECT fruit_name, sales_year, SUM(sales_revenue) AS total_revenue
FROM fruit_sales
GROUP BY fruit_name, sales_year;

这个查询将销售表按照水果种类和年份两个维度进行分组，计算每种水果每年的总销售额。

3.2 稍微复杂一些的聚合

结合GROUP BY和聚合函数，我们可以在分组的基础上对每个分组进行各种统计操作，以下是一些示例。

3.2.1 计算每年平均销售量和总销售额

SELECT sales_year, AVG(sales_quantity) AS avg_quantity, SUM(sales_revenue) AS total_revenue
FROM fruit_sales
GROUP BY sales_year;

这个查询会计算每年的平均销售量和总销售额。

3.2.2 查找每种水果的最大销售量和最小销售额

SELECT fruit_name, MAX(sales_quantity) AS max_quantity, MIN(sales_revenue) AS min_revenue
FROM fruit_sales
GROUP BY fruit_name;

这个查询会找出每种水果的最大销售量和最小销售额。

4 聚合函数的条件运用

在实际的数据分析过程中，经常需要根据特定条件对数据进行聚合分析。聚合函数的条件运用主要通过在聚合函数内部结合使用WHERE子句和HAVING子句来实现，让我们深入探讨各种应用场景。

4.1 示例：统计每个水果在销售年份为2023年的销售量

SELECT fruit_name, SUM(sales_quantity) AS total_quantity
FROM fruit_sales
WHERE sales_year = 2023
GROUP BY fruit_name;

在这个例子中，WHERE子句用于筛选出销售年份为2023年的数据，然后通过SUM函数计算每个水果的总销售量。

4.2 示例：计算销售额超过100的水果的平均销售量

SELECT fruit_name, AVG(sales_quantity) AS avg_quantity
FROM fruit_sales
WHERE sales_revenue > 100
GROUP BY fruit_name;

在这个例子中，WHERE子句用于筛选出销售额超过100的数据，然后通过AVG函数计算每个水果的平均销售量。

5 HAVING子句的应用

在MySQL中，HAVING子句通常用于对GROUP BY的结果进行条件过滤。它在很大程度上类似于WHERE子句，但WHERE用于过滤行，而HAVING用于过滤组。下面我们将详细展开HAVING的应用，并列举一些实际场景中的使用案例。

5.1 在GROUP BY的基础上使用HAVING

当我们通过GROUP BY对数据进行分组后，有时候希望进一步筛选出符合特定条件的组，这时就需要使用HAVING子句。以下是一个简单的例子：

SELECT sales_year, fruit_name, AVG(sales_quantity) AS avg_quantity
FROM fruit_sales
GROUP BY sales_year, fruit_name
HAVING avg_quantity > 100;

在这个例子中，我们首先按年份和水果名称对数据进行了分组，然后使用HAVING筛选出平均销售数量超过100的组。

5.2 HAVING与聚合函数的进一步结合

HAVING经常与聚合函数一起使用，以便更精细地过滤分组。以下是一个例子，我们想找出销售额最高的水果，但只考虑那些销售额总和超过500的水果：

SELECT fruit_name, SUM(sales_revenue) AS total_revenue
FROM fruit_sales
GROUP BY fruit_name
HAVING total_revenue > 500;

这个查询首先按水果名称分组，然后计算每个组的销售额总和，最后通过HAVING过滤出总销售额超过500的水果。

5.3 多条件的HAVING过滤

HAVING也可以使用多个条件进行过滤，从而更灵活地满足复杂的查询需求。例如，我们想找出在2023年和2024年都有销售记录的水果：

SELECT fruit_name, COUNT(DISTINCT sales_year) AS years_with_sales
FROM fruit_sales
GROUP BY fruit_name
HAVING years_with_sales = 2;

这个查询中，我们首先按水果名称分组，然后使用COUNT(DISTINCT sales_year)计算每个组中不同年份的销售记录数量，最后通过HAVING过滤出年份数量为2的水果，即在2023年和2024年都有销售记录的水果。

5.4 HAVING与其他条件的结合

在实际应用中，HAVING通常与其他条件一起使用，以进一步精炼查询结果。例如，我们想找出在2022年以后有销售记录的水果中，销售额总和超过200的水果：

SELECT fruit_name, SUM(sales_revenue) AS total_revenue
FROM fruit_sales
WHERE sales_year >= 2022
GROUP BY fruit_name
HAVING total_revenue > 200;

这个查询中，我们首先使用WHERE条件筛选出销售年份在2022年及以后的数据，然后按水果名称分组，并通过HAVING进一步过滤出销售额总和超过200的水果。

5.5 HAVING与聚合函数的嵌套

在某些情况下，我们可能需要对聚合函数的结果再次进行聚合，这时HAVING与聚合函数的嵌套就派上用场了。例如，我们想找出每个水果销售量的平均值超过所有水果平均销售量的水果：

SELECT fruit_name, AVG(sales_quantity) AS avg_quantity
FROM fruit_sales
GROUP BY fruit_name
HAVING avg_quantity > (SELECT AVG(sales_quantity) FROM fruit_sales);

这个查询中，我们首先按水果名称分组，计算每个组的销售量平均值，然后通过HAVING子句筛选出平均销售量超过所有水果平均销售量的水果。

6 GROUP BY与多表查询的结合使用

在实际的数据库应用中，数据通常存储在多个表中，因此对这些表进行联合查询并进行适当的分组是非常普遍的操作。GROUP BY与多表查询的结合使用能够为我们提供更深入的数据分析和统计能力。

6.1 使用GROUP BY对多表进行分组

在涉及多表查询时，GROUP BY可以帮助我们对结果进行更有意义的分组。考虑以下示例，我们有一个水果销售表fruit_sales和一个水果类别表fruit_categories：

CREATE TABLE fruit_categories (
    id INT PRIMARY KEY,
    fruit_name VARCHAR(50),
    category VARCHAR(50)
);



INSERT INTO fruit_categories (id, fruit_name, category) VALUES
(1, 'Apple', 'hard'),
(2, 'Orange', 'soft'),
(3, 'Banana', 'soft');

现在，我们想要按照水果类别进行销售量的统计，可以使用如下查询：

SELECT
	fc.category,
	SUM( fs.sales_quantity ) AS total_quantity 
FROM
	fruit_sales fs
	JOIN fruit_categories fc ON fs.fruit_name = fc.fruit_name 
GROUP BY
	fc.category;

这个查询将fruit_sales和fruit_categories两个表联合起来，按照水果类别进行分组，然后计算每个类别的总销售量。这对于分析不同类别水果的整体销售表现非常有帮助。

6.2 GROUP BY与JOIN操作的协同应用

在多表查询中，JOIN操作和GROUP BY可以协同使用，为我们提供更为复杂的数据统计和分析。考虑以下场景，我们要找出每个水果类别的平均销售额：

SELECT
	fc.category,
	AVG( fs.sales_revenue ) AS avg_revenue 
FROM
	fruit_sales fs
	JOIN fruit_categories fc ON fs.fruit_name = fc.fruit_name 
GROUP BY
	fc.category;

这个查询首先通过JOIN将fruit_sales和fruit_categories两个表关联，然后按照水果类别进行分组，最后计算每个类别的平均销售额。这样的查询可以为市场策略制定、库存管理等方面提供重要的数据支持。

写在最后

通过本篇MySQL修炼手册，我们深入研究了GROUP BY与HAVING的应用，以及它们在不同场景下的运用技巧。掌握这些知识，将使我们在实际应用中更加游刃有余地处理复杂的数据分析与统计任务。在下一篇手册中，我们将继续拓展MySQL的强大功能，探讨更多高级查询与优化技巧，为数据库操作的深入理解打下坚实基础。