지점코드 | 상품코드 | 거래일자 | 금액 | 이자 | |||
A001 | S001 | 20231001 | 100000 | 1000 | |||
B001 | S002 | 20231002 | 10000 | 100 | |||
C001 | S002 | 20231001 | 45000 | 450 | |||
OLD | A001 | S001 | 20231001 | 100000 | 1000 | 1 | |
NEW | A001 | S002 | 20231001 | 100000 | 1000 | 1 | |
TRUE | FALSE | TRUE | TRUE | TRUE | 1 | <<==FALSE 개수 | |
OLD | B001 | S002 | 20231002 | 10000 | 100 | 3 | |
NEW | B001 | S003 | 20231002 | 20000 | 200 | 3 | |
TRUE | FALSE | TRUE | FALSE | FALSE | 3 | <<==FALSE 개수 | |
OLD | C001 | S002 | 20231001 | 45000 | 450 | 2 | |
NEW | C001 | S002 | 20231001 | 46000 | 460 | 2 | |
TRUE | TRUE | TRUE | FALSE | FALSE | 2 | <<==FALSE 개수 |
오라클 19C 엑사 입니다.
지난번에 올렸던 내용에 약간 추가 하여 질문을 합니다. 위 관련 답을 주셔서 잘 활용을 하고 있었는 데 검증 대상이 약 5억 건중 20만건 정도를 확인 해야 하는데
grouping 인 부분만 (오류갯수부분) 나와서 오류가 있을 경우 3줄이 모두 나와야 하는 경우가 필요 합니다.
(with 나 view로 한번 감싸서 오류가 있는 모든 건을 발췌 하고 싶습니다.)
제가 혼자서 해보려고 이것 저것 해 봤으나 시간만 허비 했네요 위 내용도 바쁘시더라도 답변 부탁드립니다.
제가 맨땅에 쿼리 작성해 답변 드린 것처럼
테이블명/컬럼명 간략하게 변경하고 컬럼 개수 좀 줄여서 쿼리 올려주시면 됩니다.
제가 답변 드린 쿼리와 상황이 다른 부분에 대한 상세 설명도 부탁드립니다.
1. 테이블이 두개인지? 아니면 테이블 하나에 old 와 new 가 다 있는지? 어떻게 구별하는지?
2. old 와 new 의 연결고리가 있는지? 내가 pk 라고 생각한 부분이 연결고리가 아니라면? 무었이 연결고리인지?
3. old 와 new 는 항상 짝을 이루는지? 한쪽에만 자료가 있는 경우는 없는지?
4. 컬럼 값은 항상 존재하는지? NULL 이 있는 자료는 없는지?
WITH TAB_OLD AS (
SELECT /*+ FULL(A) PARALLEL(16)*/
ITEM_DV_NM3 AS A1 ,
REGEXP_SUBSTR(BB1,'[^ ]+',1,2) AS BB1, BB2, BB3, BB4, BB5
FROM AAA_OLD A
WHERE 1=1
AND REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,2) = 'EDM1'
AND REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,3)||'_'||REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,4) = 'R_89'
)
, TAB_NEW AS (
SELECT /*+ FULL(A) PARALLEL(16)*/
ITEM_DV_NM3 AS A1 ,
REGEXP_SUBSTR(BB1,'[^ ]+',1,2) AS BB1, BB2, BB3, BB4, BB5
FROM AAA_NEW A
WHERE 1=1
AND SUBSTR(REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,4),1,4) = 'EDM1'
AND REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,1)||'_'||REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,2) = 'R_89'
)
SELECT PK, GB
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB1) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB1) AS BB1
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB2) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB2) AS BB2
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB3) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB3) AS BB3
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB4) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB4) AS BB4
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB5) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB5) AS BB5
, DECODE(GROUPING(GB), 1,
CASE WHEN COUNT(DISTINCT BB1) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT BB2) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT BB3) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT BB4) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT BB5) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
) GAP
FROM (
SELECT DENSE_RANK() OVER (ORDER BY BB1, BB3, BB4, BB5) PK, GB, BB1, BB2, BB3, BB4, BB5
FROM (
SELECT A1 GB, BB1 *1 AS BB1, BB2, BB3, BB4, BB5 FROM TAB_OLD
UNION ALL
SELECT A1 GB, BB1 *1 AS BB1, BB2, BB3, BB4, BB5 FROM TAB_NEW
)
)
WHERE 1=1
GROUP BY PK, ROLLUP((GB, BB1, BB2, BB3, BB4, BB5))
ORDER BY PK, GB NULLS LAST
-----------------------------------------------------------------------------------------------------------------
1. 테이블이 두개인지? 아니면 테이블 하나에 old 와 new 가 다 있는지? 어떻게 구별하는지?
==> 테이블은 2개 입니다.거의 유사 하나 아주 일부 다른지만 제가 거의 같게 만들어(정규식 활용) 비교 했습니다.
2. old 와 new 의 연결고리가 있는지? 내가 pk 라고 생각한 부분이 연결고리가 아니라면? 무었이 연결고리인지?
==> 실제로는 연결고리가 있으나 테이블 마다 PK가 모두 달라서 임의로 DENSE_RANK() OVER (ORDER BY pk 컬럼 나열) 만들어서 비교 했습니다.
3. old 와 new 는 항상 짝을 이루는지? 한쪽에만 자료가 있는 경우는 없는지?
==> 항상 짝을 이루어야 합니다. 짝을 이루지 않았거나 값이 다른 경우를 모두 찾아야 합니다.(이건 기존에 조언 해 주신 SQL로 거의 구현 된 듯 함.)
4. 컬럼 값은 항상 존재하는지? NULL 이 있는 자료는 없는지?
==> 이것도 OLD에 값이 있으면 같은 값이 있으면 되고 값이 없으면 없어야 합니다.
▶▶ 결국은 OLD TABLE 에 있는 ROW가 정확히 NEW TABLE에 옮겨 졌는지( count & value)를 증명(증적)을 해야 하는 TASK 입니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | WITH tab_old AS ( SELECT 'OLD' a1, 'A001' b1, 'S001' b2, '20231001' b3, 100000 b4, 1000 b5 FROM dual UNION ALL SELECT 'OLD' , 'B001' , 'S002' , '20231002' , 10000, 100 FROM dual UNION ALL SELECT 'OLD' , 'C001' , 'S002' , '20231001' , 45000, 450 FROM dual UNION ALL SELECT 'OLD' , 'D001' , 'S002' , '20231001' , null , 450 FROM dual UNION ALL SELECT 'OLD' , 'E001' , 'S002' , '20231001' , null , 450 FROM dual ) , tab_new AS ( SELECT 'NEW' a1, 'A001' b1, 'S002' b2, '20231001' b3, 100000 b4, 1000 b5 FROM dual UNION ALL SELECT 'NEW' , 'B001' , 'S003' , '20231002' , 20000, 200 FROM dual UNION ALL SELECT 'NEW' , 'C001' , 'S002' , '20231001' , 46000, 460 FROM dual UNION ALL SELECT 'NEW' , 'D001' , 'S002' , '20231001' , null , null FROM dual UNION ALL SELECT 'NEW' , 'F001' , 'S002' , '20231001' , null , 450 FROM dual ) SELECT pk , gb , DECODE( GROUPING (gb), 1, CASE WHEN COUNT ( DISTINCT b1) = 2 OR COUNT (*) = 1 OR COUNT (b1) = 1 THEN 'FALSE' ELSE 'TRUE' END , b1) b1 , DECODE( GROUPING (gb), 1, CASE WHEN COUNT ( DISTINCT b2) = 2 OR COUNT (*) = 1 OR COUNT (b2) = 1 THEN 'FALSE' ELSE 'TRUE' END , b2) b2 , DECODE( GROUPING (gb), 1, CASE WHEN COUNT ( DISTINCT b3) = 2 OR COUNT (*) = 1 OR COUNT (b3) = 1 THEN 'FALSE' ELSE 'TRUE' END , b3) b3 , DECODE( GROUPING (gb), 1, CASE WHEN COUNT ( DISTINCT b4) = 2 OR COUNT (*) = 1 OR COUNT (b4) = 1 THEN 'FALSE' ELSE 'TRUE' END , b4) b4 , DECODE( GROUPING (gb), 1, CASE WHEN COUNT ( DISTINCT b5) = 2 OR COUNT (*) = 1 OR COUNT (b5) = 1 THEN 'FALSE' ELSE 'TRUE' END , b5) b5 , MIN ( DECODE( GROUPING (gb), 1, CASE WHEN COUNT ( DISTINCT b1) = 2 OR COUNT (*) = 1 OR COUNT (b1) = 1 THEN 1 ELSE 0 END + CASE WHEN COUNT ( DISTINCT b2) = 2 OR COUNT (*) = 1 OR COUNT (b2) = 1 THEN 1 ELSE 0 END + CASE WHEN COUNT ( DISTINCT b3) = 2 OR COUNT (*) = 1 OR COUNT (b3) = 1 THEN 1 ELSE 0 END + CASE WHEN COUNT ( DISTINCT b4) = 2 OR COUNT (*) = 1 OR COUNT (b4) = 1 THEN 1 ELSE 0 END + CASE WHEN COUNT ( DISTINCT b5) = 2 OR COUNT (*) = 1 OR COUNT (b5) = 1 THEN 1 ELSE 0 END ) ) OVER(PARTITION BY pk) gap FROM ( SELECT gb , DENSE_RANK() OVER( ORDER BY b1, b3) pk , b1, b2, b3, b4, b5 FROM ( SELECT a1 gb, b1, b2, b3, b4, b5 FROM tab_old UNION ALL SELECT a1 gb, b1, b2, b3, b4, b5 FROM tab_new ) ) GROUP BY pk, ROLLUP ((gb, b1, b2, b3, b4, b5)) ORDER BY pk, gb DESC NULLS LAST ; |
1. 샘플 데이터 보강
- null 데이터 추가
- 한쪽에만 있는 데이터 추가
2. 쿼리 설명
- null 데이터 감안하여 COUNT(컬럼) 조건 추가
- 모든 줄에 GAP 표시하기 위해 분석함수 적용
- 샘플 자료에서는 (b1, b3) 를 PK 라고 가정하고 DENSE_RANK 적용
개인적인 일로 지난주 출근을 못하고 오늘 출근해서 적용 후 확인 해 봤 습니다.
MIN(
DECODE(GROUPING(gb), 1, CASE WHEN COUNT(DISTINCT b1) = 2 OR COUNT(*) = 1 OR COUNT(b1) = 1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT b2) = 2 OR COUNT(*) = 1 OR COUNT(b2) = 1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT b3) = 2 OR COUNT(*) = 1 OR COUNT(b3) = 1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT b4) = 2 OR COUNT(*) = 1 OR COUNT(b4) = 1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT b5) = 2 OR COUNT(*) = 1 OR COUNT(b5) = 1 THEN 1 ELSE 0 END
) ) OVER(PARTITION BY pk) gap
작은 차이가 큰 결과를 만든다는 예전 광고 카피가 생각이 났네요
정말 작은 차이 같은데 매우 놀랐습니다. 제가 해야 하는 일의 생산성이 엄청 좋아 졌습니다.
넘 , 감사합니다.
PS. 건수가 많은 경우는 파티션 별로 나눠서 작업을 해야 하지만 그걸 감안 해도 몇배는 작업이 빨라 졌네요 넘 신나네요