1m 5s 동안 처리함
좋습니다. 이번엔 네 쿼리가 전부 같은 판정 로직을 쓰도록 맞췄습니다.
그리고 TB_DIVISION_INFO B는 이번 보정 판단에 사용되는 컬럼이 없어서 일부러 제외했습니다. 괜히 붙였다가 마스터 중복이 있으면 행만 불어날 수 있습니다. 필요한 건 A ↔ C의 COMPANY_ID + GUBUN1 + GUBUN2 조합입니다.
공통 규칙은 다음입니다.
[보정 대상]
현재 출입 가능
+ DISPLAY_TEXT가 VIP 또는 임산부
+ CAR_TYPE IN ('차종', '화물차', '들')
↓
[1순위]
같은 COMPANY_ID + CAR_NO의 정상 과거 이력이 있는가?
→ 있으면 가장 많이 사용된 GUBUN1/GUBUN2
↓ 없으면
[2순위]
같은 COMPANY_ID + CAR_TYPE의 정상 이력 중
가장 많이 사용된 GUBUN1/GUBUN2
단,
- CAR_TYPE = CAR_NO인 데이터 제외
- 그 CAR_TYPE이 서로 다른 차량 1대에서만 사용된 경우 제외
↓
빈도 동률
→ 가장 최근 사용값
→ 그래도 동률이면 GUBUN1, GUBUN2 순
---
1. CTE 버전 — UPDATE 전 검증용
이걸 제일 먼저 실행하면 됩니다.
WITH
/* =====================================================================
[1] TARGET
---------------------------------------------------------------------
목적 :
현재 잘못된 GUBUN1 / GUBUN2가 들어간 것으로 의심되는
"실제 수정 대상 차량"을 찾는다.
수정 대상 조건 :
1. 현재 시간이 START_DATE_TIME ~ END_DATE_TIME 사이
2. 현재 GUBUN1/GUBUN2에 해당하는 DISPLAY_TEXT가
VIP 또는 임산부
3. CAR_TYPE이 '차종', '화물차', '들' 중 하나
주의 :
TB_MEMBERTYPE_INFO는 GROUP_ID 하나만으로 판단하지 않는다.
반드시
COMPANY_ID
+ DIVISION_ID(GUBUN1)
+ GROUP_ID(GUBUN2)
조합으로 연결한다.
===================================================================== */
TARGET AS
(
SELECT
A.ROWID AS RID,
A.SERVICE_ID,
A.COMPANY_ID,
TRIM(A.CAR_NO) AS CAR_NO,
TRIM(A.CAR_TYPE) AS CAR_TYPE,
A.GUBUN1 AS OLD_GUBUN1,
A.GUBUN2 AS OLD_GUBUN2,
C.DISPLAY_TEXT AS OLD_DISPLAY_TEXT
FROM TB_SERVICE_INFO A
JOIN TB_MEMBERTYPE_INFO C
ON A.COMPANY_ID = C.COMPANY_ID
AND A.GUBUN1 = C.DIVISION_ID
AND A.GUBUN2 = C.GROUP_ID
WHERE A.START_DATE_TIME <= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
AND A.END_DATE_TIME >= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
AND
(
C.DISPLAY_TEXT LIKE '%VIP%'
OR C.DISPLAY_TEXT LIKE '%임산부%'
)
AND TRIM(A.CAR_TYPE) IN
(
'차종',
'화물차',
'들'
)
),
/* =====================================================================
[2] NORMAL_HISTORY
---------------------------------------------------------------------
목적 :
잘못 등록된 현재 값을 고칠 때 참고할 수 있는
"정상적인 과거 등록 이력"을 모은다.
왜 VIP / 임산부를 제외하는가?
현재 바로 그 VIP / 임산부 값이 잘못 들어간 데이터를
수정하려는 작업이므로,
동일한 오염 데이터가 과거에도 존재할 수 있다.
따라서 VIP / 임산부 이력을 정답 후보로 사용하지 않는다.
여기서 만든 데이터는 이후
1. 동일 CAR_NO 통계
2. 동일 CAR_TYPE 통계
두 곳에서 공통으로 사용한다.
REF_DATE :
빈도가 같은 후보가 여러 개일 때
가장 최근에 사용된 GUBUN을 선택하기 위한 날짜.
===================================================================== */
NORMAL_HISTORY AS
(
SELECT
H.COMPANY_ID,
TRIM(H.CAR_NO) AS CAR_NO,
TRIM(H.CAR_TYPE) AS CAR_TYPE,
H.GUBUN1,
H.GUBUN2,
NVL
(
H.LAST_UPDATE_DATE,
NVL
(
H.FIRST_INSERT_DATE,
H.START_DATE_TIME
)
) AS REF_DATE
FROM TB_SERVICE_INFO H
JOIN TB_MEMBERTYPE_INFO M
ON H.COMPANY_ID = M.COMPANY_ID
AND H.GUBUN1 = M.DIVISION_ID
AND H.GUBUN2 = M.GROUP_ID
WHERE
M.DISPLAY_TEXT IS NULL
OR
(
M.DISPLAY_TEXT NOT LIKE '%VIP%'
AND M.DISPLAY_TEXT NOT LIKE '%임산부%'
)
),
/* =====================================================================
[3] CARNO_STAT
---------------------------------------------------------------------
목적 :
같은 차량번호(CAR_NO)가 과거에
어떤 GUBUN1 / GUBUN2 조합을 몇 번 사용했는지 집계한다.
예 :
12가3456 / GUBUN1=8 / GUBUN2=90 → 17회
12가3456 / GUBUN1=4 / GUBUN2=20 → 3회
아직 여기에서는 정답을 하나 고르는 것이 아니라
각 GUBUN 조합의 "빈도"만 계산한다.
===================================================================== */
CARNO_STAT AS
(
SELECT
COMPANY_ID,
CAR_NO,
GUBUN1,
GUBUN2,
COUNT(*) AS CNT,
/* 빈도 동률일 경우 최근값을 판단하기 위한 날짜 */
MAX(REF_DATE) AS LAST_DATE
FROM NORMAL_HISTORY
GROUP BY
COMPANY_ID,
CAR_NO,
GUBUN1,
GUBUN2
),
/* =====================================================================
[4] CARNO_BEST
---------------------------------------------------------------------
목적 :
각 차량번호별로 가장 신뢰할 수 있는
GUBUN1 / GUBUN2 한 조합만 고른다.
우선순위 :
1. 가장 많이 등록된 조합
2. 사용 횟수가 같으면 가장 최근에 사용된 조합
3. 그래도 같으면 GUBUN1, GUBUN2 순
중요 :
이번 보정에서는 이 값이 "1순위"다.
같은 CAR_NO의 정상 과거 이력이 존재한다면
CAR_TYPE 기반 통계는 사용하지 않는다.
===================================================================== */
CARNO_BEST AS
(
SELECT
COMPANY_ID,
CAR_NO,
GUBUN1,
GUBUN2,
CNT,
LAST_DATE
FROM
(
SELECT
S.*,
ROW_NUMBER() OVER
(
PARTITION BY
COMPANY_ID,
CAR_NO
ORDER BY
CNT DESC,
LAST_DATE DESC NULLS LAST,
GUBUN1,
GUBUN2
) AS RN
FROM CARNO_STAT S
)
WHERE RN = 1
),
/* =====================================================================
[5] CAR_TYPE_USAGE
---------------------------------------------------------------------
목적 :
CAR_TYPE 자체가 "공통 차종값"으로 믿을 만한 값인지 검사한다.
실제 데이터 오류 사례 :
사용자가 CAR_TYPE 칸에 차종 대신
차량번호(CAR_NO)를 그대로 입력하는 경우가 존재한다.
예 :
CAR_NO = 12가3456
CAR_TYPE = 12가3456
처리 방법 1 :
CAR_TYPE = CAR_NO인 행은 명백한 입력오류이므로
CAR_TYPE 통계에서 즉시 제외한다.
처리 방법 2 :
어떤 CAR_TYPE이 존재하더라도
그 값이 서로 다른 차량 딱 한 대에서만 사용됐다면
그 차량에만 잘못 입력된 임의값일 가능성이 높다.
따라서 서로 다른 CAR_NO가 2대 이상 사용하는
CAR_TYPE만 신뢰한다.
CAR_NO_CNT :
해당 CAR_TYPE을 사용하는 서로 다른 차량의 수.
===================================================================== */
CAR_TYPE_USAGE AS
(
SELECT
COMPANY_ID,
TRIM(CAR_TYPE) AS CAR_TYPE,
COUNT(DISTINCT TRIM(CAR_NO)) AS CAR_NO_CNT
FROM TB_SERVICE_INFO
WHERE CAR_TYPE IS NOT NULL
/* 차종 칸에 차량번호를 그대로 적은 명백한 오류 제외 */
AND TRIM(CAR_TYPE) <> TRIM(CAR_NO)
GROUP BY
COMPANY_ID,
TRIM(CAR_TYPE)
),
/* =====================================================================
[6] CARTYPE_STAT
---------------------------------------------------------------------
목적 :
동일 CAR_NO의 과거 이력이 없는 차량을 보정하기 위해
같은 CAR_TYPE에서 어떤 GUBUN1/GUBUN2가
가장 많이 사용됐는지 집계한다.
사용 가능한 CAR_TYPE :
CAR_TYPE_USAGE에서
서로 다른 차량 2대 이상이 사용한 값만 허용한다.
추가 방어 :
CAR_TYPE = CAR_NO인 이력은 다시 한 번 제외한다.
예 :
화물차 / GUBUN1=8 / GUBUN2=90 → 800회
화물차 / GUBUN1=7 / GUBUN2=30 → 20회
===================================================================== */
CARTYPE_STAT AS
(
SELECT
H.COMPANY_ID,
H.CAR_TYPE,
H.GUBUN1,
H.GUBUN2,
COUNT(*) AS CNT,
MAX(H.REF_DATE) AS LAST_DATE
FROM NORMAL_HISTORY H
JOIN CAR_TYPE_USAGE U
ON H.COMPANY_ID = U.COMPANY_ID
AND H.CAR_TYPE = U.CAR_TYPE
/* 한 차량에서만 쓰인 CAR_TYPE은 사용하지 않음 */
AND U.CAR_NO_CNT > 1
WHERE H.CAR_TYPE IS NOT NULL
/* CAR_TYPE에 CAR_NO를 입력한 데이터 재차 제외 */
AND TRIM(H.CAR_TYPE) <> TRIM(H.CAR_NO)
GROUP BY
H.COMPANY_ID,
H.CAR_TYPE,
H.GUBUN1,
H.GUBUN2
),
/* =====================================================================
[7] CARTYPE_BEST
---------------------------------------------------------------------
목적 :
CAR_TYPE별 가장 많이 사용된
GUBUN1 / GUBUN2 조합 하나를 선택한다.
우선순위 :
1. 가장 많이 등록된 조합
2. 빈도가 같으면 최근 사용 조합
3. 그래도 같으면 GUBUN1, GUBUN2 순
중요 :
이 값은 CAR_NO에서 정답을 찾지 못했을 때만 사용한다.
===================================================================== */
CARTYPE_BEST AS
(
SELECT
COMPANY_ID,
CAR_TYPE,
GUBUN1,
GUBUN2,
CNT,
LAST_DATE
FROM
(
SELECT
S.*,
ROW_NUMBER() OVER
(
PARTITION BY
COMPANY_ID,
CAR_TYPE
ORDER BY
CNT DESC,
LAST_DATE DESC NULLS LAST,
GUBUN1,
GUBUN2
) AS RN
FROM CARTYPE_STAT S
)
WHERE RN = 1
),
/* =====================================================================
[8] RESULT_DATA
---------------------------------------------------------------------
목적 :
TARGET 한 건마다 실제로 어떤 값으로 변경할지 최종 결정한다.
판단 순서 :
1순위 : 동일 COMPANY_ID + CAR_NO 정상 과거 이력
2순위 : 동일 COMPANY_ID + CAR_TYPE 정상 통계
즉 :
CAR_NO 후보가 있으면 무조건 CAR_NO 후보를 사용한다.
CAR_NO 후보가 없을 때만 CAR_TYPE 후보를 사용한다.
FIX_BY :
CAR_NO
→ 동일 차량번호 이력으로 결정
CAR_TYPE
→ 차량번호 이력이 없어 차종 통계로 결정
CAR_TYPE_EXCLUDED
→ 해당 CAR_TYPE이 한 차량에서만 사용되어
신뢰할 수 없는 차종으로 판단
NO_REFERENCE
→ CAR_NO / CAR_TYPE 어느 쪽에서도 정답 후보를 찾지 못함
===================================================================== */
RESULT_DATA AS
(
SELECT
T.RID,
T.SERVICE_ID,
T.COMPANY_ID,
T.CAR_NO,
T.CAR_TYPE,
T.OLD_GUBUN1,
T.OLD_GUBUN2,
T.OLD_DISPLAY_TEXT,
/* 해당 CAR_TYPE을 사용하는 서로 다른 차량 수 */
U.CAR_NO_CNT AS CAR_TYPE_CAR_COUNT,
/* -----------------------------------------------------
변경할 GUBUN1
CAR_NO 후보가 있으면 그것을 우선 사용
----------------------------------------------------- */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.GUBUN1
ELSE CT.GUBUN1
END AS NEW_GUBUN1,
/* -----------------------------------------------------
변경할 GUBUN2
CAR_NO 후보가 있으면 그것을 우선 사용
----------------------------------------------------- */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.GUBUN2
ELSE CT.GUBUN2
END AS NEW_GUBUN2,
/* -----------------------------------------------------
최종 선택된 후보가 과거 몇 번 사용됐는지
검증용 정보
----------------------------------------------------- */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.CNT
ELSE CT.CNT
END AS MATCH_COUNT,
/* -----------------------------------------------------
어떤 방식으로 변경값을 찾았는지 기록
----------------------------------------------------- */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN 'CAR_NO'
WHEN CT.CAR_TYPE IS NOT NULL
THEN 'CAR_TYPE'
WHEN NVL(U.CAR_NO_CNT, 0) <= 1
THEN 'CAR_TYPE_EXCLUDED'
ELSE
'NO_REFERENCE'
END AS FIX_BY
FROM TARGET T
/* ---------------------------------------------------------
1순위 :
동일 차량번호의 정상 과거 최빈값
--------------------------------------------------------- */
LEFT JOIN CARNO_BEST CN
ON T.COMPANY_ID = CN.COMPANY_ID
AND T.CAR_NO = CN.CAR_NO
/* ---------------------------------------------------------
2순위 :
차량번호 이력이 없을 경우 사용할
동일 CAR_TYPE의 정상 최빈값
--------------------------------------------------------- */
LEFT JOIN CARTYPE_BEST CT
ON T.COMPANY_ID = CT.COMPANY_ID
AND T.CAR_TYPE = CT.CAR_TYPE
/* ---------------------------------------------------------
CAR_TYPE이 여러 차량에서 사용된 공통값인지 확인
--------------------------------------------------------- */
LEFT JOIN CAR_TYPE_USAGE U
ON T.COMPANY_ID = U.COMPANY_ID
AND T.CAR_TYPE = U.CAR_TYPE
)
/* =====================================================================
[9] 최종 검증 결과
---------------------------------------------------------------------
실제 UPDATE 전에 사람이 확인하기 위한 SELECT.
OLD_GUBUN1 / OLD_GUBUN2
→ 현재 값
NEW_GUBUN1 / NEW_GUBUN2
→ 변경 예정 값
OLD_DISPLAY_TEXT
→ 현재 VIP / 임산부 값
NEW_DISPLAY_TEXT
→ 변경 후 들어갈 구분명
FIX_BY
→ CAR_NO 기준인지 CAR_TYPE 기준인지
MATCH_COUNT
→ 선택한 GUBUN 조합이 과거 몇 번 사용됐는지
CAR_TYPE_CAR_COUNT
→ 해당 CAR_TYPE이 몇 대의 서로 다른 차량에서 사용됐는지
UPDATE_STATUS
UPDATE
→ 실제 UPDATE 대상
NO_CHANGE
→ 계산된 결과가 현재값과 동일
SKIP
→ 신뢰할 수 있는 변경값을 찾지 못함
===================================================================== */
SELECT
R.SERVICE_ID,
R.COMPANY_ID,
R.CAR_NO,
R.CAR_TYPE,
/* 현재 값 */
R.OLD_GUBUN1,
R.OLD_GUBUN2,
R.OLD_DISPLAY_TEXT,
/* 변경 예정 값 */
R.NEW_GUBUN1,
R.NEW_GUBUN2,
NEW_C.DISPLAY_TEXT AS NEW_DISPLAY_TEXT,
/* 변경값 판단 근거 */
R.FIX_BY,
/* 선택된 후보의 사용 횟수 */
R.MATCH_COUNT,
/* 이 차종을 사용한 서로 다른 차량 수 */
R.CAR_TYPE_CAR_COUNT,
/* 실제 변경 여부 */
CASE
WHEN R.NEW_GUBUN1 IS NULL
OR R.NEW_GUBUN2 IS NULL
THEN 'SKIP'
WHEN R.OLD_GUBUN1 = R.NEW_GUBUN1
AND R.OLD_GUBUN2 = R.NEW_GUBUN2
THEN 'NO_CHANGE'
ELSE
'UPDATE'
END AS UPDATE_STATUS
FROM RESULT_DATA R
/* 변경 후 GUBUN의 실제 DISPLAY_TEXT 확인 */
LEFT JOIN TB_MEMBERTYPE_INFO NEW_C
ON R.COMPANY_ID = NEW_C.COMPANY_ID
AND R.NEW_GUBUN1 = NEW_C.DIVISION_ID
AND R.NEW_GUBUN2 = NEW_C.GROUP_ID
ORDER BY
UPDATE_STATUS,
R.FIX_BY,
R.COMPANY_ID,
R.CAR_NO;
---
2. CTE 버전 — 실제 UPDATE
위 검증 결과에서 UPDATE_STATUS = 'UPDATE'인 데이터가 정상인지 확인한 후 실행합니다.
MERGE INTO TB_SERVICE_INFO A
USING
(
WITH
/* =================================================================
[1] TARGET
-----------------------------------------------------------------
실제 수정 대상.
조건 :
- 현재 출입 가능
- 현재 DISPLAY_TEXT가 VIP 또는 임산부
- CAR_TYPE이 '차종', '화물차', '들'
================================================================= */
TARGET AS
(
SELECT
A.ROWID AS RID,
A.COMPANY_ID,
TRIM(A.CAR_NO) AS CAR_NO,
TRIM(A.CAR_TYPE) AS CAR_TYPE,
A.GUBUN1 AS OLD_GUBUN1,
A.GUBUN2 AS OLD_GUBUN2
FROM TB_SERVICE_INFO A
JOIN TB_MEMBERTYPE_INFO C
ON A.COMPANY_ID = C.COMPANY_ID
AND A.GUBUN1 = C.DIVISION_ID
AND A.GUBUN2 = C.GROUP_ID
WHERE A.START_DATE_TIME <= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
AND A.END_DATE_TIME >= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
AND
(
C.DISPLAY_TEXT LIKE '%VIP%'
OR C.DISPLAY_TEXT LIKE '%임산부%'
)
AND TRIM(A.CAR_TYPE) IN
(
'차종',
'화물차',
'들'
)
),
/* =================================================================
[2] NORMAL_HISTORY
-----------------------------------------------------------------
보정값을 결정할 때 사용할 정상 과거 이력.
현재 문제 대상인 VIP / 임산부 이력은
정답 후보에서 제외한다.
================================================================= */
NORMAL_HISTORY AS
(
SELECT
H.COMPANY_ID,
TRIM(H.CAR_NO) AS CAR_NO,
TRIM(H.CAR_TYPE) AS CAR_TYPE,
H.GUBUN1,
H.GUBUN2,
/* 빈도 동률 시 최신값 선택용 */
NVL
(
H.LAST_UPDATE_DATE,
NVL
(
H.FIRST_INSERT_DATE,
H.START_DATE_TIME
)
) AS REF_DATE
FROM TB_SERVICE_INFO H
JOIN TB_MEMBERTYPE_INFO M
ON H.COMPANY_ID = M.COMPANY_ID
AND H.GUBUN1 = M.DIVISION_ID
AND H.GUBUN2 = M.GROUP_ID
WHERE
M.DISPLAY_TEXT IS NULL
OR
(
M.DISPLAY_TEXT NOT LIKE '%VIP%'
AND M.DISPLAY_TEXT NOT LIKE '%임산부%'
)
),
/* =================================================================
[3] CARNO_STAT
-----------------------------------------------------------------
같은 차량번호가 어떤 GUBUN1/GUBUN2 조합을
과거에 몇 번 사용했는지 계산한다.
================================================================= */
CARNO_STAT AS
(
SELECT
COMPANY_ID,
CAR_NO,
GUBUN1,
GUBUN2,
COUNT(*) AS CNT,
MAX(REF_DATE) AS LAST_DATE
FROM NORMAL_HISTORY
GROUP BY
COMPANY_ID,
CAR_NO,
GUBUN1,
GUBUN2
),
/* =================================================================
[4] CARNO_BEST
-----------------------------------------------------------------
차량번호별 최빈 GUBUN1/GUBUN2 한 조합 선택.
우선순위 :
1. 사용횟수
2. 최근 사용일
3. GUBUN1 / GUBUN2
================================================================= */
CARNO_BEST AS
(
SELECT
COMPANY_ID,
CAR_NO,
GUBUN1,
GUBUN2
FROM
(
SELECT
S.*,
ROW_NUMBER() OVER
(
PARTITION BY
COMPANY_ID,
CAR_NO
ORDER BY
CNT DESC,
LAST_DATE DESC NULLS LAST,
GUBUN1,
GUBUN2
) AS RN
FROM CARNO_STAT S
)
WHERE RN = 1
),
/* =================================================================
[5] CAR_TYPE_USAGE
-----------------------------------------------------------------
CAR_TYPE이 정상적인 공통 차종값인지 판단한다.
제외 :
1. CAR_TYPE IS NULL
2. CAR_TYPE = CAR_NO
→ 차종 칸에 차량번호 복붙한 명백한 입력오류
이후 서로 다른 차량에서 2대 이상 사용된
CAR_TYPE만 신뢰한다.
================================================================= */
CAR_TYPE_USAGE AS
(
SELECT
COMPANY_ID,
TRIM(CAR_TYPE) AS CAR_TYPE,
COUNT(DISTINCT TRIM(CAR_NO)) AS CAR_NO_CNT
FROM TB_SERVICE_INFO
WHERE CAR_TYPE IS NOT NULL
/* 차종 자리에 차량번호를 넣은 데이터 제외 */
AND TRIM(CAR_TYPE) <> TRIM(CAR_NO)
GROUP BY
COMPANY_ID,
TRIM(CAR_TYPE)
),
/* =================================================================
[6] CARTYPE_STAT
-----------------------------------------------------------------
동일 CAR_NO 이력이 없는 경우 사용할
CAR_TYPE별 GUBUN1/GUBUN2 빈도를 계산한다.
단 :
서로 다른 차량 2대 이상이 사용한 CAR_TYPE만 허용.
================================================================= */
CARTYPE_STAT AS
(
SELECT
H.COMPANY_ID,
H.CAR_TYPE,
H.GUBUN1,
H.GUBUN2,
COUNT(*) AS CNT,
MAX(H.REF_DATE) AS LAST_DATE
FROM NORMAL_HISTORY H
JOIN CAR_TYPE_USAGE U
ON H.COMPANY_ID = U.COMPANY_ID
AND H.CAR_TYPE = U.CAR_TYPE
/* 특정 차량 한 대에만 몰린 CAR_TYPE 제외 */
AND U.CAR_NO_CNT > 1
WHERE H.CAR_TYPE IS NOT NULL
/* CAR_TYPE = CAR_NO 데이터 재차 제외 */
AND TRIM(H.CAR_TYPE) <> TRIM(H.CAR_NO)
GROUP BY
H.COMPANY_ID,
H.CAR_TYPE,
H.GUBUN1,
H.GUBUN2
),
/* =================================================================
[7] CARTYPE_BEST
-----------------------------------------------------------------
CAR_TYPE별 가장 많이 사용된 GUBUN1/GUBUN2 한 조합 선택.
이 값은 동일 CAR_NO 정상 이력이 없을 때만 사용한다.
================================================================= */
CARTYPE_BEST AS
(
SELECT
COMPANY_ID,
CAR_TYPE,
GUBUN1,
GUBUN2
FROM
(
SELECT
S.*,
ROW_NUMBER() OVER
(
PARTITION BY
COMPANY_ID,
CAR_TYPE
ORDER BY
CNT DESC,
LAST_DATE DESC NULLS LAST,
GUBUN1,
GUBUN2
) AS RN
FROM CARTYPE_STAT S
)
WHERE RN = 1
),
/* =================================================================
[8] RESULT_DATA
-----------------------------------------------------------------
TARGET별 실제 변경값 결정.
1순위 :
같은 COMPANY_ID + CAR_NO
2순위 :
같은 COMPANY_ID + CAR_TYPE
CAR_NO 후보가 존재하면 CAR_TYPE 후보는 사용하지 않는다.
================================================================= */
RESULT_DATA AS
(
SELECT
T.RID,
T.OLD_GUBUN1,
T.OLD_GUBUN2,
/* 변경 예정 GUBUN1 */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.GUBUN1
ELSE CT.GUBUN1
END AS NEW_GUBUN1,
/* 변경 예정 GUBUN2 */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.GUBUN2
ELSE CT.GUBUN2
END AS NEW_GUBUN2
FROM TARGET T
/* 1순위 : 같은 차량번호 */
LEFT JOIN CARNO_BEST CN
ON T.COMPANY_ID = CN.COMPANY_ID
AND T.CAR_NO = CN.CAR_NO
/* 2순위 : 같은 차종 */
LEFT JOIN CARTYPE_BEST CT
ON T.COMPANY_ID = CT.COMPANY_ID
AND T.CAR_TYPE = CT.CAR_TYPE
)
/* =================================================================
[9] MERGE에 전달할 실제 변경 대상
-----------------------------------------------------------------
조건 :
1. 새로운 GUBUN1/GUBUN2를 정상적으로 찾았고
2. 기존 값과 실제로 다른 행만 전달한다.
SKIP / NO_CHANGE 대상은 MERGE에 넘기지 않는다.
================================================================= */
SELECT
RID,
NEW_GUBUN1,
NEW_GUBUN2
FROM RESULT_DATA
WHERE NEW_GUBUN1 IS NOT NULL
AND NEW_GUBUN2 IS NOT NULL
AND
(
OLD_GUBUN1 <> NEW_GUBUN1
OR OLD_GUBUN2 <> NEW_GUBUN2
)
) S
/* =====================================================================
[10] 실제 대상 행 연결
---------------------------------------------------------------------
SERVICE_ID가 첨부 DDL상 NOT NULL이지만
PK / UNIQUE 정의가 확인되지 않았기 때문에
이번 일회성 데이터 보정은 ROWID로 정확히 대상 행을 연결한다.
===================================================================== */
ON
(
A.ROWID = S.RID
)
/* =====================================================================
[11] 실제 UPDATE
---------------------------------------------------------------------
검증에서 계산했던 NEW_GUBUN1 / NEW_GUBUN2만 변경한다.
다른 컬럼은 건드리지 않는다.
===================================================================== */
WHEN MATCHED THEN
UPDATE SET
A.GUBUN1 = S.NEW_GUBUN1,
A.GUBUN2 = S.NEW_GUBUN2;
이후 직접 확인하고:
COMMIT;
-- 이상하면 COMMIT 전에
-- ROLLBACK;
---
3. 주니어용 — CTE 없는 검증용 한방쿼리
구조는 그냥 이렇게 보면 됩니다.
T = 고칠 대상
CN = 같은 차량번호에서 정답
CT = 같은 차종에서 정답
TU = 해당 차종을 믿어도 되는지
R = 최종 결정
SELECT
R.SERVICE_ID,
R.COMPANY_ID,
R.CAR_NO,
R.CAR_TYPE,
/* 현재 값 */
R.OLD_GUBUN1,
R.OLD_GUBUN2,
R.OLD_DISPLAY_TEXT,
/* 변경 예정 값 */
R.NEW_GUBUN1,
R.NEW_GUBUN2,
/* 변경 후 실제 구분명 */
NEW_C.DISPLAY_TEXT AS NEW_DISPLAY_TEXT,
/* 어떤 기준으로 변경값을 선택했는가 */
R.FIX_BY,
/* 선택된 후보가 과거에 몇 번 등장했는가 */
R.MATCH_COUNT,
/* 해당 CAR_TYPE을 서로 다른 몇 대의 차량이 사용했는가 */
R.CAR_TYPE_CAR_COUNT,
/* 실제 UPDATE 여부 */
CASE
WHEN R.NEW_GUBUN1 IS NULL
OR R.NEW_GUBUN2 IS NULL
THEN 'SKIP'
WHEN R.OLD_GUBUN1 = R.NEW_GUBUN1
AND R.OLD_GUBUN2 = R.NEW_GUBUN2
THEN 'NO_CHANGE'
ELSE
'UPDATE'
END AS UPDATE_STATUS
FROM
(
/* =================================================================
[R] 최종 변경값 결정
-----------------------------------------------------------------
T = 수정 대상
CN = 동일 CAR_NO의 가장 많이 사용된 정상 GUBUN
CT = 동일 CAR_TYPE의 가장 많이 사용된 정상 GUBUN
TU = CAR_TYPE이 여러 차량에서 사용된 정상 공통값인지 확인
우선순위 :
1. CN
2. CT
================================================================= */
SELECT
T.RID,
T.SERVICE_ID,
T.COMPANY_ID,
T.CAR_NO,
T.CAR_TYPE,
T.OLD_GUBUN1,
T.OLD_GUBUN2,
T.OLD_DISPLAY_TEXT,
TU.CAR_NO_CNT AS CAR_TYPE_CAR_COUNT,
/* -------------------------------------------------------------
CAR_NO 후보가 있으면 무조건 CAR_NO 후보 사용
------------------------------------------------------------- */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.GUBUN1
ELSE CT.GUBUN1
END AS NEW_GUBUN1,
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.GUBUN2
ELSE CT.GUBUN2
END AS NEW_GUBUN2,
/* -------------------------------------------------------------
최종 선택된 후보의 사용 횟수
------------------------------------------------------------- */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.CNT
ELSE CT.CNT
END AS MATCH_COUNT,
/* -------------------------------------------------------------
어떤 근거로 변경값을 찾았는지 표시
------------------------------------------------------------- */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN 'CAR_NO'
WHEN CT.CAR_TYPE IS NOT NULL
THEN 'CAR_TYPE'
WHEN NVL(TU.CAR_NO_CNT, 0) <= 1
THEN 'CAR_TYPE_EXCLUDED'
ELSE
'NO_REFERENCE'
END AS FIX_BY
FROM
(
/* =============================================================
[T] 실제 수정 대상
-------------------------------------------------------------
조건 :
1. 현재 출입 가능
2. 현재 DISPLAY_TEXT가 VIP 또는 임산부
3. CAR_TYPE이 '차종', '화물차', '들'
COMPANY_ID + GUBUN1 + GUBUN2 조합으로
TB_MEMBERTYPE_INFO를 정확하게 연결한다.
============================================================= */
SELECT
A.ROWID AS RID,
A.SERVICE_ID,
A.COMPANY_ID,
TRIM(A.CAR_NO) AS CAR_NO,
TRIM(A.CAR_TYPE) AS CAR_TYPE,
A.GUBUN1 AS OLD_GUBUN1,
A.GUBUN2 AS OLD_GUBUN2,
C.DISPLAY_TEXT AS OLD_DISPLAY_TEXT
FROM TB_SERVICE_INFO A
JOIN TB_MEMBERTYPE_INFO C
ON A.COMPANY_ID = C.COMPANY_ID
AND A.GUBUN1 = C.DIVISION_ID
AND A.GUBUN2 = C.GROUP_ID
WHERE A.START_DATE_TIME
<= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
AND A.END_DATE_TIME
>= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
AND
(
C.DISPLAY_TEXT LIKE '%VIP%'
OR C.DISPLAY_TEXT LIKE '%임산부%'
)
AND TRIM(A.CAR_TYPE) IN
(
'차종',
'화물차',
'들'
)
) T
/* =================================================================
[CN] 1순위 : 동일 CAR_NO의 정상 최빈 GUBUN
-----------------------------------------------------------------
처리 순서 :
1. VIP / 임산부가 아닌 정상 이력만 가져온다.
2. CAR_NO + GUBUN1 + GUBUN2별 사용 횟수를 센다.
3. 차량번호별 가장 많이 사용된 조합 하나를 선택한다.
4. 빈도가 같으면 가장 최근 사용값을 선택한다.
================================================================= */
LEFT JOIN
(
SELECT
COMPANY_ID,
CAR_NO,
GUBUN1,
GUBUN2,
CNT
FROM
(
SELECT
X.*,
ROW_NUMBER() OVER
(
PARTITION BY
COMPANY_ID,
CAR_NO
ORDER BY
CNT DESC,
LAST_DATE DESC NULLS LAST,
GUBUN1,
GUBUN2
) AS RN
FROM
(
/* -----------------------------------------------------
동일 차량번호에서 각 GUBUN 조합 사용 횟수 계산
----------------------------------------------------- */
SELECT
H.COMPANY_ID,
TRIM(H.CAR_NO) AS CAR_NO,
H.GUBUN1,
H.GUBUN2,
COUNT(*) AS CNT,
/* 빈도 동률 시 최신값 판단 */
MAX
(
NVL
(
H.LAST_UPDATE_DATE,
NVL
(
H.FIRST_INSERT_DATE,
H.START_DATE_TIME
)
)
) AS LAST_DATE
FROM TB_SERVICE_INFO H
/* 현재 유효한 GUBUN 조합만 정답 후보로 사용 */
JOIN TB_MEMBERTYPE_INFO M
ON H.COMPANY_ID = M.COMPANY_ID
AND H.GUBUN1 = M.DIVISION_ID
AND H.GUBUN2 = M.GROUP_ID
/* VIP / 임산부 이력은 정답 후보에서 제외 */
WHERE
M.DISPLAY_TEXT IS NULL
OR
(
M.DISPLAY_TEXT NOT LIKE '%VIP%'
AND M.DISPLAY_TEXT NOT LIKE '%임산부%'
)
GROUP BY
H.COMPANY_ID,
TRIM(H.CAR_NO),
H.GUBUN1,
H.GUBUN2
) X
)
/* 차량번호별 1등만 남김 */
WHERE RN = 1
) CN
ON T.COMPANY_ID = CN.COMPANY_ID
AND T.CAR_NO = CN.CAR_NO
/* =================================================================
[CT] 2순위 : 동일 CAR_TYPE의 정상 최빈 GUBUN
-----------------------------------------------------------------
이 JOIN은 같은 CAR_NO의 정상 이력이 없을 때 사용할 후보를
미리 계산해 둔다.
처리 순서 :
1. 정상 이력만 가져온다.
2. CAR_TYPE = CAR_NO 데이터는 제외한다.
3. 여러 차량에서 공통적으로 사용되는 CAR_TYPE만 허용한다.
4. CAR_TYPE + GUBUN1 + GUBUN2별 빈도를 계산한다.
5. CAR_TYPE별 가장 많이 사용된 조합 하나를 선택한다.
================================================================= */
LEFT JOIN
(
SELECT
COMPANY_ID,
CAR_TYPE,
GUBUN1,
GUBUN2,
CNT
FROM
(
SELECT
X.*,
ROW_NUMBER() OVER
(
PARTITION BY
COMPANY_ID,
CAR_TYPE
ORDER BY
CNT DESC,
LAST_DATE DESC NULLS LAST,
GUBUN1,
GUBUN2
) AS RN
FROM
(
/* -----------------------------------------------------
CAR_TYPE별 각 GUBUN 조합 사용 횟수 계산
----------------------------------------------------- */
SELECT
H.COMPANY_ID,
TRIM(H.CAR_TYPE) AS CAR_TYPE,
H.GUBUN1,
H.GUBUN2,
COUNT(*) AS CNT,
MAX
(
NVL
(
H.LAST_UPDATE_DATE,
NVL
(
H.FIRST_INSERT_DATE,
H.START_DATE_TIME
)
)
) AS LAST_DATE
FROM TB_SERVICE_INFO H
/* -----------------------------------------------------
현재 MEMBER TYPE에 존재하는 정상 GUBUN만 사용
----------------------------------------------------- */
JOIN TB_MEMBERTYPE_INFO M
ON H.COMPANY_ID = M.COMPANY_ID
AND H.GUBUN1 = M.DIVISION_ID
AND H.GUBUN2 = M.GROUP_ID
/* -----------------------------------------------------
정상적인 공통 CAR_TYPE 목록
조건 :
- CAR_TYPE IS NOT NULL
- CAR_TYPE <> CAR_NO
- 서로 다른 차량 2대 이상에서 사용
----------------------------------------------------- */
JOIN
(
SELECT
COMPANY_ID,
TRIM(CAR_TYPE) AS CAR_TYPE
FROM TB_SERVICE_INFO
WHERE CAR_TYPE IS NOT NULL
/* 차종 칸에 차량번호 복붙한 행 제외 */
AND TRIM(CAR_TYPE) <> TRIM(CAR_NO)
GROUP BY
COMPANY_ID,
TRIM(CAR_TYPE)
/* 한 차량에만 몰려 있는 차종값 제외 */
HAVING
COUNT(DISTINCT TRIM(CAR_NO)) > 1
) VALID_TYPE
ON H.COMPANY_ID = VALID_TYPE.COMPANY_ID
AND TRIM(H.CAR_TYPE) = VALID_TYPE.CAR_TYPE
WHERE H.CAR_TYPE IS NOT NULL
/* CAR_TYPE에 CAR_NO를 넣은 데이터 재차 제외 */
AND TRIM(H.CAR_TYPE) <> TRIM(H.CAR_NO)
/* VIP / 임산부 이력은 정답 후보에서 제외 */
AND
(
M.DISPLAY_TEXT IS NULL
OR
(
M.DISPLAY_TEXT NOT LIKE '%VIP%'
AND M.DISPLAY_TEXT NOT LIKE '%임산부%'
)
)
GROUP BY
H.COMPANY_ID,
TRIM(H.CAR_TYPE),
H.GUBUN1,
H.GUBUN2
) X
)
/* CAR_TYPE별 가장 많이 사용된 값 하나만 남김 */
WHERE RN = 1
) CT
ON T.COMPANY_ID = CT.COMPANY_ID
AND T.CAR_TYPE = CT.CAR_TYPE
/* =================================================================
[TU] CAR_TYPE 신뢰도 확인용
-----------------------------------------------------------------
검증 결과 화면에서
"이 CAR_TYPE이 실제 몇 대의 차량에서 사용됐는가?"
를 보여주기 위한 통계다.
CAR_TYPE = CAR_NO인 명백한 입력오류는 계산에서 제외한다.
================================================================= */
LEFT JOIN
(
SELECT
COMPANY_ID,
TRIM(CAR_TYPE) AS CAR_TYPE,
COUNT(DISTINCT TRIM(CAR_NO)) AS CAR_NO_CNT
FROM TB_SERVICE_INFO
WHERE CAR_TYPE IS NOT NULL
/* 차종 칸에 차량번호를 입력한 데이터 제외 */
AND TRIM(CAR_TYPE) <> TRIM(CAR_NO)
GROUP BY
COMPANY_ID,
TRIM(CAR_TYPE)
) TU
ON T.COMPANY_ID = TU.COMPANY_ID
AND T.CAR_TYPE = TU.CAR_TYPE
) R
/* =====================================================================
[NEW_C] 변경 후 DISPLAY_TEXT 조회
---------------------------------------------------------------------
최종적으로 결정된 NEW_GUBUN1 / NEW_GUBUN2가
실제 어떤 MEMBER TYPE인지 사람이 확인할 수 있게 한다.
===================================================================== */
LEFT JOIN TB_MEMBERTYPE_INFO NEW_C
ON R.COMPANY_ID = NEW_C.COMPANY_ID
AND R.NEW_GUBUN1 = NEW_C.DIVISION_ID
AND R.NEW_GUBUN2 = NEW_C.GROUP_ID
ORDER BY
UPDATE_STATUS,
R.FIX_BY,
R.COMPANY_ID,
R.CAR_NO;
---
4. 주니어용 — CTE 없는 실제 UPDATE 한방쿼리
이것도 위 검증 쿼리와 동일한 판정법입니다.
MERGE INTO TB_SERVICE_INFO A
USING
(
SELECT
R.RID,
R.NEW_GUBUN1,
R.NEW_GUBUN2
FROM
(
/* =================================================================
[R] 수정 대상별 최종 변경값 결정
-----------------------------------------------------------------
우선순위 :
1. 같은 차량번호(CN)
2. 같은 차종(CT)
================================================================= */
SELECT
T.RID,
T.OLD_GUBUN1,
T.OLD_GUBUN2,
/* -------------------------------------------------------------
CAR_NO 후보가 있으면 그것을 우선 사용
------------------------------------------------------------- */
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.GUBUN1
ELSE CT.GUBUN1
END AS NEW_GUBUN1,
CASE
WHEN CN.CAR_NO IS NOT NULL
THEN CN.GUBUN2
ELSE CT.GUBUN2
END AS NEW_GUBUN2
FROM
(
/* =============================================================
[T] 실제 수정 대상
-------------------------------------------------------------
조건 :
- 현재 출입 가능
- DISPLAY_TEXT가 VIP 또는 임산부
- CAR_TYPE이 '차종', '화물차', '들'
MEMBER TYPE 연결은 반드시
COMPANY_ID
+ GUBUN1
+ GUBUN2
조합으로 한다.
============================================================= */
SELECT
A.ROWID AS RID,
A.COMPANY_ID,
TRIM(A.CAR_NO) AS CAR_NO,
TRIM(A.CAR_TYPE) AS CAR_TYPE,
A.GUBUN1 AS OLD_GUBUN1,
A.GUBUN2 AS OLD_GUBUN2
FROM TB_SERVICE_INFO A
JOIN TB_MEMBERTYPE_INFO C
ON A.COMPANY_ID = C.COMPANY_ID
AND A.GUBUN1 = C.DIVISION_ID
AND A.GUBUN2 = C.GROUP_ID
WHERE A.START_DATE_TIME
<= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
AND A.END_DATE_TIME
>= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
AND
(
C.DISPLAY_TEXT LIKE '%VIP%'
OR C.DISPLAY_TEXT LIKE '%임산부%'
)
AND TRIM(A.CAR_TYPE) IN
(
'차종',
'화물차',
'들'
)
) T
/* =================================================================
[CN] 1순위 : 동일 CAR_NO의 가장 많이 사용된 정상 GUBUN
-----------------------------------------------------------------
VIP / 임산부를 제외한 과거 이력에서
차량번호별 최빈 GUBUN1/GUBUN2 한 건을 선택한다.
================================================================= */
LEFT JOIN
(
SELECT
COMPANY_ID,
CAR_NO,
GUBUN1,
GUBUN2
FROM
(
SELECT
X.*,
ROW_NUMBER() OVER
(
PARTITION BY
COMPANY_ID,
CAR_NO
ORDER BY
CNT DESC,
LAST_DATE DESC NULLS LAST,
GUBUN1,
GUBUN2
) AS RN
FROM
(
/* -----------------------------------------------------
차량번호 + GUBUN 조합별 빈도 계산
----------------------------------------------------- */
SELECT
H.COMPANY_ID,
TRIM(H.CAR_NO) AS CAR_NO,
H.GUBUN1,
H.GUBUN2,
COUNT(*) AS CNT,
MAX
(
NVL
(
H.LAST_UPDATE_DATE,
NVL
(
H.FIRST_INSERT_DATE,
H.START_DATE_TIME
)
)
) AS LAST_DATE
FROM TB_SERVICE_INFO H
JOIN TB_MEMBERTYPE_INFO M
ON H.COMPANY_ID = M.COMPANY_ID
AND H.GUBUN1 = M.DIVISION_ID
AND H.GUBUN2 = M.GROUP_ID
/* 문제값인 VIP / 임산부 이력은 정답 후보에서 제외 */
WHERE
M.DISPLAY_TEXT IS NULL
OR
(
M.DISPLAY_TEXT NOT LIKE '%VIP%'
AND M.DISPLAY_TEXT NOT LIKE '%임산부%'
)
GROUP BY
H.COMPANY_ID,
TRIM(H.CAR_NO),
H.GUBUN1,
H.GUBUN2
) X
)
/* 차량번호마다 가장 신뢰할 수 있는 값 한 개 */
WHERE RN = 1
) CN
ON T.COMPANY_ID = CN.COMPANY_ID
AND T.CAR_NO = CN.CAR_NO
/* =================================================================
[CT] 2순위 : 동일 CAR_TYPE의 가장 많이 사용된 정상 GUBUN
-----------------------------------------------------------------
같은 차량번호 이력이 없을 때 사용할 후보.
제외 규칙 :
1. CAR_TYPE = CAR_NO
2. 해당 CAR_TYPE이 한 차량에서만 사용됨
3. VIP / 임산부 이력
================================================================= */
LEFT JOIN
(
SELECT
COMPANY_ID,
CAR_TYPE,
GUBUN1,
GUBUN2
FROM
(
SELECT
X.*,
ROW_NUMBER() OVER
(
PARTITION BY
COMPANY_ID,
CAR_TYPE
ORDER BY
CNT DESC,
LAST_DATE DESC NULLS LAST,
GUBUN1,
GUBUN2
) AS RN
FROM
(
/* -----------------------------------------------------
CAR_TYPE + GUBUN 조합별 빈도 계산
----------------------------------------------------- */
SELECT
H.COMPANY_ID,
TRIM(H.CAR_TYPE) AS CAR_TYPE,
H.GUBUN1,
H.GUBUN2,
COUNT(*) AS CNT,
MAX
(
NVL
(
H.LAST_UPDATE_DATE,
NVL
(
H.FIRST_INSERT_DATE,
H.START_DATE_TIME
)
)
) AS LAST_DATE
FROM TB_SERVICE_INFO H
/* -----------------------------------------------------
현재 존재하는 정상 MEMBER TYPE만 사용
----------------------------------------------------- */
JOIN TB_MEMBERTYPE_INFO M
ON H.COMPANY_ID = M.COMPANY_ID
AND H.GUBUN1 = M.DIVISION_ID
AND H.GUBUN2 = M.GROUP_ID
/* -----------------------------------------------------
신뢰할 수 있는 CAR_TYPE 목록
- CAR_TYPE = CAR_NO 제외
- 서로 다른 차량 2대 이상이 사용한 값만 허용
----------------------------------------------------- */
JOIN
(
SELECT
COMPANY_ID,
TRIM(CAR_TYPE) AS CAR_TYPE
FROM TB_SERVICE_INFO
WHERE CAR_TYPE IS NOT NULL
/* 차종 자리에 차량번호 입력한 행 제외 */
AND TRIM(CAR_TYPE) <> TRIM(CAR_NO)
GROUP BY
COMPANY_ID,
TRIM(CAR_TYPE)
/* 한 차량에만 몰려 있는 CAR_TYPE 제외 */
HAVING
COUNT(DISTINCT TRIM(CAR_NO)) > 1
) VALID_TYPE
ON H.COMPANY_ID = VALID_TYPE.COMPANY_ID
AND TRIM(H.CAR_TYPE) = VALID_TYPE.CAR_TYPE
WHERE H.CAR_TYPE IS NOT NULL
/* CAR_TYPE에 차량번호 복붙 데이터 제외 */
AND TRIM(H.CAR_TYPE) <> TRIM(H.CAR_NO)
/* VIP / 임산부 이력 제외 */
AND
(
M.DISPLAY_TEXT IS NULL
OR
(
M.DISPLAY_TEXT NOT LIKE '%VIP%'
AND M.DISPLAY_TEXT NOT LIKE '%임산부%'
)
)
GROUP BY
H.COMPANY_ID,
TRIM(H.CAR_TYPE),
H.GUBUN1,
H.GUBUN2
) X
)
/* CAR_TYPE마다 최빈값 한 개 */
WHERE RN = 1
) CT
ON T.COMPANY_ID = CT.COMPANY_ID
AND T.CAR_TYPE = CT.CAR_TYPE
) R
/* =================================================================
실제 변경할 행만 MERGE에 전달
-----------------------------------------------------------------
1. NEW_GUBUN1 / NEW_GUBUN2가 존재해야 한다.
2. 현재값과 실제로 달라야 한다.
따라서
SKIP
NO_CHANGE
행은 UPDATE하지 않는다.
================================================================= */
WHERE R.NEW_GUBUN1 IS NOT NULL
AND R.NEW_GUBUN2 IS NOT NULL
AND
(
R.OLD_GUBUN1 <> R.NEW_GUBUN1
OR R.OLD_GUBUN2 <> R.NEW_GUBUN2
)
) S
/* =====================================================================
ROWID로 실제 수정 대상과 정확히 연결
===================================================================== */
ON
(
A.ROWID = S.RID
)
/* =====================================================================
최종 UPDATE
---------------------------------------------------------------------
GUBUN1 / GUBUN2만 수정.
===================================================================== */
WHEN MATCHED THEN
UPDATE SET
A.GUBUN1 = S.NEW_GUBUN1,
A.GUBUN2 = S.NEW_GUBUN2;
실제 작업 순서
저라면 운영에서는 딱 이 순서로 합니다.
-- 1.
-- CTE 검증용 또는 주니어용 검증 쿼리 실행
-- 2.
-- 특히 아래 확인
-- FIX_BY
-- MATCH_COUNT
-- CAR_TYPE_CAR_COUNT
-- OLD_DISPLAY_TEXT
-- NEW_DISPLAY_TEXT
-- UPDATE_STATUS
-- 3.
-- UPDATE_STATUS = 'UPDATE' 건만 눈으로 확인
-- 4.
-- MERGE 실행
-- 5.
-- 다시 검증 SELECT
-- 6.
COMMIT;
-- 이상하면 COMMIT 전에
-- ROLLBACK;
한 가지 중요한 설계 포인트는 CAR_NO 기준 보정에서는 CAR_TYPE = CAR_NO 이력을 버리지 않았다는 것입니다. 예를 들어 사용자가 CAR_TYPE만 차량번호로 잘못 적었더라도 그 행의 GUBUN1/GUBUN2는 정상일 수 있으므로, 같은 CAR_NO의 과거 GUBUN을 찾는 데는 그 이력을 사용할 수 있습니다.
반대로 CAR_TYPE을 근거로 다른 차량까지 추론할 때만 CAR_TYPE = CAR_NO를 확실하게 제거했습니다. 이게 지금 설명해주신 실제 데이터 오류 형태에는 가장 맞는 처리입니다.
