MySQL / 系统课时

实现统计报表查询

实现统计报表查询

这一课围绕订单金额、商品销量和用户消费三类常见报表,把明细订单转换成可直接展示的统计结果。重点是掌握聚合函数、分组、条件过滤以及避免重复统计的基本写法。

学习目标

  • 能使用 SUM、COUNT、GROUP BY 完成订单与销量统计
  • 能正确区分明细过滤的 WHERE 与聚合结果过滤的 HAVING
  • 能为常见报表查询选择合适的时间范围和索引

准备报表样例数据

下面建立一个独立的报表示例表,并插入几笔已支付和已取消订单。金额使用订单中的 quantity * unit_price 计算,避免依赖当前商品价格。

sql
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');

预期结果

text
Query OK, 0 rows affected
Query OK, 4 rows affected

报表通常只统计已完成业务的记录,因此后续查询都会通过 status = 'PAID' 排除已取消订单。

统计订单金额

总成交金额属于对多行明细求和的问题。先在 WHERE 中筛选有效订单和时间范围,再对每行的购买数量与单价乘积求和即可。

sql
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';

预期结果

text
+------------------+--------------+
| paid_order_count | total_amount |
+------------------+--------------+
|                3 |       800.00 |
+------------------+--------------+

时间查询推荐使用左闭右开的范围,例如某个月使用 >= 月初 与 < 下月月初。这种写法不会遗漏带时分秒的数据,也比对日期列套 DATE() 函数更容易利用索引。

统计商品销量

商品销量需要按 product_id 分组。SUM(quantity) 表示售出的件数,而 SUM(quantity * unit_price) 表示该商品贡献的成交金额,两者的统计含义不同。

sql
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;

预期结果

text
+------------+------------+--------------+
| product_id | units_sold | sales_amount |
+------------+------------+--------------+
|        101 |          5 |       500.00 |
|        102 |          1 |       300.00 |
+------------+------------+--------------+

这里不能用 COUNT(*) 代替销量,因为一条订单记录可能购买多件商品。COUNT(*) 统计的是订单明细行数,SUM(quantity) 才是商品件数。

统计用户消费并筛选高价值用户

用户消费报表既要分组,也常常需要根据聚合后的金额再次过滤。这时应使用 HAVING,因为 WHERE 执行时聚合值还没有产生。

sql
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;

预期结果

text
+---------+-------------+-------------+
| user_id | order_count | total_spent |
+---------+-------------+-------------+
|       1 |           2 |      500.00 |
|       2 |           1 |      300.00 |
+---------+-------------+-------------+

可以把判断规则记成一句话:WHERE 过滤参与统计的原始行,HAVING 过滤已经完成分组计算的结果。状态、时间、用户 ID 等明细条件应尽量放在 WHERE 中,减少真正参与聚合的数据量。

为报表查询补上索引

当订单表增长后,报表最容易变慢的是大范围扫描。前面的查询都先按状态和时间筛选,因此可以根据真实查询模式建立联合索引。

sql
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';

预期结果

text
EXPLAIN 结果中 key 应优先显示 idx_report_orders_status_created;rows 等估算值会随数据量和统计信息变化。

索引不是越多越好。如果系统主要按用户和时间查询消费记录,可能更适合 (user_id, created_at);如果报表只做每天一次的全量离线统计,也未必值得为每种聚合组合建立索引。应根据高频过滤条件和实际执行计划选择索引,而不是根据 GROUP BY 字段机械添加。

避免报表中的重复统计

统计查询最常见的错误不是语法错误,而是口径错误。连接其他一对多表时,一条订单可能被展开成多行,随后 SUM 会把金额重复累加;状态条件遗漏也会把取消订单算进成交额。编写报表时应先确认一行代表什么业务粒度,再决定怎样分组和聚合,并使用少量已知数据手工核对结果。只要统计口径、过滤条件和数据粒度三者一致,复杂报表也可以从这些基础聚合逐步组合出来。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

实现统计报表查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通