MySQL / 系统课时
实现统计报表查询
实现统计报表查询
这一课围绕订单金额、商品销量和用户消费三类常见报表,把明细订单转换成可直接展示的统计结果。重点是掌握聚合函数、分组、条件过滤以及避免重复统计的基本写法。
学习目标
- 能使用
SUM、COUNT、GROUP BY完成订单与销量统计 - 能正确区分明细过滤的
WHERE与聚合结果过滤的HAVING - 能为常见报表查询选择合适的时间范围和索引
准备报表样例数据
下面建立一个独立的报表示例表,并插入几笔已支付和已取消订单。金额使用订单中的 quantity * unit_price 计算,避免依赖当前商品价格。
CREATE TABLE report_orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL
);
INSERT INTO report_orders
(user_id, product_id, quantity, unit_price, status, created_at)
VALUES
(1, 101, 2, 100.00, 'PAID', '2026-09-01 10:00:00'),
(1, 102, 1, 300.00, 'PAID', '2026-09-02 11:00:00'),
(2, 101, 3, 100.00, 'PAID', '2026-09-03 12:00:00'),
(2, 103, 1, 500.00, 'CANCELLED', '2026-09-03 13:00:00');预期结果
Query OK, 0 rows affected
Query OK, 4 rows affected报表通常只统计已完成业务的记录,因此后续查询都会通过 status = 'PAID' 排除已取消订单。
统计订单金额
总成交金额属于对多行明细求和的问题。先在 WHERE 中筛选有效订单和时间范围,再对每行的购买数量与单价乘积求和即可。
SELECT COUNT(*) AS paid_order_count,
SUM(quantity * unit_price) AS total_amount
FROM report_orders
WHERE status = 'PAID'
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-10-01 00:00:00';预期结果
+------------------+--------------+
| paid_order_count | total_amount |
+------------------+--------------+
| 3 | 800.00 |
+------------------+--------------+时间查询推荐使用左闭右开的范围,例如某个月使用 >= 月初 与 < 下月月初。这种写法不会遗漏带时分秒的数据,也比对日期列套 DATE() 函数更容易利用索引。
统计商品销量
商品销量需要按 product_id 分组。SUM(quantity) 表示售出的件数,而 SUM(quantity * unit_price) 表示该商品贡献的成交金额,两者的统计含义不同。
SELECT product_id,
SUM(quantity) AS units_sold,
SUM(quantity * unit_price) AS sales_amount
FROM report_orders
WHERE status = 'PAID'
GROUP BY product_id
ORDER BY units_sold DESC, product_id ASC;预期结果
+------------+------------+--------------+
| product_id | units_sold | sales_amount |
+------------+------------+--------------+
| 101 | 5 | 500.00 |
| 102 | 1 | 300.00 |
+------------+------------+--------------+这里不能用 COUNT(*) 代替销量,因为一条订单记录可能购买多件商品。COUNT(*) 统计的是订单明细行数,SUM(quantity) 才是商品件数。
统计用户消费并筛选高价值用户
用户消费报表既要分组,也常常需要根据聚合后的金额再次过滤。这时应使用 HAVING,因为 WHERE 执行时聚合值还没有产生。
SELECT user_id,
COUNT(*) AS order_count,
SUM(quantity * unit_price) AS total_spent
FROM report_orders
WHERE status = 'PAID'
GROUP BY user_id
HAVING SUM(quantity * unit_price) >= 300.00
ORDER BY total_spent DESC;预期结果
+---------+-------------+-------------+
| user_id | order_count | total_spent |
+---------+-------------+-------------+
| 1 | 2 | 500.00 |
| 2 | 1 | 300.00 |
+---------+-------------+-------------+可以把判断规则记成一句话:WHERE 过滤参与统计的原始行,HAVING 过滤已经完成分组计算的结果。状态、时间、用户 ID 等明细条件应尽量放在 WHERE 中,减少真正参与聚合的数据量。
为报表查询补上索引
当订单表增长后,报表最容易变慢的是大范围扫描。前面的查询都先按状态和时间筛选,因此可以根据真实查询模式建立联合索引。
CREATE INDEX idx_report_orders_status_created
ON report_orders (status, created_at);
EXPLAIN
SELECT SUM(quantity * unit_price) AS total_amount
FROM report_orders
WHERE status = 'PAID'
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-10-01 00:00:00';预期结果
EXPLAIN 结果中 key 应优先显示 idx_report_orders_status_created;rows 等估算值会随数据量和统计信息变化。索引不是越多越好。如果系统主要按用户和时间查询消费记录,可能更适合 (user_id, created_at);如果报表只做每天一次的全量离线统计,也未必值得为每种聚合组合建立索引。应根据高频过滤条件和实际执行计划选择索引,而不是根据 GROUP BY 字段机械添加。
避免报表中的重复统计
统计查询最常见的错误不是语法错误,而是口径错误。连接其他一对多表时,一条订单可能被展开成多行,随后 SUM 会把金额重复累加;状态条件遗漏也会把取消订单算进成交额。编写报表时应先确认一行代表什么业务粒度,再决定怎样分组和聚合,并使用少量已知数据手工核对结果。只要统计口径、过滤条件和数据粒度三者一致,复杂报表也可以从这些基础聚合逐步组合出来。