From 8027c8e2e0b5c559da612b187031dd6fd82d9bc7 Mon Sep 17 00:00:00 2001
From: Junjie <fallin.jie@qq.com>
Date: 星期六, 04 四月 2026 12:03:48 +0800
Subject: [PATCH] #任务分析异常修复
---
src/main/resources/sql/20260331_add_station_err_log.sql | 67 +++++++++++++++++++++++++--------
1 files changed, 50 insertions(+), 17 deletions(-)
diff --git a/src/main/resources/sql/20260331_add_station_err_log.sql b/src/main/resources/sql/20260331_add_station_err_log.sql
index be5f1fd..90d19cc 100644
--- a/src/main/resources/sql/20260331_add_station_err_log.sql
+++ b/src/main/resources/sql/20260331_add_station_err_log.sql
@@ -69,42 +69,75 @@
DEALLOCATE PREPARE stmt_station_fault_duration;
UPDATE `asr_wrk_analysis` a
-LEFT JOIN (
- SELECT
- e.`wrk_no`,
- COUNT(*) AS `fault_count`,
- SUM(
+SET a.`station_fault_count` = COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_station_err_log` e
+ WHERE e.`wrk_no` = a.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
+ ), 0),
+ a.`station_fault_duration_ms` = COALESCE((
+ SELECT SUM(
CASE
WHEN e.`start_time` IS NULL THEN 0
ELSE GREATEST(
TIMESTAMPDIFF(
MICROSECOND,
- e.`start_time`,
- COALESCE(e.`end_time`, wl.`modi_time`, wl.`io_time`, wl.`appe_time`, e.`start_time`)
+ GREATEST(e.`start_time`, COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)),
+ LEAST(COALESCE(e.`end_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)), COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
),
0
) DIV 1000
END
- ) AS `fault_duration_ms`
- FROM `asr_bas_station_err_log` e
- LEFT JOIN `asr_wrk_mast_log` wl ON wl.`wrk_no` = e.`wrk_no`
- GROUP BY e.`wrk_no`
-) s ON s.`wrk_no` = a.`wrk_no`
-SET a.`station_fault_count` = COALESCE(s.`fault_count`, 0),
- a.`station_fault_duration_ms` = COALESCE(s.`fault_duration_ms`, 0),
+ )
+ FROM `asr_bas_station_err_log` e
+ WHERE e.`wrk_no` = a.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
+ ), 0),
a.`fault_count` = COALESCE(a.`crn_fault_count`, 0)
+ COALESCE(a.`dual_crn_fault_count`, 0)
+ COALESCE(a.`rgv_fault_count`, 0)
- + COALESCE(s.`fault_count`, 0),
+ + COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_station_err_log` e
+ WHERE e.`wrk_no` = a.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
+ ), 0),
a.`fault_duration_ms` = COALESCE(a.`crn_fault_duration_ms`, 0)
+ COALESCE(a.`dual_crn_fault_duration_ms`, 0)
+ COALESCE(a.`rgv_fault_duration_ms`, 0)
- + COALESCE(s.`fault_duration_ms`, 0),
+ + COALESCE((
+ SELECT SUM(
+ CASE
+ WHEN e.`start_time` IS NULL THEN 0
+ ELSE GREATEST(
+ TIMESTAMPDIFF(
+ MICROSECOND,
+ GREATEST(e.`start_time`, COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)),
+ LEAST(COALESCE(e.`end_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)), COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
+ ),
+ 0
+ ) DIV 1000
+ END
+ )
+ FROM `asr_bas_station_err_log` e
+ WHERE e.`wrk_no` = a.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
+ ), 0),
a.`has_fault` = CASE
WHEN COALESCE(a.`crn_fault_count`, 0)
+ COALESCE(a.`dual_crn_fault_count`, 0)
+ COALESCE(a.`rgv_fault_count`, 0)
- + COALESCE(s.`fault_count`, 0) > 0 THEN 1
+ + COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_station_err_log` e
+ WHERE e.`wrk_no` = a.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
+ ), 0) > 0 THEN 1
ELSE 0
END,
a.`update_time` = NOW()
--
Gitblit v1.9.1