-- ========================================
-- 测试里程统计优化效果
-- 用途：对比优化前后的性能和准确性
-- ========================================

-- 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. 重新统计，观察性能提升
-- ========================================