分组与聚合
当我们处理数据库中的数据时，经常需要对大量细节数据进行汇总分析。比如电商平台需要了解每个商品类别的总销量，或者教育机构想知道每个课程的平均评分。这时候，我们就需要用到SQL中的分组和聚合功能。
其实我们在前面的课程中已经多次使用过分组和聚合功能，比如我们使用 GROUP BY 子句来对数据进行分组，使用聚合函数来对数据进行统计计算。
数据分组就像把杂乱无章的数据按照某种规律整理成不同的堆叠，然后对每一堆数据进行统计计算。这个过程能帮助我们从大量原始数据中提取出有价值的商业洞察。
分组的基本概念
假设我们有一个在线学习平台的课程表，记录了每门课程的创建者信息。如果平台管理员想了解每位老师创建了多少门课程，直接查看原始数据会非常低效。
|SELECT teacher_id FROM courses;
这样的查询会返回所有课程记录，如果有数千门课程，我们很难快速统计每位老师的课程数量。这时候就需要使用分组功能。
通过添加 GROUP BY 子句，我们可以让数据库帮我们自动整理数据：
|SELECT teacher_id FROM courses GROUP BY teacher_id;
这个查询会返回每个不重复的教师ID，相当于自动去重并分组。但仅仅分组还不够，我们通常需要知道每个分组里有多少条记录，这个时候就需要使用聚合函数。
聚合函数
聚合函数（Aggregate Functions）是数据库中用于对一组数据进行统计和汇总计算的专用函数。它们通常与 GROUP BY 子句结合使用，对分组后的每一组数据执行如计数、求和、平均值、最大值、最小值等数学运算。
通过聚合函数，我们可以高效地从大量原始数据中提取出有意义的统计指标，支持业务分析和决策，例如下面是一个简单的COUNT的示例：
|SELECT teacher_id, COUNT(*) as course_count FROM courses GROUP BY teacher_id;
通过以上的查询，我们就可以知道每位老师有多少门课程。
聚合函数必须与GROUP BY子句配合使用，否则会对整个表的数据进行计算
五大核心聚合函数
COUNT() - 计数函数
COUNT() 是最常用的聚合函数之一。它的作用是统计每个分组中包含的记录条数。比如，如果我们按照 teacher_id 对课程表分组，COUNT() 就会返回每位老师各自创建了多少门课程。 COUNT() 可以统计所有行（COUNT()），也可以只统计某一列中非空的行（如 COUNT(rating) 只统计有评分的课程数量）。这种统计方式非常适合用来快速了解每个分组下数据的分布和数量。
|-- 统计每位老师创建的课程数量 SELECT teacher_id, COUNT(*) as total_courses FROM courses GROUP BY teacher_id;
SUM() - 求和函数
SUM() 用于对某个数值型字段进行求和运算。它会将每个分组（或整个表）中指定字段的所有数值相加，得到总和。 比如，我们可以用 SUM() 统计每位老师所有课程的总学时（如 duration_hours 字段），也可以用来计算总销售额（如 sales 字段）。 举例来说，如果一位老师有三门课程，学时分别为 10、20、30，则 SUM(duration_hours) 的结果就是 60。SUM() 常用于财务统计、工作量统计等需要累计数值的场景。
|-- 计算每位老师所有课程的总时长 SELECT teacher_id, SUM(duration_hours) as total_hours FROM courses GROUP BY teacher_id;
AVG() - 平均值函数
AVG() 函数用于计算某个数值字段的平均值。它会将每个分组（或整个表）中该字段的所有数值相加，然后除以记录的数量，得出平均数。 比如，我们可以用 AVG(rating) 计算每位老师课程的平均评分，也可以用 AVG(price) 计算每个类别下课程的平均价格。 平均值常用于分析整体水平、评估绩效或发现异常数据。例如，如果某位老师的平均评分明显高于其他老师，就可能说明其课程质量较高。
|-- 计算每位老师课程的平均评分 SELECT teacher_id, AVG(rating) as avg_rating FROM courses GROUP BY teacher_id;
MAX() 和 MIN() - 最值函数
MAX() 和 MIN() 函数分别用于找出每个分组中某个字段的最大值和最小值。比如，如果我们按照 teacher_id 对课程表分组，MAX(price) 就会返回每位老师所开设课程中的最高价格，MIN(price) 则返回最低价格。 这样可以帮助我们了解每位老师课程的价格区间，或者在其他场景下找出每组数据中的极值（如最高分、最低分、最大销量等）。在实际查询中，通常会和 GROUP BY 子句一起使用，实现对每个分组的最大/最小值统计。
|-- 找出每位老师课程的最高和最低价格 SELECT teacher_id, MAX(price) as highest_price, MIN(price) as lowest_price FROM courses GROUP BY teacher_id;
综合使用聚合函数
在实际的数据分析中，我们经常需要同时获取多个统计指标，比如每个分组下的数量、平均值、总和、最大值、最小值等。此时，可以在同一个查询语句中同时使用多个不同的聚合函数。这样不仅能一次性得到更丰富的分析结果，还能减少多次查询带来的性能开销。 例如，下面的查询会同时统计每个课程类别下的课程数量、平均价格、总报名人数和最高评分等信息，帮助我们从多个维度全面了解数据的分布和特征：
|SELECT category, COUNT(*) as course_count, AVG(price) as avg_price, SUM(enrollment) as total_students, MAX(rating) as best_rating FROM courses GROUP BY category;
隐式分组与显式分组
当我们在SELECT子句中只使用聚合函数（比如COUNT、SUM、AVG等），而没有包含任何普通字段时，数据库会把整张表当作一个整体来进行统计，这种情况就叫做“隐式分组”。 此时，查询的结果只会返回一行，表示对全表数据的汇总结果。例如，如果我们想统计所有课程的总数、平均价格和总报名人数，就可以只写聚合函数，不需要加GROUP BY，数据库会自动把所有数据当成一个分组来处理。
|-- 隐式分组：计算所有课程的统计信息 SELECT COUNT(*) as total_courses, AVG(price) as avg_price, SUM(enrollment) as total_enrollment FROM courses;
但如果我们既想要聚合函数的结果，又想要普通字段，就必须使用显式的 GROUP BY 子句：
|-- 显式分组：按类别分组计算统计信息 SELECT category, COUNT(*) as course_count, AVG(price) as avg_price FROM courses GROUP BY category;
如果SELECT子句中包含非聚合函数的字段，这些字段必须出现在GROUP BY子句中，否则查询会报错
统计唯一值
在实际的数据分析中，除了统计总数之外，我们经常还需要统计某个字段中不重复（唯一）的值的数量。这在分析去重后的数据分布时非常有用。 例如，在一个课程表中，假设我们想知道每个课程类别下有多少位不同的老师在授课，而不是所有课程的总数。也就是说，我们希望统计每个类别中“唯一教师ID”的数量，这样可以了解每个类别的师资覆盖面有多广。
|-- 统计每个类别中不同老师的数量 SELECT category, COUNT(DISTINCT teacher_id) as unique_teachers FROM courses GROUP BY category;
这里的 DISTINCT 关键字确保我们只统计不重复的教师ID，即使同一位老师在同一类别下有多门课程，也只会被计算一次。
在聚合中使用表达式
聚合函数不仅可以直接用于某个字段本身，还可以用于更复杂的计算表达式。也就是说，我们可以在聚合函数内部对字段进行运算、组合或处理，然后再对结果进行统计。例如，可以对两个字段相除、相加，或者用条件表达式生成新的值，再用聚合函数（如AVG、SUM等）对这些计算结果进行汇总。
假设我们想要统计每位老师的“平均学时密度”，可以用 AVG(duration_hours / duration_days)，即先计算每门课程的学时密度（学时/天），再对每位老师所有课程的密度取平均值：
|-- 计算每位老师课程的平均学时密度（学时/天） SELECT teacher_id, AVG(duration_hours / duration_days) as avg_intensity FROM courses GROUP BY teacher_id;
NULL值的处理机制
在进行聚合计算时，NULL值的处理方式与普通数值不同。大多数聚合函数（如SUM、AVG、COUNT(列名)等）在计算时会自动忽略值为NULL的记录，只对非NULL的值进行统计。例如，SUM只会累加非NULL的数值，AVG只会计算非NULL值的平均数。如果某一行的字段为NULL，这一行在聚合时会被跳过，不参与计算。
需要特别注意的是，COUNT()与COUNT(列名)的行为不同：COUNT()会统计所有行的数量，包括NULL值；而COUNT(列名)只统计该列非NULL的行数。
下面我们通过一个具体的实验来演示NULL值在聚合函数中的处理方式：
|-- 创建测试数据 CREATE TABLE test_scores ( student_id INT, score INT ); INSERT INTO test_scores VALUES (1, 85), (2, 92), (3, 78), (4, NULL);
现在对这些数据进行聚合：
|SELECT COUNT(*) as total_rows, COUNT(score) as non_null_scores, AVG(score) as avg_score, SUM(score) as total_score FROM test_scores;
结果会显示：
|+------------+-----------------+-----------+-------------+ | total_rows | non_null_scores | avg_score | total_score | +------------+-----------------+-----------+-------------+ | 4 | 3 | 85.0000 | 255 | +------------+-----------------+-----------+-------------+
除了COUNT(*)会统计所有行之外，其他聚合函数都会自动忽略NULL值进行计算
多列分组
在实际的业务分析中，往往需要同时考虑多个维度对数据进行分组统计，这样可以获得更细致、更有针对性的分析结果。例如，在一个课程平台中，我们不仅关心每个课程类别（如编程、设计、语言等）的整体情况，还可能希望进一步细分，了解在不同难度级别（如初级、中级、高级）下，各类别的课程分布和表现。 这种“多维分组”可以帮助我们发现不同类别和难度组合下的数据特征，比如哪些类别的高级课程数量较多，或者某一类别在初级难度下最受欢迎。实现这种多维度的交叉分析，只需要在SQL的GROUP BY子句中同时列出多个字段即可，数据库会自动按照每种字段组合进行分组统计。
|SELECT category, difficulty_level, COUNT(*) as course_count, AVG(price) as avg_price FROM courses GROUP BY category, difficulty_level;
基于表达式的分组
除了直接使用表中的原始字段进行分组，我们还可以利用计算表达式对数据进行分组。所谓“基于表达式的分组”，就是在GROUP BY子句中使用某些函数或运算，对字段进行加工处理后再分组。这样可以实现更灵活的数据聚合需求。
举个例子，假设我们的课程表中有一个 created_date 字段，记录了每门课程的创建日期。如果我们想要统计每一年创建的课程数量，就可以通过对 created_date 字段使用 YEAR() 函数，将日期转换成年份，然后按年份分组。这样，数据库会自动把同一年份的数据归为一组，方便我们进行年度统计分析。
这种基于表达式的分组方式，特别适合需要按照时间（如年、月、季度）、区间、类别映射等自定义规则进行分组的场景。
|SELECT YEAR(created_date) as creation_year, COUNT(*) as courses_created FROM courses GROUP BY YEAR(created_date) ORDER BY creation_year;
汇总统计（ROLLUP）
在实际的数据分析中，我们经常不仅仅关心每个分组的详细统计结果，还希望能够看到各个分组的汇总（比如每个类别的小计，甚至所有数据的总计）。如果手动分别查询每一级的汇总会非常繁琐。为此，MySQL 提供了 WITH ROLLUP 选项，可以在分组统计的基础上，自动为我们生成各级别的汇总行。
具体来说，WITH ROLLUP 会在原有的分组结果下方，依次增加每个分组字段的“小计”以及所有数据的“总计”行。例如，如果我们按照“课程类别”和“难度等级”两个字段分组，使用 WITH ROLLUP 后，查询结果会自动包含：
- 每个“类别+难度”组合的详细统计
- 每个类别下所有难度的合计（类别小计）
- 所有类别和难度的总计（全表汇总）
这样，我们可以以一条 SQL 语句同时获得详细分组和各级汇总。
|SELECT category, difficulty_level, COUNT(*) as course_count FROM courses GROUP BY category, difficulty_level WITH ROLLUP;
分组过滤：HAVING子句
在SQL查询中，若需对分组（GROUP BY）后的聚合结果进行筛选，必须使用HAVING子句，而不能用WHERE子句。原因在于，WHERE子句是在分组和聚合计算之前对原始数据进行行级过滤，而HAVING子句则是在分组和聚合之后，对每个分组的统计结果进行条件判断和过滤。(回想我们之前学过的SQL执行顺序)
因此，凡是涉及聚合函数（如COUNT、SUM、AVG等）的筛选条件，都应放在HAVING子句中实现。
|SELECT category, COUNT(*) as course_count, AVG(price) as avg_price FROM courses WHERE status = 'active' -- 分组前过滤：只统计活跃课程 GROUP BY category HAVING COUNT(*) >= 5; -- 分组后过滤：只显示课程数≥5的类别
聚合函数只能在SELECT子句和HAVING子句中使用，不能在WHERE子句中使用
电商平台分析实战演练
让我们通过一个完整的电商平台案例来综合运用这些知识。我们将创建一个完整的销售数据分析系统，从建表到数据插入，再到各种分析查询。
第一部分：创建数据库表
首先，我们需要创建电商平台的核心数据表。为了进行完整的销售分析，我们需要创建以下几个表：
|-- 创建电商平台的核心数据表 -- 1. 商品表：存储所有商品的基本信息 CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, -- 商品ID，主键自增 product_name VARCHAR(200) NOT NULL, -- 商品名称 category VARCHAR(50) NOT NULL, -- 商品类别（如：电子产品、服装、家居等）
巩固练习
现在让我们通过一些练习题来巩固SQL分组与聚合的知识。我们将继续使用上面的电商平台数据库来进行练习。
练习1：基础聚合查询
统计每个商品类别的基本信息：查询每个商品类别下的商品总数、平均价格和最高价格
- 只显示商品数量大于等于3的类别
- 按商品数量降序排列
|SELECT category, COUNT(*) as product_count, ROUND(AVG(price), 2) as avg_price, MAX(price) as max_price FROM products WHERE status = 'active' GROUP BY category HAVING COUNT(*) >= 3 ORDER BY product_count DESC;
练习2：多表联查与聚合
分析卖家销售业绩：查询每个卖家的总销售额、销售数量和平均订单金额
- 包含卖家名称、类型和注册年份
- 只显示销售额超过50000元的卖家
- 按销售额降序排列
|SELECT s.seller_name, s.seller_type, YEAR(s.registration_date) as registration_year, SUM(so.total_amount) as total_sales, SUM(so.quantity) as total_quantity, ROUND(AVG(so.total_amount),
练习3：时间序列分析
分析2023年每月销售趋势：统计每月的订单数量、销售额和平均订单金额
- 计算环比增长率（销售额相比上月的增长百分比）
- 显示月份格式为"2023-MM"
|SELECT DATE_FORMAT(order_date, '%Y-%m') as month, COUNT(*) as order_count, SUM(total_amount) as monthly_sales, ROUND(AVG(total_amount), 2) as avg_order_amount, ROUND( (SUM(total_amount) - LAG(SUM(total_amount)) OVER (ORDER BY DATE_FORMAT
练习4：用户评价与销售关联分析
分析商品评价对销售的影响：统计每个商品的平均评分、评价数量和总销售额
|SELECT p.product_name, ROUND(AVG(pr.rating), 1) as avg_rating, COUNT(pr.review_id) as review_count, SUM(so.total_amount) as total_sales, ROUND(SUM(so.total_amount) /