신용정보원 대외계 장애와
보험료 산출 에러 연관성 분석

/* 장애 전후 오류율 비교 */
SELECT
    CASE
      WHEN calc_time <  TIMESTAMP '2026-06-15 09:12:00' THEN '장애 전'
      WHEN calc_time <= TIMESTAMP '2026-06-15 10:05:00' THEN '장애 중'
      ELSE '복구 후'
    END AS period,
    COUNT(*) AS total_calc_cnt,
    SUM(CASE WHEN error_code IS NOT NULL THEN 1 ELSE 0 END) AS error_cnt,
    ROUND(
      SUM(CASE WHEN error_code IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
    ) AS error_rate_pct
FROM premium_calc_log
WHERE calc_time BETWEEN TIMESTAMP '2026-06-15 08:30:00'
                    AND TIMESTAMP '2026-06-15 11:00:00'
GROUP BY
    CASE
      WHEN calc_time <  TIMESTAMP '2026-06-15 09:12:00' THEN '장애 전'
      WHEN calc_time <= TIMESTAMP '2026-06-15 10:05:00' THEN '장애 중'
      ELSE '복구 후'
    END;

/* 재산출 및 고객 정정 대상 식별 */
SELECT
    calc_id,
    policy_no,
    customer_id,
    product_code,
    calc_time,
    premium_amount AS original_premium,
    fallback_yn,
    cache_yn,
    credit_grade,
    credit_info_base_date,
    CASE
      WHEN error_code IS NOT NULL THEN '산출오류 재처리'
      WHEN fallback_yn = 'Y' OR cache_yn = 'Y' THEN '정상조회 후 금액검증'
      ELSE '모니터링'
    END AS action_type
FROM premium_calc_log
WHERE calc_time BETWEEN TIMESTAMP '2026-06-15 09:12:00'
                    AND TIMESTAMP '2026-06-15 10:05:00'
  AND (
        error_code IS NOT NULL
        OR fallback_yn = 'Y'
        OR cache_yn = 'Y'
        OR credit_grade IS NULL
      )
ORDER BY calc_time;

# WAS / EAI / MCI 로그 검색 키워드 예시

TIMEOUT
READ_TIMEOUT
CONNECT_TIMEOUT
BROKEN_PIPE
CI_RESPONSE_NULL
CREDIT_INFO_EMPTY
PARSING_ERROR
INVALID_LENGTH
INVALID_RESPONSE_CODE
FALLBACK_APPLIED
CACHE_HIT
RATE_ENGINE_INPUT_NULL
PREMIUM_CALC_FAIL
RULE_EXECUTION_ERROR