#任务分析异常修复

Junjie

7 小时以前 8027c8e2e0b5c559da612b187031dd6fd82d9bc7

 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()