SQL聚合函数与分组统计：数据分析核心技能

前言

上两篇讲了SQL查询基础和数据操作，这篇讲SQL数据分析最核心的一块：聚合函数与分组统计。

说实话，在实际工作中，90%的数据分析需求都离不开GROUP BY。

"各品类销售额是多少？""每个用户下了几单？""月度GMV走势怎么样？"——这些问题全靠聚合函数+分组来实现。

很多同学学了SELECT和WHERE就以为自己会SQL了，结果一到面试或实际工作中被问"统计各门店Top3商品"，直接卡壳。

建议：先看完前两篇（SQL入门+增删改查），再看这篇，效果翻倍。

〇、建表与数据准备

沿用电商订单表，新增一些数据让聚合统计更有意义。

-- ============================================
-- 电商订单表 orders（扩展数据版）
-- 公众号：船长Talk
-- ============================================

CREATE TABLE IF NOT EXISTS orders (
    order_id     INT PRIMARY KEY COMMENT '订单ID',
    customer     VARCHAR(50) COMMENT '客户姓名',
    product      VARCHAR(100) COMMENT '商品名称',
    category     VARCHAR(50) COMMENT '商品分类',
    price        DECIMAL(10,2) COMMENT '单价',
    quantity     INT COMMENT '购买数量',
    total_amount DECIMAL(10,2) COMMENT '订单总金额',
    order_date   DATE COMMENT '下单日期',
    status       VARCHAR(20) COMMENT '订单状态'
);

-- 插入测试数据（20条，覆盖多分类、多客户、多日期）
INSERT INTO orders VALUES
(1,  '张三', 'iPhone 15',     '手机',   7999.00, 1,  7999.00, '2026-04-01', '已完成'),
(2,  '李四', 'MacBook Pro',    '电脑',  14999.00, 1, 14999.00, '2026-04-01', '已完成'),
(3,  '张三', 'AirPods Pro',    '配件',    999.00, 2,  1998.00, '2026-04-02', '已完成'),
(4,  '王五', 'iPhone 15',      '手机',   7999.00, 1,  7999.00, '2026-04-02', '已取消'),
(5,  '赵六', 'iPad Air',       '平板',   4799.00, 2,  9598.00, '2026-04-03', '已完成'),
(6,  '李四', 'Apple Watch',    '配件',   2999.00, 1,  2999.00, '2026-04-03', '已完成'),
(7,  '张三', 'MacBook Pro',    '电脑',  14999.00, 1, 14999.00, '2026-04-04', '已完成'),
(8,  '王五', 'Magic Keyboard', '配件',   1999.00, 1,  1999.00, '2026-04-05', '已退款'),
(9,  '赵六', 'iPhone 15',      '手机',   7999.00, 1,  7999.00, '2026-04-05', '已完成'),
(10, '张三', 'HomePod mini',   '配件',    749.00, 2,  1498.00, '2026-04-06', '已完成'),
(11, '钱七', 'iPhone 15',      '手机',   7999.00, 2, 15998.00, '2026-04-06', '已完成'),
(12, '李四', 'iPad Air',       '平板',   4799.00, 1,  4799.00, '2026-04-07', '已完成'),
(13, '孙八', 'AirPods Pro',    '配件',    999.00, 1,   999.00, '2026-04-07', '待发货'),
(14, '王五', 'MacBook Air',    '电脑',   8999.00, 1,  8999.00, '2026-04-08', '已完成'),
(15, '张三', 'Apple Pencil',   '配件',    999.00, 1,   999.00, '2026-04-08', '已完成'),
(16, '赵六', 'Magic Keyboard', '配件',   1999.00, 1,  1999.00, '2026-04-09', '已发货'),
(17, '钱七', 'MacBook Pro',    '电脑',  14999.00, 1, 14999.00, '2026-04-09', '已完成'),
(18, '李四', 'HomePod mini',   '配件',    749.00, 3,  2247.00, '2026-04-10', '待发货'),
(19, '张三', 'iPad Air',       '平板',   4799.00, 1,  4799.00, '2026-04-10', '已完成'),
(20, '孙八', 'iPhone 15',      '手机',   7999.00, 1,  7999.00, '2026-04-10', '已完成');

20条数据，覆盖5个分类（手机、电脑、配件、平板）、6个客户、10天日期。后面所有例子都基于这张表。

一、5大聚合函数

聚合函数就是把多行数据"压缩"成一个值的函数。

SQL内置了5个核心聚合函数，搞懂这5个，日常分析基本够用。

1.1 COUNT —— 计数

-- ============================================
-- COUNT 用法
-- 公众号：船长Talk
-- ============================================

-- 总订单数（所有行）
SELECT COUNT(*) AS '总订单数' FROM orders;
-- 结果：20

-- 已完成订单数（加条件）
SELECT COUNT(*) AS '已完成订单数' 
FROM orders 
WHERE status = '已完成';
-- 结果：14

-- 有效订单数（排除NULL，排除已取消/已退款）
SELECT COUNT(*) AS '有效订单数' 
FROM orders 
WHERE status NOT IN ('已取消', '已退款');
-- 结果：17

-- 🚨 COUNT(*) vs COUNT(列名) 的区别
-- COUNT(*)：统计所有行，包括NULL
-- COUNT(列名)：统计该列非NULL的值
SELECT 
    COUNT(*) AS '总行数',
    COUNT(status) AS '状态非空行数'
FROM orders;
-- 如果status列有NULL，两者结果不同

**面试坑点：**COUNT()和COUNT(列名)不一定相等。COUNT()统计所有行，COUNT(列名)跳过NULL值。面试官爱问这个。

1.2 SUM —— 求和

-- ============================================
-- SUM 用法
-- 公众号：船长Talk
-- ============================================

-- 总销售额（所有订单金额加起来）
SELECT SUM(total_amount) AS '总销售额' FROM orders;
-- 结果：139574.00

-- 已完成订单的总销售额
SELECT SUM(total_amount) AS '已完成销售额' 
FROM orders 
WHERE status = '已完成';
-- 结果：110777.00

-- 各客户消费总额
SELECT 
    customer AS '客户',
    SUM(total_amount) AS '消费总额'
FROM orders 
WHERE status = '已完成'
GROUP BY customer
ORDER BY 消费总额 DESC;
-- 结果：张三 31292, 李四 25044, 钱七 30997, 赵六 19596, 王五 8999, 孙八 7999

**实战提醒：**SUM只对数值列有效。对字符串列SUM会返回0，不会报错。所以SUM之前先确认列的数据类型。

1.3 AVG —— 平均值

-- ============================================
-- AVG 用法
-- 公众号：船长Talk
-- ============================================

-- 平均订单金额
SELECT AVG(total_amount) AS '平均订单金额' FROM orders;
-- 结果：6978.70

-- 各分类的平均订单金额
SELECT 
    category AS '分类',
    AVG(total_amount) AS '平均订单金额',
    COUNT(*) AS '订单数'
FROM orders 
WHERE status = '已完成'
GROUP BY category
ORDER BY 平均订单金额 DESC;
-- 电脑最高，配件最低

-- ⚠️ AVG 的陷阱：NULL值不参与计算
-- 如果某行 total_amount 是 NULL，它不参与AVG的分母
-- 想包含NULL（当0处理），用 COALESCE
SELECT AVG(COALESCE(total_amount, 0)) AS '含NULL的平均值'
FROM orders;

**避坑指南：**AVG的一个大坑是——1笔99999的订单和9笔100的订单，平均是10999.8，但"真实水平"其实更接近100。均值容易被极端值拉偏，这种情况考虑用中位数（后文讲）。

1.4 MAX / MIN —— 最大值 / 最小值

-- ============================================
-- MAX / MIN 用法
-- 公众号：船长Talk
-- ============================================

-- 最大/最小订单金额
SELECT 
    MAX(total_amount) AS '最大订单金额',
    MIN(total_amount) AS '最小订单金额',
    MAX(total_amount) - MIN(total_amount) AS '极差'
FROM orders;
-- 结果：15998 / 999 / 14999

-- 最大/最小下单日期（查看数据时间范围）
SELECT 
    MIN(order_date) AS '最早下单日期',
    MAX(order_date) AS '最晚下单日期'
FROM orders;
-- 结果：2026-04-01 / 2026-04-10

-- 🔥 实战场景：每个客户的最近一次购买日期（RFM模型中的Recency）
SELECT 
    customer AS '客户',
    MAX(order_date) AS '最近购买日期'
FROM orders
GROUP BY customer
ORDER BY 最近购买日期 DESC;
-- 这个查询是RFM用户分层的基础！

**实战技巧：**MAX/MIN不只用于数字，也常用于日期。计算"最近登录时间""最早上岗日期"都是这个套路。

二、GROUP BY —— 分组统计的核心

GROUP BY是SQL数据分析的灵魂。

没有GROUP BY，聚合函数只能算全表的一个值；加了GROUP BY，就能按维度拆开算。

打个比方：COUNT(*)是"一共有多少订单"，加了GROUP BY category就是"每个品类有多少订单"。

2.1 单维度分组

-- ============================================
-- GROUP BY 单维度分组
-- 公众号：船长Talk
-- ============================================

-- 各品类的订单数量和销售额
SELECT 
    category AS '品类',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '总销售额',
    AVG(total_amount) AS '平均订单金额'
FROM orders
WHERE status = '已完成'
GROUP BY category
ORDER BY 总销售额 DESC;
-- 结果示例：
-- | 品类 | 订单数 | 总销售额 | 平均订单金额 |
-- | 电脑 |   4    | 52996   |  13249.00    |
-- | 手机 |   4    | 39995   |  9998.75     |
-- | 配件 |   3    |  7742   |  2580.67     |
-- | 平板 |   3    | 19195   |  6398.33     |

2.2 多维度分组

-- 多维度分组：品类 + 客户
SELECT 
    category AS '品类',
    customer AS '客户',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '消费总额'
FROM orders
WHERE status = '已完成'
GROUP BY category, customer
ORDER BY 品类, 消费总额 DESC;
-- 可以看出每个客户在哪个品类花了多少钱

**关键原则：**SELECT后面的列，要么出现在GROUP BY里，要么被聚合函数包裹。否则SQL会报错。

-- ❌ 错误写法：customer没有出现在GROUP BY中，也没有被聚合
SELECT customer, category, COUNT(*)
FROM orders
GROUP BY category;

-- ✅ 正确写法：把customer加到GROUP BY
SELECT customer, category, COUNT(*)
FROM orders
GROUP BY customer, category;

2.3 GROUP BY 日期处理

-- ============================================
-- 按日期/月份 分组统计
-- 公众号：船长Talk
-- ============================================

-- 按天统计每日销售额
SELECT 
    order_date AS '日期',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '日销售额'
FROM orders
WHERE status = '已完成'
GROUP BY order_date
ORDER BY 日期;

-- 🔥 按月统计（DATE_FORMAT，MySQL语法）
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS '月份',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '月销售额'
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 月份;

-- 按周统计（YEARWEEK函数）
SELECT 
    YEARWEEK(order_date, 1) AS '周次',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '周销售额'
FROM orders
GROUP BY YEARWEEK(order_date, 1)
ORDER BY 周次;

三、HAVING —— 分组后的条件过滤

WHERE过滤行，HAVING过滤组。

这是新手最容易搞混的两个关键字。一句话记：WHERE在GROUP BY之前执行（过滤原始行），HAVING在GROUP BY之后执行（过滤聚合结果）。

-- ============================================
-- HAVING 用法
-- 公众号：船长Talk
-- ============================================

-- ❌ 错误：WHERE里不能用聚合函数
-- SELECT category, SUM(total_amount) FROM orders WHERE SUM(total_amount) > 10000 GROUP BY category;

-- ✅ 正确：用HAVING过滤聚合结果
SELECT 
    category AS '品类',
    SUM(total_amount) AS '总销售额'
FROM orders
WHERE status = '已完成'     -- 先过滤：只要已完成订单
GROUP BY category
HAVING SUM(total_amount) > 10000  -- 再过滤：品类总销售额 > 10000
ORDER BY 总销售额 DESC;
-- 结果：电脑(52996)、手机(39995)、平板(19195)，配件被过滤掉

-- 🔥 实战场景：找出消费超过2次的客户
SELECT 
    customer AS '客户',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '消费总额'
FROM orders
WHERE status = '已完成'
GROUP BY customer
HAVING COUNT(*) >= 2
ORDER BY 消费总额 DESC;
-- 结果：张三、李四、赵六、钱七（都有2单以上）

-- 🔥 实战场景：每个品类中，平均订单金额高于5000的
SELECT 
    category AS '品类',
    AVG(total_amount) AS '平均订单金额',
    COUNT(*) AS '订单数'
FROM orders
WHERE status = '已完成'
GROUP BY category
HAVING AVG(total_amount) > 5000
ORDER BY 平均订单金额 DESC;

**执行顺序速记：**FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

WHERE先过滤原始数据，GROUP BY分组，HAVING再过滤分组结果。别搞反了。

四、组合统计：一次查询出完整报告

工作中最常见的需求：一张表搞出完整的统计报告。

4.1 多维度综合统计

-- ============================================
-- 综合统计报告
-- 公众号：船长Talk
-- ============================================

-- 各品类的完整统计（订单数/销售额/平均金额/最大单笔/最小单笔）
SELECT 
    category AS '品类',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '总销售额',
    ROUND(AVG(total_amount), 2) AS '平均订单金额',
    MAX(total_amount) AS '最大单笔',
    MIN(total_amount) AS '最小单笔',
    COUNT(DISTINCT customer) AS '客户数'
FROM orders
WHERE status = '已完成'
GROUP BY category
ORDER BY 总销售额 DESC;
-- 一次查询，7个指标，一份完整的品类分析报告

4.2 CASE WHEN + 聚合：条件聚合

-- ============================================
-- CASE WHEN 条件聚合（🔥 高频面试题）
-- 公众号：船长Talk
-- ============================================

-- 各品类的已完成/已取消/待发货订单数（一次查询搞定）
SELECT 
    category AS '品类',
    COUNT(*) AS '总订单数',
    SUM(CASE WHEN status = '已完成' THEN 1 ELSE 0 END) AS '已完成',
    SUM(CASE WHEN status = '已取消' THEN 1 ELSE 0 END) AS '已取消',
    SUM(CASE WHEN status = '待发货' THEN 1 ELSE 0 END) AS '待发货',
    SUM(CASE WHEN status = '已退款' THEN 1 ELSE 0 END) AS '已退款',
    ROUND(
        SUM(CASE WHEN status = '已完成' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 
        1
    ) AS '完成率%'
FROM orders
GROUP BY category
ORDER BY 总订单数 DESC;

-- 🔥 这个技巧叫"条件聚合"，能在一行里做多次统计
-- 不需要写多个查询，一次GROUP BY搞定所有维度的交叉统计
-- 面试高频题！必会！

**条件聚合是数据分析师的看家本领。**不会这个，报表需求一来就得写十几个查询，效率低下。

4.3 ROLLUP —— 小计与总计

-- ============================================
-- ROLLUP 生成小计和总计
-- 公众号：船长Talk
-- ============================================

-- 各品类 + 总计（MySQL语法）
SELECT 
    IFNULL(category, '【总计】') AS '品类',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '总销售额'
FROM orders
WHERE status = '已完成'
GROUP BY category WITH ROLLUP;
-- 结果多一行：品类=【总计】，订单数=14，总销售额=110777

-- 多维度ROLLUP：品类 + 客户 + 小计 + 总计
SELECT 
    IFNULL(category, '【小计】') AS '品类',
    IFNULL(customer, '【合计】') AS '客户',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '总销售额'
FROM orders
WHERE status = '已完成'
GROUP BY category, customer WITH ROLLUP;
-- 结果包含：每个品类每个客户的明细 + 品类小计 + 总计
-- 适合直接导Excel当报表用

五、实战场景：从需求到SQL

最后，用3个真实工作场景把上面的知识点串起来。

5.1 场景一：月度销售看板

-- ============================================
-- 实战：月度销售看板
-- 公众号：船长Talk
-- ============================================

-- 按日期统计，附带环比增长率
SELECT 
    order_date AS '日期',
    COUNT(*) AS '订单数',
    SUM(total_amount) AS '日销售额',
    -- 环比：和前一天对比
    ROUND(
        (SUM(total_amount) - LAG(SUM(total_amount), 1) OVER (ORDER BY order_date))
        / LAG(SUM(total_amount), 1) OVER (ORDER BY order_date) * 100,
        1
    ) AS '环比增长率%'
FROM orders
WHERE status = '已完成'
GROUP BY order_date
ORDER BY 日期;
-- LAG是窗口函数，这里先用GROUP BY算出每天的值，再和前一天对比

5.2 场景二：用户RFM分层

-- ============================================
-- 实战：用户RFM分层（简化版）
-- 公众号：船长Talk
-- R = 最近购买天数（越小越好）
-- F = 购买频次（越多越好）
-- M = 累计消费金额（越多越好）
-- ============================================

SELECT 
    customer AS '客户',
    DATEDIFF(CURRENT_DATE(), MAX(order_date)) AS 'R-最近购买天数',
    COUNT(*) AS 'F-购买频次',
    SUM(total_amount) AS 'M-累计消费',
    -- 简单分层：R=2且M>=10000为高价值客户
    CASE 
        WHEN DATEDIFF(CURRENT_DATE(), MAX(order_date)) = 2 
             AND SUM(total_amount) >= 10000 
        THEN '⭐ 高价值'
        WHEN COUNT(*) >= 2 THEN '💎 活跃客户'
        WHEN DATEDIFF(CURRENT_DATE(), MAX(order_date)) > 7 THEN '⚠️ 流失风险'
        ELSE '👤 普通客户'
    END AS '客户分层'
FROM orders
WHERE status = '已完成'
GROUP BY customer
ORDER BY `M-累计消费` DESC;

5.3 场景三：各品类Top1商品

-- ============================================
-- 实战：各品类销售额最高的商品
-- 公众号：船长Talk
-- ============================================

SELECT 
    category AS '品类',
    product AS '商品',
    total_amount AS '销售额',
    quantity AS '销量'
FROM orders o1
WHERE status = '已完成'
  AND total_amount = (
      -- 子查询：找该品类最大订单金额
      SELECT MAX(total_amount) 
      FROM orders o2 
      WHERE o2.category = o1.category 
        AND o2.status = '已完成'
  )
ORDER BY 销售额 DESC;

-- 🔥 如果一个品类有多个最大值，上面会返回多行
-- 只要一个的话，可以在外面包一层 LIMIT
SELECT * FROM (
    SELECT 
        category AS '品类',
        product AS '商品',
        total_amount AS '销售额'
    FROM orders o1
    WHERE status = '已完成'
      AND total_amount = (
          SELECT MAX(total_amount) 
          FROM orders o2 
          WHERE o2.category = o1.category 
            AND o2.status = '已完成'
      )
    GROUP BY category
    ORDER BY 销售额 DESC
) t
GROUP BY 品类;

六、聚合函数速查表

-- ============================================
-- 聚合函数速查表
-- 公众号：船长Talk
-- ============================================

-- 1. COUNT(*)      → 统计所有行
-- 2. COUNT(列名)   → 统计非NULL行
-- 3. COUNT(DISTINCT 列名) → 统计去重后的数量
-- 4. SUM(列名)     → 求和
-- 5. AVG(列名)     → 平均值（注意极端值影响）
-- 6. MAX(列名)     → 最大值
-- 7. MIN(列名)     → 最小值
-- 8. GROUP BY      → 按列分组
-- 9. HAVING        → 过滤分组结果（WHERE过滤原始行）
-- 10. WITH ROLLUP  → 生成小计和总计
-- 11. CASE WHEN聚合 → 条件聚合（一次查询多个维度统计）

-- 执行顺序：
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

总结

聚合函数 + GROUP BY是SQL数据分析的"吃饭家伙"。

5个聚合函数（COUNT/SUM/AVG/MAX/MIN）+ GROUP BY + HAVING，能解决90%的统计需求。

进阶技巧：**条件聚合（CASE WHEN + SUM）**是面试高频考点，工作中也极其常用，务必掌握。

下一篇预告：《SQL多表查询完全指南：JOIN的7种用法详解》，讲怎么把多张表关联起来查询。这是SQL从"能查"到"查得好"的关键一步。

觉得有用？点赞收藏，关注船长Talk，每天学一点数据分析实战技能。

📖 SQL数据分析系列：

① SQL零基础入门：10个语句解决80%的查询问题

② SQL数据操作完全指南：增删改查实战详解

③ SQL聚合函数与分组统计：数据分析核心技能（本文）

④ SQL多表查询完全指南：JOIN的7种用法详解（下一篇）