MySQL 数据聚合与分组

介绍

在本实验中，我们将探索 MySQL 中的数据聚合和分组操作，这些是分析和汇总数据库中数据的关键技能。你将学习如何使用聚合函数对多行数据进行计算，基于列值对数据进行分组，并过滤分组后的结果。这些技术对于生成报告、分析趋势以及从数据中提取有意义的见解至关重要。通过动手练习，你将获得这些关键数据库操作的实践经验，并理解如何在 MySQL 中有效地分析数据。

Skills Graph

基本聚合函数

在这一步中，我们将探索 MySQL 中的基本聚合函数。这些函数对多行数据进行计算并返回单个值，是数据分析中不可或缺的工具。

首先，让我们连接到 MySQL 并选择数据库：

sudo mysql -u root

连接成功后：

USE sales_db;

COUNT 函数

让我们从 COUNT 函数开始，它用于计算结果集中的行数：

-- 计算总销售数量
SELECT COUNT(*) as total_sales
FROM sales;

你应该会看到类似以下的输出：

+--------------+
| total_sales  |
+--------------+
| 12           |
+--------------+

COUNT 可以以不同的方式使用：

-- 计算销售的唯一产品数量
SELECT COUNT(DISTINCT product_name) as unique_products
FROM sales;

-- 按类别计算销售数量
SELECT category, COUNT(*) as sales_count
FROM sales
GROUP BY category;

说明：

• COUNT(*) 计算表中的所有行，包括 NULL 值
• COUNT(DISTINCT column) 仅计算指定列中的唯一值
• 与 GROUP BY 一起使用时，COUNT 会分别计算每个组的总数
• as 关键字为结果列创建别名，使输出更易读

SUM 函数

SUM 函数用于计算数值列的总和：

-- 计算总销售数量
SELECT SUM(quantity) as total_items_sold
FROM sales;

-- 计算总收入
SELECT
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;

说明：

• SUM 只能用于数值列
• 像 quantity * unit_price 这样的计算会在聚合之前执行
• ROUND(x, 2) 将数字四舍五入到小数点后两位
• 在收入计算中，乘法会在求和之前进行，以保持精度

AVG 函数

AVG 函数用于计算平均值：

-- 计算平均单价
SELECT
    ROUND(AVG(unit_price), 2) as avg_price
FROM sales;

-- 计算每次销售的平均数量
SELECT
    ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;

说明：

• AVG 会自动忽略计算中的 NULL 值
• ROUND 的第二个参数指定小数位数
• 结果会自动转换为 DECIMAL 类型以保持精度
• AVG 通常用于查找数据中的典型值

MAX 和 MIN 函数

这些函数用于查找最大值和最小值：

-- 查找产品的价格范围
SELECT
    MIN(unit_price) as lowest_price,
    MAX(unit_price) as highest_price
FROM sales;

-- 查找第一次和最后一次销售的日期
SELECT
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM sales;

说明：

• MIN/MAX 适用于数字、字符串和日期
• 对于日期，MIN 查找最早的日期，而 MAX 查找最晚的日期
• 多个聚合函数可以组合在一个 SELECT 语句中
• 与其他聚合函数一样，它们会自动忽略 NULL 值
• 这些函数对于查找数据中的值范围和边界非常有用

使用 GROUP BY 分组数据

在这一步中，我们将学习如何使用 GROUP BY 子句对数据进行分组。分组允许我们基于特定列值对数据的子集执行聚合计算。

基本分组

让我们从简单的分组操作开始：

-- 按类别统计销售数量和总数量
SELECT
    category,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
ORDER BY total_quantity DESC;

此查询显示每个类别的销售数量和总销售数量。你应该会看到类似以下的输出：

+-------------+-------------+----------------+
| category    | sales_count | total_quantity |
+-------------+-------------+----------------+
| Furniture   |           5 |             22 |
| Electronics |           5 |             21 |
| Appliances  |           2 |             10 |
+-------------+-------------+----------------+

说明：

• GROUP BY 将具有相同类别的行合并为一行
• ORDER BY total_quantity DESC 按数量从高到低排序结果
• 每个聚合函数（COUNT、SUM）在每个组内独立运行
• SELECT 中的非聚合列必须出现在 GROUP BY 子句中

多列分组

我们可以通过多列分组来获得更详细的洞察：

-- 按类别和地区进行销售分析
SELECT
    category,
    region,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

说明：

• 按多列分组会为每个唯一组合创建子组
• GROUP BY 中列的顺序会影响数据的分组方式
• 结果首先按类别排序，然后在每个类别内按总收入排序
• 这种方法有助于识别每个类别中表现最佳的地区

基于日期的分组

MySQL 提供了提取日期部分的函数，可用于基于时间的分组：

-- 每日销售摘要
SELECT
    sale_date,
    COUNT(*) as transactions,
    SUM(quantity) as items_sold,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

说明：

• 将同一天发生的所有交易分组
• ORDER BY sale_date 按时间顺序排列结果
• 统计每天的交易数量并计算每日总计
• 适用于识别每日销售模式和趋势
• 可以使用 DATE 函数修改为按月或按年分组

使用 HAVING 过滤分组数据

在这一步中，我们将学习 HAVING 子句，它允许我们在分组后对结果进行过滤。WHERE 在分组前过滤单行数据，而 HAVING 则过滤分组本身。

基本 HAVING 用法

让我们查找总销售数量超过 15 件的类别：

SELECT
    category,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
HAVING total_quantity > 15;

这将仅显示总数量超过 15 件的类别：

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Electronics |             21 |
| Furniture   |             22 |
+-------------+----------------+

说明：

• HAVING 在应用 GROUP BY 后过滤分组
• 你可以在 HAVING 中引用聚合函数的结果
• 过滤条件使用别名 'total_quantity'
• 总数量为 15 件或更少的类别将被排除在结果之外

结合 WHERE 和 HAVING

我们可以同时使用 WHERE 和 HAVING。WHERE 在分组前过滤行，而 HAVING 在分组后过滤：

-- 查找北部地区的高销量类别
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity
FROM sales
WHERE region = 'North'
GROUP BY category
HAVING total_quantity > 5;

说明：

• WHERE 在分组前过滤单行数据（region = 'North'）
• 然后数据按类别分组
• 最后，HAVING 过滤分组结果（total_quantity > 5）
• 顺序很重要：FROM → WHERE → GROUP BY → HAVING → SELECT

复杂的 HAVING 条件

我们可以在 HAVING 中使用多个条件：

-- 查找高销量和高收入的类别
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
HAVING total_quantity > 10 AND total_revenue > 1000
ORDER BY total_revenue DESC;

说明：

• 可以使用 AND/OR 组合多个条件
• 所有聚合计算都可用于 HAVING 条件
• 你可以在 HAVING 中使用算术运算
• ORDER BY 在 HAVING 过滤后应用
• 这种查询对于识别表现最佳的类别非常有用

高级聚合技术

在这最后一步中，我们将结合之前学到的所有内容，探索更高级的聚合技术。我们将创建全面的销售报告，展示这些工具的强大功能。

销售绩效仪表板

让我们创建一个全面的销售分析：

SELECT
    category,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as total_transactions,
    SUM(quantity) as total_quantity,
    ROUND(AVG(quantity), 1) as avg_quantity_per_sale,
    ROUND(MIN(unit_price), 2) as min_price,
    ROUND(MAX(unit_price), 2) as max_price,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

说明：

• 创建完整的类别绩效概览
• 使用 DISTINCT 计算每个类别中的唯一产品数量
• 结合多个聚合函数进行综合分析
• 使用 MIN 和 MAX 显示价格范围
• 计算平均销售数量和总收入
• 结果按收入排序，突出表现最佳的类别

区域绩效分析

让我们按地区分析销售绩效：

SELECT
    region,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as avg_price_per_unit
FROM sales
GROUP BY region
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

说明：

• 按地理区域分组销售数据
• 使用 DISTINCT 计数显示产品多样性
• 计算总收入和相关数量指标
• avg_price_per_unit 通过总收入除以总数量计算
• HAVING 过滤掉收入较低的地区
• 帮助识别表现最佳和最差的地区

每日趋势分析

让我们创建一个每日销售趋势报告：

SELECT
    sale_date,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue,
    ROUND(AVG(quantity * unit_price), 2) as avg_transaction_value
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

说明：

• 跟踪每日销售绩效指标
• 显示每日产品和类别的多样性
• 统计每日交易数量和总数量
• 计算每日收入和平均交易价值
• 按时间顺序排序有助于识别趋势
• 适用于发现模式和季节性影响
• 有助于库存规划和人员配置决策

总结

在本实验中，我们涵盖了 MySQL 中数据聚合和分组的基本内容：

1. 使用基本聚合函数（COUNT、SUM、AVG、MAX、MIN）来汇总数据
2. 使用 GROUP BY 对数据进行分组，以分析模式和趋势
3. 使用 HAVING 子句过滤分组结果
4. 结合多种技术创建全面的数据分析

这些技能是 MySQL 中数据分析和报告的基础。掌握如何有效地聚合和分组数据，可以帮助你从数据库中提取有意义的见解，并创建有价值的业务报告。