-- ========================================
|
-- 测试里程统计优化效果
|
-- 用途：对比优化前后的性能和准确性
|
-- ========================================
|
|
-- 1. 查看某个车辆某天的分段数据task_id覆盖率
|
-- 替换参数：@vehicleId, @statDate
|
SET @vehicleId = 1; -- 替换为实际车辆ID
|
SET @statDate = '2025-12-04'; -- 替换为实际日期
|
|
SELECT
|
'分段统计' as '类型',
|
COUNT(*) as '总分段数',
|
SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) as '有任务ID的分段数',
|
CONCAT(ROUND(SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%') as 'task_id覆盖率'
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE vehicle_id = @vehicleId
|
AND DATE(segment_start_time) = @statDate;
|
|
-- 2. 对比两种计算方式的结果
|
-- 方式A：优化方案（直接按task_id聚合）
|
SELECT
|
'优化方案' as '计算方式',
|
SUM(segment_distance) as '总里程',
|
SUM(CASE WHEN task_id IS NOT NULL THEN segment_distance ELSE 0 END) as '任务里程',
|
SUM(CASE WHEN task_id IS NULL THEN segment_distance ELSE 0 END) as '非任务里程',
|
CONCAT(ROUND(SUM(CASE WHEN task_id IS NOT NULL THEN segment_distance ELSE 0 END) * 100.0 /
|
NULLIF(SUM(segment_distance), 0), 2), '%') as '任务占比'
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE vehicle_id = @vehicleId
|
AND DATE(segment_start_time) = @statDate;
|
|
-- 方式B：当前统计表中的数据（可能使用了时间重叠计算）
|
SELECT
|
'统计表数据' as '计算方式',
|
total_mileage as '总里程',
|
task_mileage as '任务里程',
|
non_task_mileage as '非任务里程',
|
CONCAT(ROUND(task_ratio * 100, 2), '%') as '任务占比'
|
FROM tb_vehicle_mileage_stats
|
WHERE vehicle_id = @vehicleId
|
AND DATE(stat_date) = @statDate;
|
|
-- 3. 查看使用优化方案的车辆数量（task_id覆盖率 > 80%）
|
SELECT
|
DATE(segment_start_time) as '日期',
|
vehicle_id,
|
vehicle_no,
|
COUNT(*) as '总分段数',
|
SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) as '有task_id分段数',
|
CONCAT(ROUND(SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%') as '覆盖率',
|
CASE
|
WHEN SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) > COUNT(*) * 0.8
|
THEN '✓ 使用优化方案'
|
ELSE '× 使用降级方案'
|
END as '方案选择'
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE segment_start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
|
GROUP BY DATE(segment_start_time), vehicle_id, vehicle_no
|
HAVING COUNT(*) > 10 -- 只统计分段数大于10的
|
ORDER BY DATE(segment_start_time) DESC, vehicle_id
|
LIMIT 20;
|
|
-- 4. 统计整体优化效果
|
SELECT
|
'整体统计' as '统计类型',
|
COUNT(DISTINCT CONCAT(vehicle_id, '_', DATE(segment_start_time))) as '车辆-日期组合总数',
|
SUM(CASE WHEN task_coverage > 0.8 THEN 1 ELSE 0 END) as '可使用优化方案的数量',
|
CONCAT(ROUND(SUM(CASE WHEN task_coverage > 0.8 THEN 1 ELSE 0 END) * 100.0 /
|
COUNT(DISTINCT CONCAT(vehicle_id, '_', DATE(segment_start_time))), 2), '%') as '优化覆盖率'
|
FROM (
|
SELECT
|
vehicle_id,
|
DATE(segment_start_time) as stat_date,
|
SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) * 1.0 / COUNT(*) as task_coverage
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE segment_start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
|
GROUP BY vehicle_id, DATE(segment_start_time)
|
HAVING COUNT(*) > 10
|
) coverage_stats;
|
|
-- 5. 性能测试：执行时间对比
|
-- 说明：使用EXPLAIN ANALYZE查看执行计划（MySQL 8.0+）或使用BENCHMARK函数
|
|
-- 优化方案查询（直接聚合）
|
EXPLAIN
|
SELECT
|
vehicle_id,
|
SUM(CASE WHEN task_id IS NOT NULL THEN segment_distance ELSE 0 END) as task_mileage,
|
SUM(CASE WHEN task_id IS NULL THEN segment_distance ELSE 0 END) as non_task_mileage
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE vehicle_id = @vehicleId
|
AND DATE(segment_start_time) = @statDate
|
GROUP BY vehicle_id;
|
|
-- 6. 建议的优化措施
|
SELECT
|
'优化建议' as '类型',
|
'补充历史数据的task_id' as '措施1',
|
'确保定时任务正常运行' as '措施2',
|
'确保任务状态正确更新' as '措施3',
|
'定期检查task_id覆盖率' as '措施4';
|
|
-- ========================================
|
-- 使用说明：
|
-- 1. 设置参数：修改第7-8行的@vehicleId和@statDate
|
-- 2. 执行查询1-4查看优化效果
|
-- 3. 如果覆盖率低于80%，执行fix_segment_mileage_task_association.sql修复
|
-- 4. 重新统计，观察性能提升
|
-- ========================================
|
