第7课: 聚合函数与分组

COUNT、SUM、AVG、MAX、MIN、GROUP BY、HAVING

聚合函数原理与基础

聚合函数的工作原理：

聚合函数对一组数据进行计算并返回单个结果值。其工作过程包括：

数据分组： 根据GROUP BY子句将数据划分为不同的组（如果没有GROUP BY，则整个结果集为一组）
组内计算： 对每个组内的数据应用聚合函数进行计算
结果合并： 将每个组的计算结果合并为最终的结果集

注意： 聚合函数会忽略NULL值（COUNT(*)除外），这是聚合函数的一个重要特性。

聚合函数工作流程图

常用聚合函数

聚合函数对一组值执行计算并返回单个值，常用于统计分析。

-- 示例1: COUNT 计数
SELECT COUNT(*) AS 总用户数 FROM users;  -- 统计所有行，包括NULL
SELECT COUNT(DISTINCT category) AS 分类数量 FROM products;  -- 去重计数
SELECT COUNT(phone) AS 填写手机号的用户数 FROM users;  -- NULL不计数
SELECT COUNT(1) AS 总记录数 FROM orders;  -- 与COUNT(*)效果相同

-- 示例2: SUM 求和
SELECT SUM(total_amount) AS 总销售额 FROM orders;
SELECT SUM(quantity) AS 总销量 FROM order_items;
SELECT SUM(price * quantity) AS 总价值 FROM order_items;

-- 示例3: AVG 平均值
SELECT AVG(price) AS 平均价格 FROM products;
SELECT AVG(age) AS 平均年龄 FROM users WHERE age IS NOT NULL;
SELECT SUM(total_amount) / COUNT(*) AS 平均订单金额 FROM orders;  -- 与AVG效果相同

-- 示例4: MAX 和 MIN 最大最小值
SELECT MAX(price) AS 最高价, MIN(price) AS 最低价 FROM products;
SELECT MAX(order_date) AS 最近订单时间, MIN(order_date) AS 最早订单时间 FROM orders;
SELECT MAX(LENGTH(username)) AS 最长用户名长度 FROM users;

其他聚合函数

-- 示例5: GROUP_CONCAT 字符串聚合
SELECT category, GROUP_CONCAT(product_name SEPARATOR ', ') AS 商品列表
FROM products
GROUP BY category;

-- 示例6: JSON_ARRAYAGG JSON数组聚合 (MySQL 5.7+)
SELECT category, JSON_ARRAYAGG(product_name) AS 商品数组
FROM products
GROUP BY category;

-- 示例7: STDDEV 和 VARIANCE 标准差和方差
SELECT STDDEV(price) AS 价格标准差, VARIANCE(price) AS 价格方差
FROM products;

GROUP BY 分组查询

GROUP BY 将结果集按一个或多个列进行分组，常与聚合函数配合使用。分组查询是数据分析的核心功能之一。

GROUP BY 工作原理：

根据指定的分组字段对结果集进行排序
将具有相同分组字段值的行归为一组
对每个组应用聚合函数计算结果
返回每个组的聚合结果

-- 示例5: 按分类统计商品数量
SELECT category, COUNT(*) AS 商品数量
FROM products
GROUP BY category;

-- 示例6: 按用户统计订单金额
SELECT
    user_id,
    COUNT(*) AS 订单数量,
    SUM(total_amount) AS 总消费金额,
    AVG(total_amount) AS 平均订单金额,
    MAX(order_date) AS 最近订单时间
FROM orders
GROUP BY user_id
ORDER BY 总消费金额 DESC;

-- 示例7: 多字段分组
SELECT
    category,
    YEAR(created_at) AS 年份,
    COUNT(*) AS 商品数量,
    AVG(price) AS 平均价格
FROM products
GROUP BY category, YEAR(created_at)
ORDER BY category, 年份;

-- 示例8: 使用表达式分组
SELECT
    CASE
        WHEN price < 100 THEN '低价商品'
        WHEN price < 500 THEN '中价商品'
        ELSE '高价商品'
    END AS 价格区间,
    COUNT(*) AS 商品数量,
    AVG(price) AS 平均价格
FROM products
GROUP BY 价格区间;

HAVING 过滤分组

HAVING 用于过滤分组后的结果，WHERE 过滤分组前的数据。HAVING 可以使用聚合函数，而 WHERE 不能。

WHERE vs HAVING：

特性	WHERE	HAVING
执行时机	分组前	分组后
作用对象	行	组
使用聚合函数	不可以	可以
性能	通常更好（减少分组数据量）	通常较差（分组后过滤）

-- 示例9: 查询订单数量大于5的用户
SELECT
    user_id,
    COUNT(*) AS 订单数量,
    SUM(total_amount) AS 总金额
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- 示例10: WHERE 和 HAVING 组合使用
SELECT
    category,
    AVG(price) AS 平均价格,
    COUNT(*) AS 商品数量
FROM products
WHERE stock > 0  -- 分组前过滤：只统计有库存的商品
GROUP BY category
HAVING AVG(price) > 100  -- 分组后过滤：平均价格大于100
ORDER BY 平均价格 DESC;

-- 示例11: 使用别名在HAVING中
SELECT
    category AS 分类,
    COUNT(*) AS 商品数量
FROM products
GROUP BY 分类
HAVING 商品数量 > 10;  -- MySQL支持在HAVING中使用列别名

高级聚合查询

WITH ROLLUP 汇总统计

WITH ROLLUP 用于在分组查询的基础上添加汇总行，方便进行总计统计。

-- 示例12: WITH ROLLUP 汇总统计
SELECT
    category,
    COUNT(*) AS 商品数量,
    SUM(price) AS 总价值
FROM products
GROUP BY category WITH ROLLUP;

-- 示例13: 多字段分组的 ROLLUP
SELECT
    YEAR(order_date) AS 年份,
    MONTH(order_date) AS 月份,
    COUNT(*) AS 订单数量,
    SUM(total_amount) AS 总销售额
FROM orders
GROUP BY 年份, 月份 WITH ROLLUP;

字符串聚合函数

MySQL提供了多种字符串聚合函数，用于将分组内的字符串值合并为单个字符串。

-- 示例14: GROUP_CONCAT 字符串聚合
SELECT
    category,
    GROUP_CONCAT(product_name SEPARATOR ', ') AS 商品列表,
    GROUP_CONCAT(DISTINCT price ORDER BY price DESC SEPARATOR ', ') AS 价格列表
FROM products
GROUP BY category;

-- 示例15: JSON_ARRAYAGG JSON数组聚合
SELECT
    category,
    JSON_ARRAYAGG(product_name) AS 商品数组,
    JSON_ARRAYAGG(JSON_OBJECT('name', product_name, 'price', price)) AS 商品信息数组
FROM products
GROUP BY category;

窗口函数与聚合

窗口函数可以在聚合的同时保留原始数据，提供更灵活的分析能力。

-- 示例16: 使用窗口函数计算累计总和
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS 累计销售额,
    AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 移动平均
FROM orders
ORDER BY order_date;

-- 示例17: 按用户计算订单排名
SELECT
    user_id,
    order_id,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total_amount DESC) AS 订单排名,
    SUM(total_amount) OVER (PARTITION BY user_id) AS 用户总消费
FROM orders;

聚合查询最佳实践：

WHERE vs HAVING：WHERE 在分组前过滤，HAVING 在分组后过滤，优先使用 WHERE 提高性能
COUNT(*)：统计所有行，包括 NULL 值，性能通常最好
COUNT(column)：统计非 NULL 值的行数，适用于需要排除 NULL 的场景
DISTINCT：与聚合函数配合使用可去重统计，但会影响性能
索引优化：GROUP BY 字段应建立索引，避免临时表和文件排序
避免 SELECT *：分组查询中只选择分组字段和聚合结果，减少数据传输
合理使用 ROLLUP：对于需要汇总统计的场景，ROLLUP 比多次查询更高效
注意 GROUP_CONCAT 长度限制：默认最大长度为1024，可以通过 group_concat_max_len 参数调整

聚合查询性能优化

性能优化策略：

索引优化：
- 在 GROUP BY 字段上创建索引
- 复合索引的顺序应与 GROUP BY 字段顺序一致
- 考虑使用覆盖索引减少回表操作
数据过滤：
- 使用 WHERE 子句在分组前过滤数据
- 避免在 GROUP BY 中使用复杂表达式
- 考虑使用子查询预先过滤数据
查询计划分析：
- 使用 EXPLAIN 分析聚合查询的执行计划
- 关注是否使用了索引，是否产生了临时表
- 优化 GROUP BY 的排序操作
配置优化：
- 调整 tmp_table_size 和 max_heap_table_size 提高临时表性能
- 对于大数据量聚合，考虑使用汇总表或物化视图

实战场景：销售数据分析

-- 每月销售统计
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS 月份,
    COUNT(*) AS 订单数量,
    SUM(total_amount) AS 总销售额,
    AVG(total_amount) AS 平均订单金额,
    MAX(total_amount) AS 最大订单金额
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 月份;

-- 商品销售排行榜
SELECT
    p.product_name,
    COUNT(oi.order_item_id) AS 销售次数,
    SUM(oi.quantity) AS 总销量,
    SUM(oi.quantity * oi.price) AS 总销售额
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
HAVING 总销量 > 0
ORDER BY 总销售额 DESC
LIMIT 10;

-- 用户消费等级分析
SELECT
    CASE
        WHEN 总消费 >= 10000 THEN 'VIP'
        WHEN 总消费 >= 5000 THEN '高级会员'
        WHEN 总消费 >= 1000 THEN '普通会员'
        ELSE '新用户'
    END AS 用户等级,
    COUNT(*) AS 用户数量
FROM (
    SELECT user_id, SUM(total_amount) AS 总消费
    FROM orders
    GROUP BY user_id
) AS user_stats
GROUP BY 用户等级;

练习题：

统计每个分类的商品数量和平均价格
查询订单总金额大于1000的用户ID和订单总额
统计每个月的新注册用户数量
查询购买次数超过3次的商品及其销量
统计每个用户的订单数量，只显示订单数大于2的用户
查询每个分类中价格最高和最低的商品
统计2024年每个季度的销售额
查询平均订单金额超过500的用户，显示用户ID、订单数量和平均金额

上一课下一课