-- ========================================
|
-- 修复GPS分段里程的任务关联
|
-- 用途：为已有的分段里程数据补充 task_id 和 task_code
|
-- 优化说明：补充task_id后，统计计算将使用优化方案（直接SQL聚合，更快）
|
-- ========================================
|
|
-- 1. 查看当前未关联任务的分段数量
|
SELECT COUNT(*) as '未关联任务的分段数'
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE task_id IS NULL;
|
|
-- 2. 查看有多少任务可以被关联
|
SELECT COUNT(DISTINCT t.task_id) as '可关联的任务数'
|
FROM sys_task t
|
INNER JOIN sys_task_vehicle tv ON t.task_id = tv.task_id
|
WHERE t.del_flag = '0'
|
AND t.task_status NOT IN ('PENDING', 'CANCELLED');
|
|
-- 3. 更新逻辑：根据车辆ID和时间重叠关联任务
|
-- 注意：这个查询会比较慢，建议分批执行或在非高峰期执行
|
|
UPDATE tb_vehicle_gps_segment_mileage seg
|
INNER JOIN (
|
SELECT
|
seg2.segment_id,
|
t.task_id,
|
t.task_code
|
FROM tb_vehicle_gps_segment_mileage seg2
|
INNER JOIN sys_task_vehicle tv ON seg2.vehicle_id = tv.vehicle_id
|
INNER JOIN sys_task t ON tv.task_id = t.task_id
|
WHERE seg2.task_id IS NULL
|
AND t.del_flag = '0'
|
-- AND t.task_status NOT IN ('PENDING', 'CANCELLED')
|
-- 时间重叠条件：分段开始时间 < 任务结束时间 AND 分段结束时间 > 任务开始时间
|
AND seg2.segment_start_time < COALESCE(t.actual_end_time, t.planned_end_time, DATE_ADD(t.create_time, INTERVAL 24 HOUR))
|
AND seg2.segment_end_time > COALESCE(t.actual_start_time, t.planned_start_time, t.create_time)
|
GROUP BY seg2.segment_id, t.task_id, t.task_code
|
) task_match ON seg.segment_id = task_match.segment_id
|
SET
|
seg.task_id = task_match.task_id,
|
seg.task_code = task_match.task_code;
|
|
-- 4. 查看修复结果
|
SELECT
|
'已关联任务' as '类型',
|
COUNT(*) as '数量',
|
CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tb_vehicle_gps_segment_mileage), 2), '%') as '占比'
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE task_id IS NOT NULL
|
UNION ALL
|
SELECT
|
'未关联任务' as '类型',
|
COUNT(*) as '数量',
|
CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tb_vehicle_gps_segment_mileage), 2), '%') as '占比'
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE task_id IS NULL;
|
|
-- 5. 查看每个车辆的关联情况
|
SELECT
|
seg.vehicle_id,
|
seg.vehicle_no,
|
COUNT(*) as '总分段数',
|
SUM(CASE WHEN seg.task_id IS NOT NULL THEN 1 ELSE 0 END) as '已关联分段数',
|
CONCAT(ROUND(SUM(CASE WHEN seg.task_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%') as '关联率'
|
FROM tb_vehicle_gps_segment_mileage seg
|
GROUP BY seg.vehicle_id, seg.vehicle_no
|
ORDER BY COUNT(*) DESC
|
LIMIT 20;
|
|
-- 6. 分析未能关联的原因
|
-- 查看某个未关联分段的详细信息
|
SELECT
|
seg.segment_id,
|
seg.vehicle_id,
|
seg.vehicle_no,
|
seg.segment_start_time,
|
seg.segment_end_time,
|
'该时段该车辆的任务' as '说明',
|
t.task_id,
|
t.task_code,
|
t.task_status,
|
t.create_time,
|
COALESCE(t.actual_start_time, t.planned_start_time) as '任务开始时间',
|
COALESCE(t.actual_end_time, t.planned_end_time) as '任务结束时间'
|
FROM tb_vehicle_gps_segment_mileage seg
|
LEFT JOIN sys_task_vehicle tv ON seg.vehicle_id = tv.vehicle_id
|
LEFT JOIN sys_task t ON tv.task_id = t.task_id
|
AND t.del_flag = '0'
|
AND seg.segment_start_time < COALESCE(t.actual_end_time, t.planned_end_time, DATE_ADD(t.create_time, INTERVAL 24 HOUR))
|
AND seg.segment_end_time > COALESCE(t.actual_start_time, t.planned_start_time, t.create_time)
|
WHERE seg.task_id IS NULL
|
ORDER BY seg.segment_start_time DESC
|
LIMIT 10;
|
|
-- ========================================
|
-- 使用说明：
|
-- 1. 先执行查询语句（1、2）查看数据情况
|
-- 2. 在非高峰期执行更新语句（3）
|
-- 3. 执行结果查询（4、5、6）验证修复效果
|
-- 4. 如果数据量大，建议添加 LIMIT 分批执行
|
-- ========================================
|
|
-- ========================================
|
-- 预防措施：确保定时任务正常运行
|
-- ========================================
|
|
-- 7. 检查GPS分段里程计算定时任务状态
|
SELECT
|
job_id,
|
job_name,
|
job_group,
|
invoke_target,
|
cron_expression,
|
status as '状态(0=正常,1=暂停)',
|
create_time,
|
update_time
|
FROM sys_job
|
WHERE job_name LIKE '%GPS%' OR job_name LIKE '%里程%'
|
ORDER BY create_time DESC;
|
|
-- 8. 如果定时任务是暂停状态，启动它
|
-- UPDATE sys_job SET status = '0' WHERE job_name = 'GPS分段里程实时计算';
|
|
-- 9. 检查最近的GPS计算记录
|
SELECT
|
vehicle_id,
|
vehicle_no,
|
MAX(segment_end_time) as '最后计算时间',
|
COUNT(*) as '分段数',
|
SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) as '有任务关联的分段数'
|
FROM tb_vehicle_gps_segment_mileage
|
WHERE segment_end_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
|
GROUP BY vehicle_id, vehicle_no
|
ORDER BY MAX(segment_end_time) DESC;
|
