接手这个项目之前我其实已经有一段时间没碰电商类的数据分析了，大部分精力都花在报表工具和Python处理上。但在一次内部讨论里，业务方提了一个很具体的问题：能不能在下单高峰期快速看出一小时内的订单趋势和商品集中度？我意识到，与其把所有数据抽出来再算，不如直接在MySQL里把订单相关表结构理顺，用SQL把核心指标一次算清。这篇东西就是我那次实战的完整复盘，内容包括表怎么建、指标口径怎么定、SQL怎么写才不踩坑，以及最后怎么把结果真正用到业务决策里。
如果你的工作内容恰好也是看订单、看销售额、看用户复购，而且手头数据库就是MySQL，那你现在看这篇正合适。这不是教科书式的数据仓库方案，也不是某个中间件的高级教程，就是一个人在面对一堆订单表时，从整理数据到产出分析结论的完整路径。
1. 从一张订单明细表开始：先把数据模型理顺
1.1 电商订单数据的真实形态
很多人拿到订单数据，第一反应就是“不就是一张大宽表嘛”。还真不是。电商订单在业务系统里通常是拆开的，主订单表、明细表、支付表、售后表各管一摊，因为它们形态不一样：一张订单可以有多个商品，可以分多次支付，还可能有部分退款。如果硬要把所有信息压成一张表，字段会爆炸，而且一对多关系会让统计结果翻倍。
我当时接到的数据模型大概是这样的（字段做了简化，避免和线上真实表完全一样）：
orders：主订单表，一个订单一条记录，包含订单号、用户ID、下单时间、订单状态、订单金额、实付金额。order_items：订单明细表，一个订单几条商品记录，包含商品ID、数量、单价、小计金额、退款标记。payments：支付流水表，一个订单可能对应一到多次支付记录。users：用户表，记录注册时间、渠道来源。
这个结构其实很典型。主订单表是“头”，明细表是“行”；头和行靠订单号关联。你要是只用主订单表统计商品数量，或者只用明细表统计客单价，口径一定出问题。先把“一行代表什么”想清楚，后面所有SQL才靠谱。
1.2 状态字段决定了你不能随便SUM
订单状态是整个分析里最容易被忽视的字段。同样是订单金额，待付款状态、已支付状态、已取消状态它们的含义完全不同。我当时业务里的状态大致是：
- 已下单未支付：订单生成了，钱没到账
- 已支付：用户完成付款，订单进入履约
- 已发货 / 已完成：订单正常流转
- 已取消：用户主动取消或超时未付系统自动取消
- 退款中 / 已退款：支付后发生售后
你算“销售额”的时候，如果直接把支付成功的订单和已取消的订单加在一起，最后给老板的报表就会虚高。还有些订单支付后又退款的，如果只算支付金额，高估了真实流水；如果完全剔除，又低估了支付转化环节的真实规模。所以第一步不是写SQL，而是和业务方定清楚每个指标的口径。
1.3 建分析库，别直接压在业务库上
这是我在这个项目里做的第一个关键决定。当时有同事说，直接连线上业务库写查询不就行了吗，反正只是跑几个SELECT。我坚决没这么做，理由有两个：
一是业务库的高峰期读压力本来就大，一个没走索引的全表扫描就能拖垮页面响应；二是业务库表结构是给OLTP设计的，很多分析字段没有，就算跑出结果也都是临时拼SQL，每次口径还不一样。
所以我在同一套MySQL实例里单独建了一个analysis库，通过定时任务把业务库的订单、明细、用户数据同步进来。同步的规模不大，每天几十万级订单量，一个最朴素的INSERT SELECT就能完成增量更新，根本用不上复杂组件。做数据分析之前先把数据中心化，后面所有报表都从这一个地方出，口径统一，慢查询也不会影响线上。
2. 把核心指标写进SQL：GMV、客单价、复购率的一次算清
2.1 指标口径先定出来，再谈SQL
我见过太多“SQL写对了但业务不认”的情况，问题不出在语法，出在口径。你在写代码前必须把每个指标的定义钉死，最好用一张表记下来。我之前整理的核心指标口径是这样：
| 指标 | 推荐口径 | 说明 |
|---|---|---|
| GMV | 所有非取消订单的订单金额之和 | 体现下单规模，包含未支付订单 |
| 销售额 | 已支付订单的实付金额，剔除退款 | 真实到账口径 |
| 订单量 | 有支付记录的订单数，去重 | 不要重复计算拆单 |
| 客单价 | 销售额 ÷ 支付订单数 | 看清楚“每个订单贡献多少钱” |
| 支付转化率 | 已支付订单数 ÷ 已下单订单数 | 衡量下单后有没有完成付款 |
| 复购率 | 某周期内购买≥2次的用户 ÷ 购买用户 | 衡量用户忠诚度 |
这里最容易吵起来的点就是GMV到底含不含退款。不同行业确实有不同习惯，大促平台经常把GMV定义为“下单金额”以便对外宣传，但财务看的是销售额。我的原则是，在报表里两个都保留，分别列明口径，别含含糊糊只报一个数。
2.2 用一段SQL把核心指标全跑出来
口径清楚了，SQL其实不难。关键是把多个指标合并到一条查询里，避免反复扫同一张表。下面这个是我项目里按天统计的完整示例，涵盖了订单量、用户数、销售额、客单价。
SELECT DATE(paid_at) AS pay_date, COUNT(DISTINCT order_id) AS pay_order_cnt, COUNT(DISTINCT user_id) AS pay_user_cnt, ROUND(SUM(pay_amount), 2) AS sales_amount, ROUND(SUM(pay_amount) / COUNT(DISTINCT order_id), 2) AS avg_order_amount, ROUND(SUM(pay_amount) / COUNT(DISTINCT user_id), 2) AS avg_user_amount FROM orders WHERE status IN ('PAID', 'SHIPPED', 'COMPLETED') AND paid_at >= '2024-01-01' AND paid_at < '2024-02-01' GROUP BY DATE(paid_at) ORDER BY pay_date;几点值得注意：
第一，这里统计的是“已支付订单”状态集合，我用IN包含多个状态天然排除了未支付、已取消、退款中的单子。缺点是已全额退款的订单还在里面，所以销售额会稍高。如果严格一点，我通常还在明细层把退款标记为is_refunded=1的商品剔除，这个到第三节再讲。
第二，COUNT(DISTINCT order_id)比COUNT(*)慢一点，但安全得多。因为主订单表本身记录粒度是订单一行，理论上不用DISTINCT；但加上去能防止多表JOIN以后产生重复记录导致统计翻倍。
第三，为什么用SUM(pay_amount)而不是SUM(total_amount)？因为优惠、满减、运费这些都已经体现在实付金额里了。total_amount是订单原价总和，统计“销售额”应该回归用户实际掏了多少钱。
2.3 复购率可以这样算
复购率这个指标，看起来只是“买了不止一次”的用户占比，但只要你把周期定错了，结论就完全不一样。我按自然月统计，把某月有支付记录的用户作为分母，把“在这个月之前也买过”的用户作为分子。
WITH user_buy AS ( SELECT user_id, DATE(paid_at) AS pay_date, COUNT(DISTINCT order_id) AS buy_cnt FROM orders WHERE status IN ('PAID', 'SHIPPED', 'COMPLETED') AND paid_at >= '2024-01-01' AND paid_at < '2024-02-01' GROUP BY user_id, DATE(paid_at) ), user_month AS ( SELECT user_id, SUM(buy_cnt) AS month_buy_cnt FROM user_buy GROUP BY user_id ) SELECT COUNT(*) AS total_buy_user, SUM(CASE WHEN month_buy_cnt >= 2 THEN 1 ELSE 0 END) AS multi_buy_user, ROUND(SUM(CASE WHEN month_buy_cnt >= 2 THEN 1 ELSE 0 END) / COUNT(*), 4) AS repurchase_rate FROM user_month;这段SQL的原理不复杂：先用子查询把每个用户每天的购买次数算出来，再把一个月的购买次数汇总，最后用CASE WHEN判断该用户是否属于复购用户。跑了三个月的数据，稳定在8%到12%之间，不同品类差异很大。
做这个分析时我还发现一个现象：只看“次月复购率”会漏掉一些周期较长的用户。比如买电器的人可能三个月后才再买一次，月复购率就显得很低。后来我在报表里加了一个90天滚动窗口，把统计周期拉长，业务反馈才觉得数据“对了”。
3. 多维分析的三板斧：时间、用户、商品
3.1 时间维度：下订单高峰和你想的不一样
订单分析最开始看的永远是时间分布。大家习惯关注日维度销售额，但我建议你先看看小时维度，因为这里面藏着排班和推广时间的决策依据。
我当时写了一个小时维度的分组查询，不是直接按HOUR(paid_at)聚合，而是先把小时划分成时段，因为业务方关心的是“晚上”“下午”这种语义化的时段，而不是00点到23点的数字。
SELECT CASE WHEN HOUR(paid_at) BETWEEN 6 AND 10 THEN '早间06-10' WHEN HOUR(paid_at) BETWEEN 11 AND 14 THEN '午间11-14' WHEN HOUR(paid_at) BETWEEN 15 AND 18 THEN '午后15-18' WHEN HOUR(paid_at) BETWEEN 19 AND 21 THEN '晚间19-21' ELSE '深夜22-05' END AS time_bucket, COUNT(DISTINCT order_id) AS order_cnt, ROUND(SUM(pay_amount), 2) AS sales_amount FROM orders WHERE paid_at >= '2024-01-01' AND paid_at < '2024-04-01' GROUP BY time_bucket ORDER BY sales_amount DESC;这个查询结果当时给了我一个不小的意外：晚上19点到21点的订单量占比最高，但客单价最高的却是午后15点到18点。仔细想，这其实很正常，白天上班族摸鱼时买大件，晚上回家顺手买日用。业务方看了这个分布后，调整了客服排班和活动推送时间，转化确实有一点提升。
这里补充一个技术细节：HOUR(paid_at)这种写法在数据量小时没感觉，数据量大时会导致索引失效，因为对字段套了函数以后B+树没法走常规范围查询。如果你统计的是最近几天的数据，可以先缩小paid_at的范围再套函数，性能会好很多。本节只分析一个月内的数据，性能还好，后面第四部分我专门踩过这个坑。
3.2 用户维度：新客老客往往在用两套逻辑
用户维度的分析，最基础但最有洞察力的切法是把用户分成新客和老客。新客看“首单体验”，老客看“复购频率”。
我第一次做用户分层时用的是RFM思路的简化版，不搞复杂打分，只用SQL判断：
- 新客：首次支付时间在统计周期内的用户
- 活跃老客：周期内有支付，且首次支付在统计周期之前
- 流失召回：周期内没有支付，但历史上买过的用户（这个需要用历史全量数据，所以我把同步周期拉长到了一整年）
SELECT CASE WHEN first_pay_date >= '2024-01-01' AND last_pay_date >= '2024-01-01' THEN '新客' WHEN first_pay_date < '2024-01-01' AND last_pay_date >= '2024-01-01' THEN '活跃老客' WHEN first_pay_date < '2024-01-01' AND last_pay_date < '2024-01-01' THEN '沉睡召回' ELSE '其他' END AS user_segment, COUNT(DISTINCT user_id) AS user_cnt, ROUND(SUM(pay_amount), 2) AS sales_amount FROM user_payment_summary GROUP BY user_segment;这里的user_payment_summary是我在分析库里预先建好的汇总表，记录每个用户的首次支付时间、最近支付时间、总支付金额、总支付次数。与其在查询里反复扫描订单表，不如一次算好这张中间表，后续分析都在它上面跑。这也是我对所有“用户标签类”分析的一致做法：能预先聚合就别临时扫明细表。
分析完这个分层以后，我发现新客的客单价其实比老客高不少。原因是平台首单优惠券力度大，用户首单往往凑单买得多；反而老客复购时趋于买低客单的消耗品。这个发现直接影响到后面运营策略，第四部分我还会提到。
3.3 商品维度：到底是谁在贡献销售额
商品维度分析最忌讳的是直接按product_name分组，因为同样的商品在不同链接下描述不一样，很容易拆得稀碎。正确做法是先按品类聚合，看看品类结构是不是健康；再掏商品明细，找出高贡献、高退货问题的商品。
品类聚合的SQL很直接，但要看一个关键指标：品类销售占比。超过30%的单品类集中度意味着风险很高，一旦这个品类出问题，整体销售额就会被拖垮。
SELECT p.category, COUNT(DISTINCT oi.order_id) AS sale_orders, ROUND(SUM(oi.subtotal * (1 - oi.refund_flag)), 2) AS net_sales, ROUND(SUM(oi.subtotal * (1 - oi.refund_flag)) / SUM(SUM(oi.subtotal * (1 - oi.refund_flag))) OVER (), 4) AS category_share FROM order_items oi JOIN products p ON oi.product_id = p.product_id JOIN orders o ON oi.order_id = o.order_id WHERE o.status IN ('PAID', 'SHIPPED', 'COMPLETED') AND o.paid_at >= '2024-01-01' AND o.paid_at < '2024-02-01' GROUP BY p.category;重点解释一下refund_flag这个字段。我在明细表里加了一个0/1标记，0代表该商品没有发生退款，1代表发生了退款。这样在算净销售额时，我可以用subtotal * (1 - refund_flag)把退款商品的金额剔除，而不是等到订单状态变成“已退款”才处理。这个字段在做增量同步时更新，逻辑是同一订单内商品只要售后单标记为退款完成，就把该明细行的flag置为1。
整体销售看品类，细节排查看具体商品。同一个品类下，少数爆品贡献了大部分销售额；而退款率排名靠前的商品，几乎都集中在外观尺寸和实物描述有偏差的品类。这就是商品维度分析要干的事：把贡献者找出来，把风险点标记出来。
4. 实测踩过的坑：金额精度、重复行和索引黑洞
4.1 用FLOAT存金额，对账就是自找麻烦
这是我在项目初期犯过的错误，现在想起来都觉得不该。当时表结构是从老系统沿用下来的，金额字段是FLOAT类型，我一开始没注意，随手就用了。结果某天做日对账，线上支付系统拉出来的金额和我的SQL跑出来的金额差了0.13元。
差得不多，但0.13元足以让财务质疑整个数据链路的可信度。我当时排查了半天，最后发现原因是FLOAT是浮点数，二进制存储无法精确表示很多十进制小数。比如0.1在FLOAT里是一个近似值，大量相加以后误差就会累积出来。这跟你在纸上手算小数不一样，计算机的浮点运算天生存在精度损失。
解决方式很简单：金额字段一律使用DECIMAL(10,2)。MySQL的DECIMAL是定点数，不会出现浮点误差。我后来把订单表、明细表、支付表的所有金额字段都改成了DECIMAL，再跑一次对账，数字完全对上了。顺手把分析库里金额相关的中间汇总字段也都改成了DECIMAL。
如果你的系统里已经有FLOAT存金额的历史数据，也别慌，在转换时先用ROUND清洗一次，然后再修改字段类型。直接在查询里ROUND(SUM(amount), 2)这种办法只是治标，数据进库的那一刻就该用DECIMAL。
4.2 JOIN明细表导致的数据翻倍
我做过一次日销售额统计，SQL写得很顺手：主订单表JOIN订单明细表，再JOIN商品表，SUM金额。出来的结果比前一天高了一截，一开始我还以为是“大促预热”效果，后来检查业务数据才知道，是JOIN产生了重复行。
具体原因是，那一批订单里有些商品被拆成了多个包裹，一个订单在明细表里其实有两条记录：商品A和商品B。我在JOIN订单表时，没注意主订单表的金额字段和明细表的金额字段被同时SUM了，等于一份钱加了两遍。即使用了DISTINCT也不行，因为两条明细记录本身不重复。
解决办法是明确表的粒度和统计范围。如果指标是订单级，就只用主订单表；如果指标是商品级，就只用明细表。非要把两张表JOIN在一起统计，就只能保证金额字段来自同一张表，不能混用。上面第三节的语句里我统一选用了明细表的subtotal，这个设计是有意的。
我还加了一个简单的验收规则：每次跑完报表，先和前一天的数据做“波动率检查”，如果日环比超过30%而业务上又没有大促，基本可以判定SQL有问题。人工查数据，总得有个自动校准的手段。
4.3 字段套函数，索引不干活
我最初写“某天订单”的查询是这样：
SELECT COUNT(*) FROM orders WHERE DATE(paid_at) = '2024-03-18';刚跑的时候数据量小，没问题。同步了半年数据之后，这条SQL慢得离谱，执行计划显示全表扫描。原因很常见：在paid_at这个时间字段外面套了DATE()函数，MySQL没办法高效使用时间索引，它不得不把每一行的时间值算一遍再做比较。
正确的写法是用范围条件：
SELECT COUNT(*) FROM orders WHERE paid_at >= '2024-03-18 00:00:00' AND paid_at < '2024-03-19 00:00:00';这个查询可以利用B+树索引直接定位区间，执行时间从几百毫秒降到几毫秒。如果你的数据量到了几百万行，这个差别肉眼可见。
我当时顺手把所有统计查询里涉及时间的写法都改了一遍。凡是WHERE DATE(xxx) = ...、WHERE YEAR(xxx) = ...这些函数包裹字段的写法，全部改成开区间比较。后来MySQL 8.0虽然也能做函数索引优化，但养成习惯更好——你没法保证团队所有成员都用最新版本。
4.4 NULL和时区，两个容易被忽略的“影子对手”
订单表里的paid_at字段，在用户下单未支付时是NULL。如果你直接写WHERE paid_at IS NOT NULL，那NULL的被排除了；但如果你写WHERE paid_at > '2024-01-01'，NULL也会被排除，因为NULL和任何值比较结果都是未知。逻辑上没错，但如果你后续拿NULL去和业务字段做聚合，比如SUM(CASE WHEN paid_at < ... THEN ... END)，NULL行不会进任何分支，很可能会低估数据规模。
我的处理方式是在转换时就把NULL单独标记出来，比如加一个pay_status字段，把“未支付”“已支付”“已退款”这些业务状态显式写清楚，而不是隐式依赖时间字段的非NULL判断。
时区问题则更隐蔽。当时服务器数据库时区是UTC，业务方看的报表时间却是东八区。我用DATE(paid_at)分组时发现，每天的数据对不上运营后台，凌晨的订单被算到了前一天。后来我把数据库连接参数里的time_zone显式设置为+08:00，同时在同步任务里统一用业务时间写入分析库，报表数字才和线上后台一致了。
5. 从SQL结果到业务动作：分析师和取数工具之间差什么
5.1 高频问题：算完这些数，然后呢
很多同行写完SQL就以为交差了，但业务方真正需要的不是一堆数字，而是从数字里读出来的判断。我做这个项目时，刚开始也只要“把订单量和销售额统计出来”，后来发现老板真正关心的永远是三个问题：哪里涨了，哪里跌了，接下来怎么办。
举个例子。小时维度分析跑完以后，数据显示晚间19点到21点订单量最高，但客服值班人数却是白天最多。这就是一个可以直接落地的业务动作：把客服排班向晚间倾斜，或者在晚间加大促销弹窗的投放。SQL本身没有结论，但当你把“订单高峰”和“客服人力”放在一起对比，结论自然就出来了。
我的习惯是，每次数据报告里至少带一个“行动建议”，不管是人货场哪个维度。哪怕是“建议关注某品类退款率上升”，也比只抛一张表强。
5.2 从订单明细数据倒推“支付转化漏斗”
订单数据虽然不像埋点日志那样有完整的浏览行为，但你完全可以从状态字段倒推一个简化版漏斗：下单未支付、支付成功、支付后发货、完成、退款。每个状态之间的转化率，就是一张非常清晰的过程表。
用SQL统计状态分布非常简单：
SELECT status, COUNT(*) AS order_cnt, ROUND(COUNT(*) / SUM(COUNT(*)) OVER (), 4) AS status_pct FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01' GROUP BY status ORDER BY order_cnt DESC;当时我发现的一个问题是：下单未支付的比例接近35%，而且这个比例在移动端明显高于PC端。一查才明白，移动端跳转支付的成功率受支付环境、App调用影响很大，很多用户下单以后没有完成支付就退出了。
我们后续在支付唤起环节加了优化，没过多久未支付率就降了几个百分点。一个简单的状态分布SQL，带来的是实际收入的改善。SQL写得再漂亮，指标算得再精确，如果落不到这一步，始终只是“取数”。
5.3 给指标体系做个轻量看板
做完这些分析之后，我发现一个问题：每次业务方要数据都得现写SQL，而且口径还不统一。干脆我用视图（VIEW）把最核心的指标固化下来，再做一张报表页把数据呈现出来。
我在分析库里建了几个视图，比如：
v_daily_business：按天统计订单量、销售额、客单价、退款金额v_user_rfm：用户维度的首购、最近购买、购买频次v_product_analysis：商品维度的销量、销售额、退款率
视图的好处是业务方直接SELECT * FROM v_daily_business就能取数，不需要关心底层的复杂SQL和JOIN逻辑。缺点是查询效率一般，但我这个量级完全够用。如果哪天真到了千万级数据，再换成预聚合的汇总表也不迟。
提示：视图解决的是“口径统一”的问题，不是“性能优化”的问题。数据量大了以后，物化表格或者定时任务生成汇总表才是正路。
5.4 后面可以往哪里扩展
这个项目做完以后，我留下的是一个可持续迭代的分析底座：一个独立分析库、一套口径清晰的核心表、一组可复用的SQL查询。后续如果要接更完整的用户行为，可以把埋点日志的浏览、加购、收藏数据也同步进来，就能把漏斗从“下单后”扩展到“浏览到下单”。
如果还想做预测，也可以把每天的订单数据导出后用Python建模，数据库负责提供特征。但预警别上来就上机器学习，很多时候用SQL算一个7天滚动均值，再对比当日值，波动超过阈值就预警，简单有效，而且业务方也好懂。
写在最后
这个项目让我印象最深的不是某个SQL技巧，而是“对口径负责”这件事。数据算错一个位数，后面所有决策都可能被带偏；字段类型选错，对账时就得白熬夜。分析师的真正价值不在于会写多少函数，而在于能把一个模糊的业务问题翻译成精确的查询条件，再把结果翻译成人话告诉业务方。
如果你也要在MySQL里做订单分析，我的实操建议就三条：第一，先定口径再写SQL；第二，金额用DECIMAL，时间用范围查询，多表JOIN时看好粒度；第三，永远带着业务问题去跑数。那几张表结构不复杂，真正花时间的，是把业务逻辑理顺的过程。
