꿈꾸는 개발자, DBA 커뮤니티 구루비

ROW별 COLUMN별 값 비교2

by 구사일생 [Oracle 기초] 대량테이블 N개의 컬럼 [2023.12.27 22:23:46]

	지점코드	상품코드	거래일자	금액	이자
	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로 한번 감싸서 오류가 있는 모든 건을 발췌 하고 싶습니다.)

제가 혼자서 해보려고 이것 저것 해 봤으나 시간만 허비 했네요 위 내용도 바쁘시더라도 답변 부탁드립니다.

by 마농 [2023.12.28 08:53:50]

적용하신 쿼리를 보여주세요.

by 구사일생 [2023.12.28 21:42:31]

헉 , 염치 없지만 답을 넘 기다렸나 봅니다. 살짝 당황 ~~

작성한 SQL 반출이 어렵지만 (중간 중간 지워서) 카메라로 몰래 찍어 와서 올리겠습니다.

by 마농 [2023.12.29 08:59:50]

제가 맨땅에 쿼리 작성해 답변 드린 것처럼
테이블명/컬럼명 간략하게 변경하고 컬럼 개수 좀 줄여서 쿼리 올려주시면 됩니다.
제가 답변 드린 쿼리와 상황이 다른 부분에 대한 상세 설명도 부탁드립니다.
1. 테이블이 두개인지? 아니면 테이블 하나에 old 와 new 가 다 있는지? 어떻게 구별하는지?
2. old 와 new 의 연결고리가 있는지? 내가 pk 라고 생각한 부분이 연결고리가 아니라면? 무었이 연결고리인지?
3. old 와 new 는 항상 짝을 이루는지? 한쪽에만 자료가 있는 경우는 없는지?
4. 컬럼 값은 항상 존재하는지? NULL 이 있는 자료는 없는지?

by 구사일생 [2023.12.29 20:27:35]

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 입니다.

by 마농 [2024.01.01 22:44: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 적용

by 구사일생 [2024.01.02 22:19:08]

역시 ~~~ 알려주신 내용을 내일 바로 적용 해 보겠습니다.

늘 드는 생각이지만 엄청 다른가 ??? 하는 바보같은 생각이 또 드네요

감사하게 받아 가서 적용해 보고 결과 올릴게요

암튼 또 이렇게 한수 배우 갑니다.

by 구사일생 [2024.01.08 21:43:10]

개인적인 일로 지난주 출근을 못하고 오늘 출근해서 적용 후 확인 해 봤 습니다.

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. 건수가 많은 경우는 파티션 별로 나눠서 작업을 해야 하지만 그걸 감안 해도 몇배는 작업이 빨라 졌네요 넘 신나네요

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

Rich Text Editor, commentnote

Editor toolbars

Press ALT 0 for help

◢Elements path

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변