从明细到图表：日周月年聚合统计的完整链路与ECharts实践

几年前我第一次接到一个统计图需求，产品经理的原话是：“把订单表按天、按周、按月、按年聚一下，画个折线图，很简单的。”我当时的想法也一样简单——SQL 里 GROUP BY 一下，前端 ECharts 一渲染，完事。等我真的把第一版写出来，才发现“简单地按时间分组”背后全是坑：图表数据断档、跨年周归属乱了、时区对不上、数据量一大查询慢到怀疑人生。这篇文章就把这类需求整理成一条完整链路：数据库里的明细数据，如何变成日度、周度、月度、年度四档聚合数据，再稳妥地交给 ECharts 出图。适合正在做数据看板、报表统计接口的后端或全栈开发参考，也适合前端同学理解后端为什么要给那几种结构。

1. “不就是按时间分组吗”：一句话需求背后的四层复杂度

这类需求表面上是一个 GROUP BY，实际上一旦开始落地，你会撞上四层问题。不把这四层想清楚，后面大概率返工。

1.1 图表要的是“聚合趋势”，不是明细本身

数据库里存的是每一笔业务记录，比如订单事件明细表，一年可能上千万行。ECharts 要画的折线图、柱状图，本质上表达的是“某个时间段内发生了多少量、多少金额”，它是一个聚合结果，不是一个明细列表。如果把明细直接拉到前端渲染，浏览器内存和网络传输都扛不住，而且用户看到的也会是密密麻麻一堆点，根本没有趋势价值。

所以第一步要明确：需求天然要求聚合。查询返回的不应该是每一笔单，而应该是“每一条时间轴上的汇总值”。这个认知决定了后面所有设计。

1.2 四档粒度不是同一套逻辑的四个参数

很多人以为只要把 SQL 里的 GROUP BY 表达式换一下就完成日、周、月、年的切换，实际不是。举几个例子：

• 日度聚合要处理时区，否则晚上 8 点到 12 点的数据会被归到“昨天”还是“今天”都说不清；
• 周度聚合要定义“一周从周一开始还是周日开始”，还要考虑跨年周到底归到哪一年；
• 月度聚合要小心字符串排序，格式不当会导致 1 月排在 10 月后面；
• 年度聚合本身不难，但通常要配合“近 5 年”这种动态窗口，窗口边界算不对又会出问题。

每档粒度都有自己的口径细节，所以后面我实际使用的时候，是把四个粒度拆成四个方法分别实现，而不是硬拼一个万能 SQL。

1.3 口径一致性：同一个数字，两个部门算出来不一样

这是最容易扯皮的地方。一条交易记录里往往既有没有订单时间，又有支付时间，还有发货时间。统计图到底按哪个时间归属？如果定义不统一，业务同学拿日报和月报对账，永远对不上。

我见过最典型的情况是：A 报表按支付时间统计，B 报表按下单时间统计，两边数字差异不小，最后追查半天发现是口径问题。所以在写第一行 SQL 之前，先和需求方把“统计时间字段”敲死，并且写进接口注释里。

另一个隐藏口径是“零”和“没有数据”的区别。某天没有订单，SQL 聚合出来根本不会出现这一行。折线图如果直接缺一个点，视觉上是断线，业务会认为数据坏了。但如果你无脑补 0，又可能掩盖“数据管道延迟导致当天数据还没到位”的真实问题。这个我会在第 4 章详细讲。

1.4 性能和查询窗口

明细表一旦上了千万行，直接对全表做 GROUP BY 是不可接受的。时间窗口越大、粒度越细，查询越慢。后面必须考虑索引设计、预聚合、缓存。这些不在一开始规划好，等接口上线后被慢查询拖住，再来补救就很被动。

2. 数据聚合放在哪一层：数据库、服务端还是浏览器

聚合逻辑可以放在三个位置：数据库 SQL、后端代码、前端浏览器。这不是随便选的，选错位置会直接决定接口性能和代码复杂度。

2.1 三个位置的能力边界对比

2.2 我的默认选型：数据库聚合 + 服务端补零

绝大多数情况下，我推荐组合拳：数据库负责粗粒度聚合（GROUP BY 到目标时间粒度），后端服务端负责补零和格式组装。

为什么这样分？原因有三个。第一，数据库的 GROUP BY 是经过十几年优化的，比你在 Python 或 Java 里写 HashMap 累加要快得多。第二，经过聚合后，数据库返回到后端的可能只有几十行，比如一个月 31 条，网络传输和内存开销都极小。第三，补零这个动作放在后端最灵活，因为数据库的日期维表不是每个团队都有，而后端生成一个连续日期数组非常容易。

前端只做一件事：拿到后端已经对齐好的 categories 和 series，直接渲染。这样前端逻辑最少，出问题最好排查。

2.3 什么情况下别在数据库做聚合

• 数据源本身不是 SQL 数据库，而是第三方接口返回的 JSON，或者日志文件；
• 权限过滤逻辑非常复杂，需要先把用户权限范围内的数据 ID 列表查出来再过滤明细；
• 数据已经是聚合结果，后端要做的是把日聚合数组重新采样成周或者月聚合。

遇到这些情况，就把聚合逻辑往后端移动。还有一个折中方案：如果数据量不大（十万行以内），直接在内存里分组也很快，代码可读性反而更好。

2.4 对索引的影响要有预判

SQL 聚合看起来省事，但如果你在 WHERE 条件和 GROUP BY 里对时间字段做函数运算，比如DATE_SUB(created_at, INTERVAL 8 HOUR)，MySQL 就可能放弃索引，导致全表扫描。这个问题不是“选了 SQL 聚合”就一定好，而是要求你懂数据库的执行计划。我会在第 5 章专门讲怎么绕开它。

3. 日周月年的 SQL 分组写法与口径陷阱：时区、ISO 周和排序

下面用订单事件表来举例子。表结构大差不差：

CREATE TABLE trade_event ( id BIGINT PRIMARY KEY, user_id BIGINT, amount DECIMAL(10, 2), status TINYINT, created_at DATETIME NOT NULL, KEY idx_created_at (created_at) );

3.1 日度聚合：DATE 函数与 UTC+8 时区修正

日度聚合最简单的写法是：

SELECT DATE(DATE_SUB(created_at, INTERVAL 8 HOUR)) AS day_key, COUNT(*) AS trade_cnt, SUM(amount) AS trade_amount FROM trade_event WHERE created_at >= ? AND created_at < ? GROUP BY day_key ORDER BY day_key;

这里的INTERVAL 8 HOUR是为了把 UTC 时间偏移到东八区。数据库经常用 UTC 存时间，业务统计却按 UTC+8 的自然日来分组。如果不做这个偏移，东八区凌晨 0 点到 8 点的数据会归到前一天。

需要提醒一句：时区换算最好在写入端或者预处理层解决，不要在每条查询里都做函数运算。查询里对created_at套函数，会让这个字段上的索引失效。如果你的表已经很大，这个查询每次都会全表扫。

如果真的只能在查询时做偏移，也尽量把 WHERE 条件写成“大于等于某个常量边界”，而不是对列做DATE_SUB()。

3.2 周度聚合：先把日期归一化到本周周一

周度聚合在业务需求里最乱。有的需求说“周一到周日为一周”，有的说“周一到周日为一周”其实也分自然周和 ISO 周两种口径。

MySQL 里WEEKDAY()函数返回周一为 0、周日为 6。把任意日期归一到本周周一，最直观的写法是：

SELECT DATE_SUB(DATE(created_at), INTERVAL WEEKDAY(created_at) DAY) AS week_start, COUNT(*) AS trade_cnt, SUM(amount) AS trade_amount FROM trade_event WHERE created_at >= ? AND created_at < ? GROUP BY week_start ORDER BY week_start;

这里DATE(created_at)先截断到当天 0 点，再减掉WEEKDAY()对应的天数，得到的就是这一周周一的日期。用“周起始日”作为分组主键有一个好处：它本身就是一个真实日期，排序不需要额外处理，返回给前端时也容易格式化。比如 2024 年第 25 周的week_start就是2024-06-17。

如果业务明确要求 ISO 周，MySQL 的YEARWEEK()有个 mode 参数：YEARWEEK(created_at, 3)表示 ISO 周，每周从周一开始，跨年周归属到下一年。但只取YEARWEEK()的返回值有一个问题：它返回的是数字年周组合，比如202425，不方便展示和按周起始日计算。所以大部分项目我建议直接用“周起始日”，这个字段能同时满足排序、展示、按周边界查询三个需求。

跨年周是最容易出 bug 的地方。2023 年 1 月 1 日是个周日，按 ISO 周它属于 2022 年的第 52 周。如果不注意，很多统计会把那一天的订单算进 2023 年第一周，导致跨年那一周的数据整体偏掉。

3.3 月度聚合：DATE_FORMAT 与字符串排序陷阱

月度聚合最常用的是DATE_FORMAT：

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month_key, COUNT(*) AS trade_cnt, SUM(amount) AS trade_amount FROM trade_event WHERE created_at >= ? AND created_at < ? GROUP BY month_key ORDER BY month_key;

'%Y-%m'返回的是2024-01、2024-02这种字符串，字典序和自然时间顺序一致，所以ORDER BY month_key可以直接用。但如果你图省事写成'%m-%Y'，返回01-2024，字典序就是乱的，排序会变成 01、02、10、11 这种，图表的横轴就会错乱。

另一个常见问题是误用SUBSTRING(created_at, 1, 7)来做月份截取。如果created_at是 DATETIME 还好，但如果是字符串且长度格式不统一，这种写法会莫名其妙多出几条脏数据。老老实实用DATE_FORMAT或DATE_TRUNC。

月度聚合的边界问题不严重，但等你好不容易把 12 个月的数据查出来，回头一看 3 月没有数据，前端折线图中间就空了一段，所以补零逻辑必须对接上。

3.4 年度聚合：YEAR 与动态窗口

年度聚合本身很简单：

SELECT YEAR(created_at) AS year_key, COUNT(*) AS trade_cnt, SUM(amount) AS trade_amount FROM trade_event WHERE created_at >= ? AND created_at < ? GROUP BY year_key ORDER BY year_key;

但实际使用中，没人会真的把从建库到现在的每一年都画出来，通常要一个“近 5 年”或“近 10 年”的窗口。窗口写法有个小技巧，避免对列做函数运算：

WHERE created_at >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 4 YEAR), '%Y-01-01')

右侧是一个常量日期，MySQL 能用上索引。如果你写成YEAR(created_at) >= YEAR(NOW()) - 4，右侧勉强还是常量，但对左边列套了YEAR()，索引直接失效，表稍微大一点就会慢到让人抓狂。

3.5 四档粒度是拆四个方法，还是拼一个动态 SQL

很多同事喜欢把日周月年写成同一个方法，用 if 或 switch 换 GROUP BY 表达式。我的实际感受是：前期看着省代码，后期天花板很低，因为每个粒度的补零、格式化、时间窗口都不同，把这些塞进一个方法里，条件分支多得维护不住。

我更推荐拆成四个方法，每个方法职责单一：入参是时间范围，出参是time_key + 聚合值的列表。第 6 章我会给一个完整的骨架代码。

4. 喂给 ECharts 的数据该长什么样：补零、时间轴与提示框格式化

SQL 聚合只完成了后半段的一半。数据库返回的是“有数据的那些时间点”，图表需要的却是“完整时间轴上每个点的值”。这就到了补零环节。

4.1 接口返回结构设计：让前端少写逻辑

我的习惯是接口返回一个“已经对齐好”的结构，前端几乎不做数据预处理：

{ "unit": "month", "start": "2023-08", "end": "2024-08", "categories": ["2023-08", "2023-09", "...", "2024-08"], "series": [ { "name": "订单量", "type": "line", "data": [154, 201, 178, 230, "..."] } ] }

categories是给 xAxis 用的完整时间轴，series[0].data和它一一对应。前端拿到这个结构，直接setOption就可以。关键是“对齐”这件事一定要后端做，因为前端不知道业务规则里的“连续月份从哪到哪”。

4.2 空数据补零：由谁做、怎么做

SQL 里 GROUP BY 之后，某一天没交易，这一行就不存在。折线图直接渲染就会断线。后端补零的逻辑一般分两步：

1. 根据unit、start、end生成完整的时间轴数组；
2. 把数据库聚合结果的 HashMap 灌进去，缺失的 key 填 0。

一个用 Python 实现的示例：

import pandas as pd def build_time_axis(unit, start, end): freq_map = { "day": ("D", "%Y-%m-%d"), "week": ("W-MON", "%Y-%m-%d"), "month": ("M", "%Y-%m"), "year": ("Y", "%Y"), } freq, fmt = freq_map[unit] series = pd.date_range(start, end, freq=freq) return [d.strftime(fmt) for d in series] def fill_zero(rows, unit, start, end): axis = build_time_axis(unit, start, end) mapping = {r["time_key"]: r["value"] for r in rows} return axis, [mapping.get(t, 0) for t in axis]

这里有个细节必须注意：周度聚合的start和end要提前对齐到周一。如果end落在周三，pd.date_range生成的最后一个周一可能比你期望的少一个点。日、月、年同理，分别对齐到 0 点、月初和年初。边界对齐是这类代码最容易被忽略但又最关键的地方。

补零还有一层业务判断：如果当天数据没有到位，是填 0 还是留空？我的建议是接口里加一个missingKeys字段，把“确实没有数据”的键单独返回，前端可以在 tooltip 里标注“当日无数据”，而不是让用户误以为业务爆发为 0。

4.3 ECharts 配置：category 轴、tooltip 与轴标签

既然 categories 已经生成好，xAxis 直接用type: 'category'就行：

const chart = echarts.init(document.getElementById('trendChart')); chart.setOption({ xAxis: { type: 'category', data: categories, axisLabel: { interval: 'auto' } }, yAxis: { type: 'value' }, series: series.map(s => ({ name: s.name, type: s.type, data: s.data, smooth: true })), tooltip: { trigger: 'axis' } });

不过 axis 标签默认会全量显示，月份一多就挤成一片。这时把axisLabel.interval设置成'auto'，或者后端返回 labels 时做抽样，前端就没那么操心。

tooltip 格式化也有讲究。如果 categories 是2024-06-17这种周起始日，业务想看的是“2024-06-17 ~ 2024-06-23”，这个格式在后端拼好会更简单，前端只需：

tooltip: { trigger: 'axis', formatter: function (params) { return params[0].axisValue + '<br/>' + params.map(p => `${p.seriesName}：${p.value}`).join('<br/>'); } }

4.4 注意别让浏览器再做一次时区转换

前端如果拿到的 categories 已经是业务时区下的日期字符串，就不要再用new Date(categories[i]).toLocaleDateString()之类的方式转格式。浏览器会把它当成浏览器本地时间解析，再做一次偏移，可能正好又把东八区的日期错回去一天。格式化统一在进入图表之前完成，图表拿到的就是最终展示值。

5. 数据量上来之后：联合索引、日汇总表与缓存分层

图表接口和普通列表接口不同：它是聚合查询，数据量一大，SQL 一点优化不做就会拖垮数据库。这一章讲三个层面的手段，优先级从高到低排列。

5.1 联合索引：让 GROUP BY 少扫很多行

最基础的优化是索引。假设业务上最常见的过滤条件是“统计某时间段内、某状态下的订单”，查询大致长这样：

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month_key, COUNT(*) AS trade_cnt, SUM(amount) AS trade_amount FROM trade_event WHERE status = 1 AND created_at >= '2024-01-01' AND created_at < '2024-09-01' GROUP BY month_key;

这时候可以考虑联合索引(status, created_at, amount)。MySQL 先用status过滤，再用索引里有序的created_at做范围扫描，最后amount还在索引里，SUM(amount)可以直接从覆盖索引取数，不用回表。

注意属性顺序有讲究。一般来说，等值条件放前面（status），范围条件放后面（created_at），这样索引才能发挥最大能力。

至于COUNT(*)，InnoDB 的二级索引天然携带主键，通常不会做成覆盖索引也能计数，但回表本身有成本，索引里带上 status 之后至少已经减少了大范围。

5.2 日汇总表：把“宝贵的大表”和“普通查询”隔离开

如果每天都有大量明细写入，让日、周、月、年四个粒度的统计每次都直接扫订单明细表，是很浪费的。更稳的做法是加一张日汇总表：

CREATE TABLE trade_event_daily_summary ( day_key DATE NOT NULL, status TINYINT NOT NULL, trade_cnt INT NOT NULL, trade_amount DECIMAL(14, 2) NOT NULL, PRIMARY KEY (day_key, status) );

每天凌晨跑一个定时任务，把前一天的明细按“天 + 状态”聚合进这张表。之后：

• 日度图表直接查日汇总表；
• 月度图表在日汇总表上再按DATE_FORMAT(day_key, '%Y-%m')聚合；
• 周度图表按“周起始日”再聚合；
• 年度图表按YEAR(day_key)再聚合。

日汇总表一年最多 365 行，和上亿行的明细表相比，查询成本完全不是一个量级。这也是“数据仓库分层聚合”思路的轻量版。

要支持实时数据的话，可以“历史日汇总表 + 当天明细”两部分结果合在一起。历史部分查日汇总表，当天部分单独查明细，再用代码 merge。

5.3 缓存：别让重复请求重复打库

图表口径相对稳定，非常适合加缓存。通常我用 Redis，缓存 key 直接包含粒度、时间范围、过滤维度：

stats:trade_trend:{unit}:{start}:{end}:{status}

value 就是组装好的categories + seriesJSON。失效策略很简单：定时任务跑完日汇总后主动刷新相关 key；如果担心任务失败，再加一个 5 到 10 分钟的过期兜底。

这里我提醒一句：缓存只解决“重复查询不重复打库”的问题，它不能掩盖低质量 SQL。没有索引和预聚合的情况下，就算缓存了前几个请求，冷启动依然会拖垮数据库。先把 SQL 优化好，再谈缓存。

5.4 EXPLAIN 是最后的体检官

每次写完聚合 SQL，我都会习惯性看一下执行计划：

EXPLAIN SELECT DATE_FORMAT(created_at, '%Y-%m') AS month_key, COUNT(*), SUM(amount) FROM trade_event WHERE status = 1 AND created_at >= '2024-01-01' AND created_at < '2024-09-01' GROUP BY month_key;

重点看type列。理想情况下应该是range或ref，如果出现ALL，说明在扫全表。另一个训练是看Extra里有不有Using index，如果有说明走了覆盖索引，性能会更理想。

6. 最小可用落地骨架：从请求参数到前端粒度切换

前面讲了一堆原理，最后给一个可以直接抄作业的骨架。它不算优雅，但足够稳。

6.1 请求参数设计

接口可以是：

GET /api/stats/trade-trend?unit=month&rangeType=last_12m

unit取值：day、week、month、year。rangeType可以用last_7d、last_30d、last_12m、last_5y。后端拿到后会解析成具体的start和end。

边界校验必须做三件事：

• unit不在白名单，直接 400；
• start > end，直接 400；
• 日度粒度最大查询范围限制在 90 天，月度和年度可以放宽到 10 年。否则用户一次拉几十年的数据，接口和浏览器都受罪。

6.2 后端处理顺序

1. 解析unit和rangeType，得到start、end；
2. 生成完整时间轴，供补零用；
3. 查数据库聚合结果（或走缓存）；
4. 把聚合结果灌进时间轴映射，缺失补 0；
5. 组装categories和series返回。

核心代码骨架：

@app.get("/api/stats/trade-trend") def trade_trend(): unit = request.args.get("unit", "month") range_type = request.args.get("rangeType", "last_12m") if unit not in VALID_UNITS: return jsonify({"error": "unsupported unit"}), 400 start, end = resolve_range(range_type) rows = query_aggregate_from_db(unit, start, end) axis, data = fill_zero(rows, unit, start, end) return jsonify({ "unit": unit, "start": start, "end": end, "categories": axis, "series": [{ "name": "订单量", "type": "line", "data": data }] })

query_aggregate_from_db内部就是第 3 章说的那几个 SQL，按unit分发到四个方法。

6.3 前端粒度切换：别忘记 notMerge

前端放四个按钮：日、周、月、年。点击后重新请求接口，然后更新图表：

async function switchUnit(unit) { chart.showLoading(); const data = await fetch('/api/stats/trade-trend?unit=' + unit).then(r => r.json()); chart.setOption({ xAxis: { type: 'category', data: data.categories }, yAxis: { type: 'value' }, series: data.series.map(s => ({ name: s.name, type: 'line', data: s.data, smooth: true })) }, true); chart.hideLoading(); }

这里有个细节必须说明：setOption的第二个参数传true，表示notMerge。如果不传，旧的时间轴和 series 会残留。最常见的现象就是想从月度切到日度时，月度那 12 个点的数据还叠在日度图上，图形完全错位。

6.4 一个最小演示数据样例

假设查询月度数据，数据库只返回了 8 月到 12 月有数据：

time_key trade_cnt 2024-08 182 2024-09 220 2024-10 201 2024-11 254 2024-12 0 -- 注意这一行是补零后的

后端补零之后，输出的 categories 和 series 就是对齐的两个数组：

categories: [2024-08, 2024-09, 2024-10, 2024-11, 2024-12] series: [182, 220, 201, 254, 0]

前端拿到的就是一份“随时能画”的数据。

6.5 多指标扩展：order_cnt 和 GMV 一起画

需求通常会从“订单量”扩展成“订单量 + 成交金额”，甚至再加一个同比。后端做法很简单：SQL 聚合里多算一个SUM(amount)，然后把 series 数组扩展成两条：

"series": [ { "name": "订单量", "type": "line", "data": [182, 220, 201, 254, 0] }, { "name": "成交金额", "type": "bar", "data": [12000, 15000, 13800, 17000, 0] } ]

前端不需要额外处理，只需要在渲染时给 series 里的每条数据指定 type 和 name。图表一下子从单一指标变成了组合视图，而且后端逻辑没有变结构。

做了几个这样的统计接口之后，我最大的体会是：先定义口径，再写 SQL，最后才打开 ECharts 文档。多花 10 分钟把日、周、月、年的口径写清楚，能省掉后面大量“你这个统计不对”的扯皮。最后分享一个我自己常用的习惯：不管在哪个环节做补零，我都会在接口响应里带上start、end和unit字段，方便前端排查数据不对齐的问题。这个字段看着多余，但图表一旦出现“少一个点”或“多一个点”的情况，它能帮你快速定位问题是出在时间轴生成、数据库查询还是前端渲染哪一段。