2.21 聚合函数的特性与避坑指南（NULL值处理、DISTINCT在聚合函数中的应用）
开篇：为什么聚合函数用不对，统计结果全白费
我刚入行时，运营让我统计“店铺独立访客数”。我直接SELECT COUNT(user_id) FROM traffic，得出一个数。运营说不对，因为同一个用户可能访问多次，重复计数了。我这才知道，要用COUNT(DISTINCT user_id)。还有一次，我统计“已支付订单的平均金额”，用了AVG(amount)，结果比财务算的低。排查后发现，订单表里混了退款订单（金额为0），0值拉低了平均值。
聚合函数是数据分析中最常用的工具，但也是最容易踩坑的地方。NULL值、重复值、数据类型、过滤条件……稍不注意，统计结果就错了。这一章我会带你彻底搞懂聚合函数的特性和避坑技巧，让你统计的每个数字都经得起推敲。
学习前准备：
已完成MySQL安装（参考系列前几章）
已安装DBeaver或Navicat
准备一个练习数据库，比如
agg_skill_demo
学习前环境准备
步骤1：确保MySQL服务已启动。
步骤2：创建练习数据库和表，并插入包含各种坑的测试数据。
CREATE DATABASE agg_skill_demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE agg_skill_demo; -- 订单表（包含NULL、0值、重复数据等） CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, amount DECIMAL(10,2), order_status VARCHAR(20), quantity INT ); INSERT INTO orders (user_id, amount, order_status, quantity) VALUES (1001, 299.00, '已支付', 2), (1001, NULL, '已支付', 1), -- 金额为NULL (1002, 189.00, '已取消', 3), (1003, 599.00, '已支付', 1), (1001, 399.00, '已支付', 1), (1004, 0.00, '已支付', 5), -- 金额为0 (1005, 1299.00, '已支付', 1), (1002, 89.00, '已取消', 2), (1006, 89.00, '已支付', 10), (1003, 259.00, '已支付', 1), (1001, 299.00, '已支付', 2); -- 重复订单（模拟数据重复）聚合函数基础认知与高频使用场景
聚合函数对一组数据进行计算，返回单个结果。在电商数据分析中，你几乎每天都会用到：
订单统计：总订单数、总GMV、客单价。
用户分析：独立访客数、复购率、平均下单金额。
商品分析：总销量、最高/最低价格。
库存管理：总库存量、平均周转天数。
常用聚合函数：
| 函数 | 作用 | 电商场景 |
|---|---|---|
COUNT() | 计数 | 订单数、用户数 |
SUM() | 求和 | GMV、销量 |
AVG() | 平均值 | 客单价、件单价 |
MAX() | 最大值 | 最高订单金额 |
MIN() | 最小值 | 最低售价 |
我的踩坑经历：有一次统计“活跃用户数”，我用了COUNT(user_id)，结果同一个用户被计了多次。后来改成COUNT(DISTINCT user_id)才正确。去重是统计用户数时的必备操作。 |
电商场景高频聚合函数详解
4.1 COUNT：计数
4.1.1 基础语法
COUNT(*) -- 所有行（包括NULL） COUNT(列名) -- 非NULL的行数 COUNT(DISTINCT 列名) -- 去重后计数4.1.2 电商实操案例
案例一：总订单数（包括无效订单）
SELECT COUNT(*) FROM orders;预期结果：11行。
案例二：有效订单数（amount不为NULL）
SELECT COUNT(amount) FROM orders;预期结果：10（因为有一行amount为NULL）。
案例三：独立用户数
SELECT COUNT(DISTINCT user_id) FROM orders;预期结果：6（1001,1002,1003,1004,1005,1006）。
案例四：已支付订单数
SELECT COUNT(*) FROM orders WHERE order_status = '已支付';预期结果：8（排除已取消的2条）。
4.2 SUM：求和
4.2.1 基础语法
SUM(列名) -- 自动忽略NULL4.2.2 电商实操案例
案例一：全店GMV（已支付订单金额总和）
SELECT SUM(amount) FROM orders WHERE order_status = '已支付';预期结果：299 + 599 + 399 + 0 + 1299 + 89 + 259 + 299 = 3243? 计算：299+599=898，+399=1297，+0=1297，+1299=2596，+89=2685，+259=2944，+299=3243。注意金额为NULL的行被忽略，金额为0的行计入（0不影响）。
案例二：女装旗舰店的总销量（假设有shop_name字段，这里用全店）
SELECT SUM(quantity) FROM orders WHERE order_status = '已支付';预期结果：2+1+1+5+1+10+1+2 = 23。
4.3 AVG：平均值
4.3.1 基础语法
AVG(列名) -- 自动忽略NULL，但包含04.3.2 电商实操案例
案例一：已支付订单的平均金额
SELECT AVG(amount) FROM orders WHERE order_status = '已支付';预期结果：总GMV 3243 / 8笔 = 405.375。注意金额为0的订单ORD006? 实际ORD006金额1299，没有0？ORD005金额0，是已支付吗？在插入数据中ORD005是0.00，已支付。所以平均包含0，会拉低平均值。
4.4 MAX/MIN：最大/最小值
4.4.1 基础语法
MAX(列名) MIN(列名)4.4.2 电商实操案例
案例一：最高订单金额
SELECT MAX(amount) FROM orders;预期结果：1299。
案例二：最低订单金额（已支付）
SELECT MIN(amount) FROM orders WHERE order_status = '已支付';预期结果：0（因为ORD005的0元订单）。
4.5 避坑提醒
COUNT(1)和COUNT(*)性能几乎一样，推荐用COUNT(*)。SUM和AVG会自动忽略NULL，但不会忽略0，要根据业务决定是否排除0。使用
AVG前，如果要去除异常值，可以在WHERE中过滤。
实操避坑提醒：统计客单价时，应该用SUM(amount)/COUNT(*)，而不是AVG(amount)，因为AVG(amount)会自动忽略NULL，而分母应该是总订单数（包括金额为NULL的订单？通常NULL代表数据缺失，不应计入）。正确做法：先过滤掉金额为NULL的订单。
聚合函数中NULL值的处理逻辑
5.1 核心规则
COUNT(列名)忽略NULL。SUM(列名)忽略NULL（相当于0）。AVG(列名)忽略NULL（只对非NULL值求平均）。MAX/MIN忽略NULL（除非全为NULL，则返回NULL）。
5.2 电商实操案例
案例一：COUNT(amount) vs COUNT(*)
SELECT COUNT(*) AS total_rows, COUNT(amount) AS non_null_amount FROM orders;预期结果：total_rows=11, non_null_amount=10。
案例二：SUM(amount) 自动忽略NULL
SELECT SUM(amount) FROM orders;预期结果：忽略NULL行，总和为3243? 之前计算已支付订单总和3243，但包含已取消订单？全表求和包括已取消的189和89，所以总GMV（全订单）应为3243+189+89=3521。注意NULL被忽略。
案例三：AVG(amount) 只对非NULL计算
SELECT AVG(amount) FROM orders;预期结果：总金额3521 / 非NULL行数10 = 352.1。
5.3 避坑提醒
如果希望NULL参与计算（视为0），需要用
COALESCE(amount, 0)转换。统计订单数时，用
COUNT(*)；统计有金额的订单数，用COUNT(amount)。
我的踩坑经历：我统计“订单平均金额”时用了AVG(amount)，但有些订单金额为NULL（表示未支付），导致平均值只算了已支付的订单，运营以为整体客单价很高。后来改成SUM(amount)/COUNT(*)，NULL被忽略但分母包含NULL订单，结果又不对。最终确定：只统计已支付订单，用WHERE order_status = '已支付'。
DISTINCT在聚合函数中的应用规则
6.1 基础语法
COUNT(DISTINCT 列名) SUM(DISTINCT 列名) -- 很少用，对去重后的值求和，业务含义特殊 AVG(DISTINCT 列名) -- 也很少用6.2 电商实操案例
案例一：独立访客数（UV）
SELECT COUNT(DISTINCT user_id) FROM orders;案例二：去重后的商品价格平均值（每个价格只算一次）
SELECT AVG(DISTINCT amount) FROM orders WHERE amount IS NOT NULL;注意：这个指标业务意义不大，只是演示。
案例三：独立用户的总消费金额（每个用户只算一次？不合理，通常用SUM(amount) GROUP BY user_id）
-- 错误用法：SUM(DISTINCT amount) 会把同一金额只算一次 SELECT SUM(DISTINCT amount) FROM orders WHERE order_status = '已支付';6.3 避坑提醒
COUNT(DISTINCT column)是高频用法，用于统计去重数量。SUM(DISTINCT column)很少用，因为通常需要的是所有值的和，而不是去重后的和。DISTINCT会消耗更多性能，大数据量时谨慎使用。
实操避坑提醒：统计“独立用户数”必须用COUNT(DISTINCT user_id)，否则会重复计数。但如果你已经用GROUP BY user_id聚合，再COUNT(*)也是正确的。
综合实操案例：服饰类目月度店铺订单数据汇总统计
7.1 案例背景
某服饰类目店铺需要生成6月份的经营数据汇总，包含以下指标：
总订单数（所有订单）。
有效订单数（已支付且金额不为空）。
GMV（有效订单金额总和）。
客单价（GMV / 有效订单数）。
独立用户数（去重）。
最高订单金额、最低订单金额（有效订单）。
总销量（有效订单的商品数量总和）。
7.2 分步操作
步骤1：创建测试数据（已完成）
步骤2：总订单数
SELECT COUNT(*) AS total_orders FROM orders;步骤3：有效订单数（amount IS NOT NULL AND order_status = ‘已支付’）
SELECT COUNT(*) AS valid_orders FROM orders WHERE order_status = '已支付' AND amount IS NOT NULL;步骤4：GMV
SELECT SUM(amount) AS gmv FROM orders WHERE order_status = '已支付' AND amount IS NOT NULL;步骤5：客单价
SELECT ROUND(SUM(amount) / COUNT(*), 2) AS avg_order_value FROM orders WHERE order_status = '已支付' AND amount IS NOT NULL;步骤6：独立用户数
SELECT COUNT(DISTINCT user_id) AS unique_users FROM orders WHERE order_status = '已支付' AND amount IS NOT NULL;步骤7：最高/最低订单金额
SELECT MAX(amount) AS max_amount, MIN(amount) AS min_amount FROM orders WHERE order_status = '已支付' AND amount IS NOT NULL;步骤8：总销量
SELECT SUM(quantity) AS total_items FROM orders WHERE order_status = '已支付' AND amount IS NOT NULL;步骤9：一站式查询（组合）
SELECT COUNT(*) AS total_orders, SUM(CASE WHEN order_status = '已支付' AND amount IS NOT NULL THEN 1 ELSE 0 END) AS valid_orders, SUM(CASE WHEN order_status = '已支付' AND amount IS NOT NULL THEN amount ELSE 0 END) AS gmv, ROUND(SUM(CASE WHEN order_status = '已支付' AND amount IS NOT NULL THEN amount ELSE 0 END) / NULLIF(SUM(CASE WHEN order_status = '已支付' AND amount IS NOT NULL THEN 1 ELSE 0 END), 0), 2) AS avg_order_value, COUNT(DISTINCT CASE WHEN order_status = '已支付' AND amount IS NOT NULL THEN user_id END) AS unique_users, MAX(CASE WHEN order_status = '已支付' AND amount IS NOT NULL THEN amount END) AS max_amount, MIN(CASE WHEN order_status = '已支付' AND amount IS NOT NULL THEN amount END) AS min_amount, SUM(CASE WHEN order_status = '已支付' AND amount IS NOT NULL THEN quantity ELSE 0 END) AS total_items FROM orders;7.3 结果验证
执行一站式查询，检查数据逻辑一致性（如客单价 = GMV / 有效订单数）。
📌 电商数据合规提示：统计独立用户数时，使用的是内部user_id，不涉及用户手机号等敏感信息。如果原始表中有手机号字段，绝对不能用COUNT(DISTINCT phone)，因为会导出用户个人标识。应使用脱敏后的用户ID。
本章踩坑清单与合规总结
8.1 新手常见踩坑
| 错误 | 原因 | 正确做法 |
|---|---|---|
COUNT(column)统计用户数 | 忽略了NULL，且未去重 | 用COUNT(DISTINCT user_id) |
AVG(amount)包含0值 | 0值拉低平均值 | 用WHERE amount > 0过滤 |
SUM(amount)未过滤退款订单 | 退款订单金额为负或0 | 加WHERE order_status = '已支付' |
COUNT(DISTINCT col1, col2)报错 | MySQL不支持多列DISTINCT计数 | 用子查询或CONCAT |
| 聚合函数与普通列混用无GROUP BY | 语法错误 | 要么全聚合，要么加GROUP BY |
8.2 性能优化建议
为
WHERE条件中的列建立索引。大数据量去重（
COUNT(DISTINCT col)）可能很慢，考虑用近似算法或预聚合。避免在聚合函数中使用复杂的表达式。
8.3 电商数据合规红线
独立用户数统计：不要使用手机号、身份证等个人标识字段进行去重，应使用内部匿名ID。
统计结果分享：GMV、客单价等核心经营数据，仅限内部授权人员查看，不得外泄。
NULL值处理：如果订单金额为NULL代表数据缺失，应在统计报告中说明，不要强行填充0。
结语
聚合函数是SQL统计的基石，但NULL值、重复值、过滤条件等细节决定了统计结果的准确性。掌握COUNT、SUM、AVG、MAX、MIN的特性，以及DISTINCT和NULL的处理规则，你就能自信地计算出任何经营指标。下一章我们会讲GROUP BY分组聚合，让统计更精细化。
有问题的评论区留言，我看到会回复。
