MariaDB 教程 / 用 COUNT、SUM、AVG、MIN 和 MAX 汇总数据

COUNT、SUM、AVG、MIN 和 MAX 把多行数据汇总为数量、合计、平均值与边界值。COUNT(*) 统计行,COUNT(列) 统计该列的非 NULL 值;解释结果前,应先明确每行代表什么、计量单位、平均值的分母与缺失值规则。

先定义每行与每个指标的含义

“有几种商品”“库存共有多少件”“每种商品平均多少件”和“库存金额是多少”是不同问题。一种商品是否只出现一行,数值表示件数还是金额,都会影响计算。SQL 能执行成功,不代表算出的就是需要的业务指标。

指标 表达式 单位或统计对象
商品数量 COUNT(*) 商品记录的行数
非缺失备注数量 COUNT(note) 非 NULL 的备注值
总库存 SUM(stock) 件数
平均库存 AVG(stock) 每个已知库存值对应的平均件数
库存金额 SUM(price*stock) 每行价格乘库存后的金额合计

本节先计算整张练习表的汇总,不把每个分类分开。将来连接其他表时,还必须检查一件商品是否因多条匹配记录而重复出现,否则相同金额可能被重复相加。指标的粒度应先确定,不能事后仅靠舍入修正错误总额。

函数的规则可对照 MariaDB 官方的 COUNT 文档、SUM 文档与 AVG 文档。

准备同时包含 NULL、空备注与零库存的数据

以 course_user 连接学习服务器,先执行 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。下面使用独立表 products_22_lab;若同名对象已存在,先检查表定义和记录,确认是否可以作为本节练习使用,不要覆盖用途不明的表。

完整代码应在 MariaDB 客户端中执行。保存文件时使用 UTF-8,再按照 SQL 文件与 SOURCE 教程用实际路径加载,例如 SOURCE C:/mariadb-lab/lesson.sql;。不要把 SQL 当成操作系统命令执行。

USE shopcourse;
CREATE TABLE products_22_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p22_price CHECK (price >= 0),
  CONSTRAINT ck_p22_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_22_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

原始韩文示例字符串保持不变:编号 1~6 分别表示笔记本、圆珠笔、马克杯、台灯、文件夹与线缆,分类表示文具、生活用品与电子产品。使用编号核对结果,可以避免翻译商品名称造成数据差异。

六行中有两条 NULL 备注、两条空字符串备注、两条有内容的备注。零库存商品仍然是实际存在的记录,也属于 COUNT(*) 的范围。NULL 与空字符串教程说明了为什么“没有备注值”和“备注是空字符串”会得到不同的计数。

一次查询核对所有汇总结果

SELECT COUNT(*) AS all_rows,COUNT(note) AS known_notes,
 SUM(stock) AS stock_total,AVG(stock) AS stock_average,
 MIN(price) AS lowest_price,MAX(price) AS highest_price,
 SUM(price*stock) AS inventory_total
FROM products_22_lab;
结果列 预期结果 解释
all_rows 6 全部商品记录
known_notes 4 空字符串不是 NULL,也被计入
stock_total 87 20 + 0 + 12 + 5 + 50 + 0
stock_average 14.5 87 除以六个非 NULL 库存值;显示小数位可能不同
lowest_price / highest_price 1500.00 / 25000.00 最低与最高单价
inventory_total 366000.00 逐行计算价格 × 库存,再合计

AVG(stock) 的分母是非 NULL 库存值的数量,不是无条件使用 COUNT(*)。本表 stock 不允许 NULL,因此分母恰好为六;零也参与分母。另一个允许缺失值的数据集若只有 0、10、NULL,平均值是 5,而不是把 NULL 当零之后的 10÷3。

因此,“未知库存”和“已确认没有库存”不能不加判断地互换。对 NULL 的填充值政策会直接改变均值。输出为 14.5000 或 14.5 主要是显示刻度的差别,应先核对实际数值与类型;涉及舍入规则时,再参考 ROUND 与 CAST 教程。

检查没有匹配行时的结果

SELECT COUNT(*),SUM(stock),AVG(stock),COALESCE(SUM(stock),0)
FROM products_22_lab WHERE price < 0;

本表不允许负价格,所以这个 WHERE 没有匹配行。四个结果依次为 0、NULL、NULL、0。没有 GROUP BY 的这条聚合查询仍会给出一行汇总结果;COUNT 的零和 SUM、AVG 的 NULL 表达不同含义。

COALESCE 在这里只把显示用的空合计转换成零,并没有补出任何原始记录。有些报表适合把“没有记录的合计”显示成零,但把没有观测值的平均数记录成实际测得的零,是另一项决定。应让读者或应用知道这个零来自汇总规则,而不是来自原始观测。

还应区分“完全没有输入行”和“有输入行但某个字段全部为 NULL”。COUNT(*) 可以帮助确认前者,COUNT(字段) 可以帮助检查可用值的数量,不能只看一个 SUM 的结果就判断原因。

先按行计算,再使用正确的聚合运算

SUM(price*stock) 先算每件商品的库存金额,再相加。SUM(price)*SUM(stock) 则把两个互不对应的总数相乘,会混入不同商品之间的组合,不是同一个指标。总额过大时,应先检查运算顺序与单位,而不是先怀疑小数精度。

SELECT product_id,price,stock,price*stock AS row_value
FROM products_22_lab ORDER BY product_id;

按编号逐行的 row_value 应为 70000.00、0.00、96000.00、125000.00、75000.00、0.00,相加是 366000.00。通过这六条明细可以直接追查总额;如果其中一项不对,核对该行的 price 和 stock,再检查汇总查询。

MIN(price) 与 MAX(price) 返回的是价格值,不会自动选出对应的商品名称。本例最低价格 1500.00 对应编号 2、5 两件商品;不能把某个随意取出的名称贴在最低价旁边。如果需要极值对应的全部商品,应使用明确的筛选或排序规则;ORDER BY 与 LIMIT 教程也说明了如何处理相同排序值。

从原始记录核对总额与重复输入

小样本可以逐行读取主键,并独立相加检查总数。出现异常时,确认六件商品是否被重复插入、NULL 是否被默默改成零、WHERE 是否筛掉了某些行,以及连接查询是否把一行扩成了多行。应先证明输入粒度正确,再调整最终的聚合表达式。

大规模汇总还需要考虑数据库结果类型与应用的数值范围,不要仅看单行能保存就认为总额永远不会超出调用方类型。本节的库存金额仅表示给定 price 与 stock 的乘积合计,不代表已经定义了成本、税额或其他会计估值规则。

这些查询不会修改原始六行。需要重练时,先确认数据库、表结构与记录,并备份应保留的数据。下面只清理已确认可丢弃的本节练习表;DROP TABLE 会删除定义与全部记录,普通 ROLLBACK 无法恢复,应与事务实验分开执行。

DROP TABLE shopcourse.products_22_lab;