2.21 sql聚合函数的特性与避坑指南（NULL值处理、DISTINCT在聚合函数中的应用）

2.21 聚合函数的特性与避坑指南NULL值处理、DISTINCT在聚合函数中的应用开篇为什么聚合函数用不对统计结果全白费我刚入行时运营让我统计“店铺独立访客数”。我直接SELECT COUNT(user_id) FROM traffic得出一个数。运营说不对因为同一个用户可能访问多次重复计数了。我这才知道要用COUNT(DISTINCT user_id)。还有一次我统计“已支付订单的平均金额”用了AVG(amount)结果比财务算的低。排查后发现订单表里混了退款订单金额为00值拉低了平均值。聚合函数是数据分析中最常用的工具但也是最容易踩坑的地方。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不为NULLSELECT COUNT(amount) FROM orders;预期结果10因为有一行amount为NULL。案例三独立用户数SELECT COUNT(DISTINCT user_id) FROM orders;预期结果61001,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? 计算2995998983991297012971299259689268525929442993243。注意金额为NULL的行被忽略金额为0的行计入0不影响。案例二女装旗舰店的总销量假设有shop_name字段这里用全店SELECT SUM(quantity) FROM orders WHERE order_status 已支付;预期结果211511012 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没有0ORD005金额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_rows11, non_null_amount10。案例二SUM(amount) 自动忽略NULLSELECT SUM(amount) FROM orders;预期结果忽略NULL行总和为3243? 之前计算已支付订单总和3243但包含已取消订单全表求和包括已取消的189和89所以总GMV全订单应为3243189893521。注意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 电商实操案例案例一独立访客数UVSELECT 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;步骤4GMVSELECT 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 BY8.2 性能优化建议为WHERE条件中的列建立索引。大数据量去重COUNT(DISTINCT col)可能很慢考虑用近似算法或预聚合。避免在聚合函数中使用复杂的表达式。8.3 电商数据合规红线独立用户数统计不要使用手机号、身份证等个人标识字段进行去重应使用内部匿名ID。统计结果分享GMV、客单价等核心经营数据仅限内部授权人员查看不得外泄。NULL值处理如果订单金额为NULL代表数据缺失应在统计报告中说明不要强行填充0。结语聚合函数是SQL统计的基石但NULL值、重复值、过滤条件等细节决定了统计结果的准确性。掌握COUNT、SUM、AVG、MAX、MIN的特性以及DISTINCT和NULL的处理规则你就能自信地计算出任何经营指标。下一章我们会讲GROUP BY分组聚合让统计更精细化。有问题的评论区留言我看到会回复。