1. 项目概述
"苍穹外卖日记"系列记录了外卖平台后端开发的全过程，Day11聚焦于四大核心数据统计模块的开发实战。作为外卖系统的"数据驾驶舱"，这些统计功能直接影响运营决策的精准度。我将分享从需求分析到SQL优化的完整实现路径，以及如何避免常见的数据统计陷阱。
2. 营业额统计实现
2.1 需求拆解与技术选型
营业额统计需要支持多维度查询：
- 按日/周/月/自定义时段汇总
- 区分支付方式（微信/支付宝）
- 包含退款金额扣除逻辑
采用方案：
SELECT DATE_FORMAT(order_time,'%Y-%m-%d') AS date, SUM(CASE WHEN payment_method=1 THEN amount ELSE 0 END) AS wechat_amount, SUM(CASE WHEN payment_method=2 THEN amount ELSE 0 END) AS alipay_amount, SUM(amount) - SUM(IFNULL(refund_amount,0)) AS net_amount FROM orders WHERE order_time BETWEEN ? AND ? GROUP BY date关键点：使用CASE WHEN实现支付方式分类统计，IFNULL处理可能为空的退款字段
2.2 动态时间范围处理
前端传参格式处理示例（Java）：
public Date[] parseTimeRange(String timeRange) { String[] parts = timeRange.split(" - "); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); return new Date[]{ sdf.parse(parts[0]), new Date(sdf.parse(parts[1]).getTime() + 86400000) // 包含结束日 }; }2.3 性能优化实践
索引策略：
- 联合索引
(order_time, payment_method) - 单独索引
refund_amount
- 联合索引
缓存方案：
@Cacheable(value = "turnover", key = "#timeRange.concat('-').concat(#shopId)") public TurnoverVO queryTurnover(String timeRange, Long shopId) { // 数据库查询逻辑 }
3. 用户统计模块开发
3.1 用户增长趋势分析
核心指标：
- 新增用户数
- 活跃用户数（有下单行为）
- 留存率（次日/7日/30日）
留存率计算SQL：
SELECT COUNT(DISTINCT day1.user_id) AS day1_users, COUNT(DISTINCT day2.user_id) AS day2_users, CONCAT(ROUND(COUNT(DISTINCT day2.user_id)/COUNT(DISTINCT day1.user_id)*100,2),'%') AS retention_rate FROM (SELECT user_id FROM orders WHERE DATE(order_time)=?) day1 LEFT JOIN (SELECT user_id FROM orders WHERE DATE(order_time)=DATE_ADD(?, INTERVAL 1 DAY)) day2 ON day1.user_id=day2.user_id3.2 用户画像补充
通过JOIN扩展用户属性：
SELECT u.gender, u.age_range, COUNT(DISTINCT o.user_id) AS user_count, AVG(o.amount) AS avg_order_amount FROM orders o JOIN users u ON o.user_id=u.id WHERE o.order_time BETWEEN ? AND ? GROUP BY u.gender, u.age_range4. 订单统计深度优化
4.1 多维度订单分析
订单状态流转统计：
public OrderStatsVO getOrderStats(LocalDate begin, LocalDate end) { return orderMapper.selectStats(begin, end); } // MyBatis映射 <select id="selectStats" resultType="OrderStatsVO"> SELECT COUNT(*) AS totalOrders, SUM(amount) AS totalAmount, COUNT(DISTINCT user_id) AS validUsers, SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS completedOrders, SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) AS cancelledOrders FROM orders WHERE DATE(order_time) BETWEEN #{begin} AND #{end} </select>4.2 实时统计方案对比
| 方案 | 响应时间 | 数据延迟 | 实现复杂度 |
|---|---|---|---|
| 直接查询 | 1-3s | 无 | 低 |
| 定时任务 | 200ms | 5分钟 | 中 |
| 消息队列 | 150ms | <1s | 高 |
推荐中小平台采用方案二：
@Scheduled(cron = "0 */5 * * * ?") public void refreshOrderStats() { // 查询最新数据 OrderStats stats = calculateRealTimeStats(); // 更新Redis redisTemplate.opsForValue().set("order:stats", stats); }5. 销量TOP10实现方案
5.1 聚合查询优化
基础实现：
SELECT item_id, item_name, SUM(quantity) AS total_sales FROM order_detail WHERE order_id IN ( SELECT id FROM orders WHERE order_time BETWEEN ? AND ? ) GROUP BY item_id, item_name ORDER BY total_sales DESC LIMIT 105.2 分库分表处理
当订单数据量超过500万时，采用ShardingSphere分片策略：
spring: shardingsphere: sharding: tables: orders: actual-data-nodes: ds$->{0..1}.orders_$->{0..15} table-strategy: inline: sharding-column: order_time algorithm-expression: orders_$->{order_time.getMonthValue() % 2}5.3 缓存击穿防护
采用双重检查锁防止热点Key问题：
public List<HotItemVO> getTop10Items(LocalDate date) { String cacheKey = "top10:" + date.toString(); List<HotItemVO> cached = redisTemplate.opsForValue().get(cacheKey); if (cached != null) return cached; synchronized (this) { cached = redisTemplate.opsForValue().get(cacheKey); if (cached == null) { cached = queryFromDB(date); redisTemplate.opsForValue().set(cacheKey, cached, 1, TimeUnit.HOURS); } return cached; } }6. 数据可视化实践
6.1 ECharts配置示例
营业额趋势图配置：
option = { tooltip: { trigger: 'axis' }, legend: { data: ['微信支付', '支付宝', '净收入'] }, xAxis: { type: 'category', data: dates }, yAxis: { type: 'value' }, series: [ { name: '微信支付', type: 'line', data: wechatData }, { name: '支付宝', type: 'line', data: alipayData }, { name: '净收入', type: 'bar', data: netData } ] }6.2 大屏适配技巧
- 响应式布局方案：
.chart-container { width: 100%; height: 400px; min-width: 300px; } @media (max-width: 768px) { .chart-container { height: 300px; } }- 数据自动刷新：
setInterval(() => { fetch('/api/stats/latest').then(res => { chart.setOption(updateOption(res.data)); }); }, 30000); // 30秒刷新7. 生产环境问题排查
7.1 慢查询日志分析
典型问题案例：
# Time: 2023-08-15T11:45:03.123456Z # Query_time: 12.345678 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 2000000 SET timestamp=1692099903; SELECT item_id, SUM(quantity) FROM order_detail GROUP BY item_id ORDER BY SUM(quantity) DESC LIMIT 10;优化方案：
- 为order_detail表添加复合索引
(item_id, quantity) - 使用物化视图预计算
7.2 数据一致性校验
定时核对脚本：
def check_stats_consistency(): db_total = query_db("SELECT SUM(amount) FROM orders") redis_total = get_redis("turnover:total") if abs(db_total - redis_total) > 100: # 允许100元误差 alert_admin("数据不一致！DB:{} Redis:{}".format(db_total, redis_total))8. 扩展思考与优化方向
实时计算方案升级：
- 引入Flink流处理
- 实现基于时间窗口的统计
预测分析：
from statsmodels.tsa.arima.model import ARIMA model = ARIMA(history_data, order=(7,0,1)) model_fit = model.fit() forecast = model_fit.forecast(steps=30) # 预测未来30天AB实验统计：
SELECT experiment_group, COUNT(DISTINCT user_id) AS users, SUM(amount) AS revenue, SUM(amount)/COUNT(DISTINCT user_id) AS arpu FROM orders WHERE experiment_id=123 GROUP BY experiment_group
在完成这些统计模块后，建议建立自动化数据质量监控体系，定期检查指标波动是否在合理范围内。我们团队通过设置同比/环比阈值告警，成功在早期发现了多次数据异常情况。
