Day27 - ECharts 数据统计、SQL 优化与日期补齐

一、Apache ECharts 快速了解

Apache ECharts 是一款基于 JavaScript 的数据可视化图表库，用于将后端提供的数据以柱形图、饼形图、折线图等形式直观展示。

在苍穹外卖项目中，数据统计模块的折线图、柱形图全部通过 ECharts 渲染。使用时的核心关注点在于：后端返回的数据格式必须与前端图表要求的格式一致，通常是日期列表与对应数值列表，两者通过逗号分隔以字符串形式返回。

---

二、四个数据统计接口的自实现思路

未参考讲义答案，先独立实现了营业额、用户、订单、销量排名四个统计接口。整体思路统一采用 GROUP BY 分组 SQL + Service 层组装 VO，与讲义的”按日 for 循环查 DB”思路完全不同。

2.1 营业额统计

需求：统计 [begin, end] 区间内，每天**已完成订单（status=5）**的金额合计。

实现思路：

1. Mapper 层写一条分组 SQL，GROUP BY date(order_time) 返回每天日期和营业额，结果为 List<Map<String, Object>>
2. Service 层遍历 Map 列表，分别组装日期字符串列表和营业额字符串列表
3. 用 String.join(",", list) 拼成 VO 需要的逗号分隔字符串

SQL 关键片段（已做索引优化后的写法）：

select date(order_time) as date, sum(amount) as turnover
from orders
where status = 5 and order_time >= #{begin} and order_time < #{endPlus1}
group by date(order_time)
order by date(order_time)

2.2 用户统计

需求：折线图两根线——每日总用户数和每日新增用户数。

实现思路（自行设计，先查前期总数 + 分组累加，比讲义每日两次查询更优）：

1. 先单独查一次：begin 日期之前注册的用户总数 previousTotalUser，作为起始基数
2. 分组查询 [begin, end] 区间每天的新增用户数（count(*) 按 date(create_time) 分组）
3. Service 层遍历每日新增，累加到 totalUser，同步生成三个 list：日期、每日新增、累计总用户

关键流程：

previousTotalUser = 查询 begin 之前总数
totalUser = previousTotalUser

for 每一天的新增用户 newUser:
    totalUser += newUser
    dateList     += 当天日期
    newUserList  += newUser
    totalUserList += totalUser

2.3 订单统计

需求：折线图展示每日总订单数 + 有效订单数（已完成），并返回整个区间的订单总数、有效订单数和订单完成率。

初始思路：分两次查询——一次查每天总订单数，一次查每天 status=5 的有效订单数。

优化后（从 AI 学到的知识）：用 sum(case when status=5 then 1 else 0 end) 在一条 SQL 里同时查出两个指标，从两次查询合并为一次：

select date(order_time) as date,
       count(*)                              as orderCount,
       sum(case when status = 5 then 1 else 0 end) as validOrderCount
from orders
where order_time >= #{begin} and order_time < #{endPlus1}
group by date(order_time)
order by date(order_time)

Service 层遍历结果时同时维护两个全局累计值：totalOrderCount（订单总数）和 validOrderCount（有效订单总数），最后计算：

orderCompletionRate = totalOrderCount == 0
    ? 0.0
    : validOrderCount.doubleValue() / totalOrderCount.doubleValue();

2.4 销量排名 Top10

需求：柱形图展示 [begin, end] 区间销量前 10 的商品（菜品 + 套餐），销量为商品销售份数。

SQL 思路：

• orders 表 INNER JOIN order_detail 表，条件 orders.status = 5（只统计已完成订单中的明细）

• GROUP BY order_detail.name 后 ORDER BY sum(number) DESC

• LIMIT 10 取前 10 名

这是四个接口中唯一一个从一开始就用了索引友好的时间范围写法的接口。

---

三、两个 SQL 优化技巧

当天从 AI 处和自己思考中确认了两条通用的 SQL 性能优化原则，并落到了代码里。

3.1 用半开区间替代日期函数包裹列

写法（❌ 不走索引）	写法（✅ 走索引）
date(order_time) between #{begin} and #{end}	order_time >= #{begin} and order_time < #{endPlus1}

原因：

• 当 order_time 列上建立了索引时，where 条件中对列使用 date() 函数会导致索引失效，退化为全表扫描

• 改为直接比较列值时，MySQL 可以利用索引的有序性快速定位区间

Service 层的配套处理：

LocalDate endPlus1 = end.plusDays(1);

传参时将结束日期往后加 1 天，配合 < endPlus1 天然等于 <= end 当天 23:59:59.999，不需要写 LocalDateTime.of(date, LocalTime.MAX) 这种容易出错的闭区间边界。

这条优化原则从销量排名接口学到，后续在索引修复中统一应用到了营业额、用户、订单三个统计接口。

3.2 条件聚合：SUM(CASE WHEN ...) 一次查询代替多次

今日首次接触的重点语法，在此单独强化记忆。 之前的写法是”同一张表、同一个时间范围、两个不同过滤条件 → 分两条 SQL 分别查”，今天学到用 CASE WHEN 嵌在聚合函数里，一条 SQL 同时出多个结果。

语法形态

最核心的写法（订单统计中已实际使用）：

count(*)                                    as orderCount,        -- 全部行数，即总订单数
sum(case when status = 5 then 1 else 0 end) as validOrderCount     -- 只统计 status=5 的行数

执行逻辑（逐行理解）

CASE WHEN 在 SQL 中的角色类似 Java 的三元表达式或 if-else，它是逐行求值，然后再交给外层的聚合函数：

某一行的 status 值	case when status = 5 then 1 else 0 end 的计算结果
1（待付款）	0
2（待接单）	0
3（已接单）	0
5（已完成）	1
6（已取消）	0

计算完每一行是 0 还是 1 后，外层 SUM(...) 再把所有值加起来——得到的恰好就是”满足 status = 5 的行数”，即有效订单数。

等价的另一种写法：count(case when status = 5 then 1 end)。不写 else 0 时，不满足条件的行返回 null，count 会自动忽略 null，结果完全一样。两种写法都会用，sum(… then 1 else 0 end) 更直观。

为什么要聚合里嵌条件，而不是写两条 SQL？

本质原因：两次查询会扫描两次表。 两条 SQL 虽然过滤条件只差一个 status = 5，但数据库要分别做两次：打开索引 → 定位范围 → 逐行读取 → 计数 → 返回结果集。用条件聚合并在一起后，整张表（或索引范围）只扫描一次，在遍历的过程中顺手把两种指标同时算出来。

收益

回到订单统计场景，对比非常直观：

做法	扫描次数	SQL 数量
讲义风格（每天查两次）	7 天 × 2 次 = 14 次扫描	14 条
你的初始想法（全区间查两次）	2 次扫描	2 条
SUM(CASE WHEN …) 条件聚合	1 次扫描	1 条

条件聚合把查询次数从 2 次压缩到 1 次，并且是零额外成本的语法结构，数据库天然支持，所有主流关系型数据库（MySQL、PostgreSQL、Oracle、SQL Server）写法完全一致，通用度非常高。

---

四、踩坑：GROUP BY 分组导致日期缺失

问题现象

对照讲义后发现的核心功能性 BUG：

GROUP BY date(order_time) 只会返回实际有数据的日期。如果查询 8.25 ~ 8.31 共 7 天，但其中 8.27、8.28 两天没有订单/用户，SQL 结果只返回 5 条记录。此时 VO 中的 dateList 只有 5 个日期，而前端折线图期望 7 天连续数据，导致 X 轴断档、图表与实际区间不一致。

解决方法：双 while 循环 + currentDate 指针补齐

营业额统计接口先自行修复，之后按同一模式套用到用户统计、订单统计。

核心结构：

LocalDate currentDate = begin;

for (Map<String, Object> map : mapList) {
    LocalDate date = LocalDate.parse(map.get("date").toString());

    // 内层 while：currentDate 和 date 之间缺的日期，逐条补 0
    while (currentDate.isBefore(date)) {
        dateList.add(currentDate.toString());
        turnoverList.add("0");
        // 其他指标同样补 0，总用户类指标保持不变
        currentDate = currentDate.plusDays(1);
    }

    // 添加当前这条真实数据
    dateList.add(date.toString());
    turnoverList.add(map.get("turnover").toString());
    // 累计类指标在此处累加
    currentDate = currentDate.plusDays(1);
}

// 外层 while：SQL 最后一条记录到 end 之间的尾部缺失日期继续补 0
while (currentDate.isBefore(endPlus1)) {
    dateList.add(currentDate.toString());
    turnoverList.add("0");
    currentDate = currentDate.plusDays(1);
}

不同指标补 0 的注意点：

• 营业额、订单数、新增用户这种当日流量型指标：缺失日直接补 0

• 总用户数这种存量累计型指标：缺失日保持上一天的累计值不变，不能简单补 0

---

五、自实现 vs 讲义实现 优劣对比

维度	自实现	讲义实现
SQL 查询次数（7 天为例）	营业额 1 次 / 用户 2 次 / 订单 1 次	营业额 7 次 / 用户 14 次 / 订单 14 次
DB 性能	显著更优，一次分组替代 N 次单点查询	简单 for 循环逐天查询，大数据量下压力大
日期完整性	需额外用双 while 补齐，实现复杂度较高	天然保证日期连续（先构造完整日期 List），无数据的日子自动是 0
代码结构	Service 层逻辑多，List<Map> + 手动转型类型弱，代码偏长	结构清晰直白，Service 伪代码式易读，Mapper 用通用 sumByMap / countByMap 复用性强
索引利用	修复后一致，统一半开区间写法	修复前一致，修复前也是逐天查询无日期函数问题
类型安全	Map<String, Object> 拿值需要 ((Number)map.get("x")).longValue()，IDE 不提示字段错误	直接 Double、Integer 单值返回，或用专用 DTO（如 GoodsSalesDTO），强类型

我的理解：

• 自实现强在性能，适合生产环境大数据量报表

• 讲义实现强在可靠性和可读性，”日期永远完整”这条在报表场景中是刚需

• 最优做法是两者结合：保留自实现的高性能 SQL 聚合思路 + 采用讲义的日期完整性骨架（先构造完整日期 List 再匹配结果，或双 while 补齐）

---

今日总结

模块	核心要点
ECharts	数据可视化图表库，后端只需按前端要求返回”逗号分隔日期列表 + 逗号分隔数值列表”即可
营业额统计	GROUP BY 分组查每日已完成订单金额；配合双 while 保证日期完整
用户统计	begin 前总用户数 + 每日新增累加 = 每日总用户；注意累计型指标补日期时不能清零
订单统计	count(*) + sum(case when status=5 then 1 else 0 end) 一条 SQL 同时拿两个值；完成率要做分母为 0 的保护
销量 Top10	JOIN order_detail + orders，分组排序 LIMIT 10；这里最早用对了 >= begin and < endPlus1 的半开区间索引写法
SQL 优化 1	时间列比较禁止用 date(col) 包裹，改用 col >= start and col < endPlus1 走索引
SQL 优化 2	同维度多指标统计用条件聚合 SUM(CASE WHEN ...) 一次查出，不要分多次查
日期补齐	GROUP BY 只返回有数据的日期，报表必须补齐无数据日期；双 while + currentDate 指针是可靠做法
优劣取舍	自实现性能好但需手动补齐日期；讲义循环查询代码简单。生产环境应采用”高性能 SQL + 日期完整性骨架”结合的方式