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

데이터 연동 테이블인데 변동이 있는 값만 추출하고싶습니다 minus 이용

by 쿼리초보 [SQL Query] [2018.08.09 15:50:20]

데이터 연동 테이블인데 변동이 있는 값만 추출하고싶습니다 minus 이용

새로운 데이터 ( 키 , 벨류 )
a 2
b 1
c 2
e 1

기존 데이터 ( 키 , 벨류 )
a 1
b 1
c 1
d 1

추출하고싶은 데이터 ( 키 , 벨류 , 상태 )
a 2 u(업데이트)
c 2 u(업데이트)
d 1 d(딜리트)
e 1 i(인서트)

위처럼 새로운 데이터 minus 기존데이터 하면

추출하고싶은 데이터가 나올꺼같은데..

삭제된 d 1 은 추출이 안되네요

그리고 상태도 뽑을수 없고요

쿼리로 만들어보면

WITH TA_DATA AS (
SELECT 'a' key , '1' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '1' val from dual
UNION ALL
SELECT 'd' key , '1' val from dual
) , NEW_DATA AS (
SELECT 'a' key , '2' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '2' val from dual
UNION ALL
SELECT 'e' key , '1' val from dual
)
SELECT * FROM NEW_DATA
MINUS
SELECT * FROM TA_DATA

결과

key val
a 2
c 2
e 1

입니다

원하는 결과는

key val status
a 2 u
c 2 u
d 1 d
e 1 i

이렇게 뽑고싶은게 이거 가능한건가요 ㅠㅠ

도움 부탁드립니다..

by 우리집아찌 [2018.08.09 16:42:37]

WITH TA_DATA AS (
SELECT 'a' key , '1' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '1' val from dual
UNION ALL
SELECT 'd' key , '1' val from dual
) , NEW_DATA AS (
SELECT 'a' key , '2' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '2' val from dual
UNION ALL
SELECT 'e' key , '1' val from dual
)

 SELECT NVL(A.KEY,B.KEY) KEY 
      , NVL(A.VAL,B.VAL) VAL 
      , CASE WHEN B.KEY IS NULL  THEN 'I'
             WHEN A.KEY IS NULL  THEN 'D'
             WHEN A.VAL != B.VAL THEN 'U'
        END STATUS
  FROM NEW_DATA A
       FULL OUTER JOIN
       TA_DATA B 
    ON A.KEY = B.KEY
 WHERE  CASE WHEN B.KEY IS NULL  THEN 'I'
             WHEN A.KEY IS NULL  THEN 'D'
             WHEN A.VAL != B.VAL THEN 'U'
        END IS NOT NULL
 ORDER BY 1

by 잠만보 [2018.08.09 16:52:50]

WITH TA_DATA AS (
SELECT 'a' key , '1' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '1' val from dual
UNION ALL
SELECT 'd' key , '1' val from dual
) , NEW_DATA AS (
SELECT 'a' key , '2' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '2' val from dual
UNION ALL
SELECT 'e' key , '1' val from dual
)

SELECT TD.KEY,
       TD.VAL,
       
       ND.KEY N_KEY,
       ND.VAL N_VAL,
       
       CASE WHEN TD.VAL = ND.VAL THEN 'EQUALS'
            WHEN TD.VAL IS NULL AND ND.VAL IS NOT NULL THEN 'INSERT'
            WHEN TD.VAL IS NOT NULL AND ND.VAL IS NULL THEN 'DELETE'
            WHEN TD.VAL != ND.VAL THEN 'UPDATE'
       END STATUS
  FROM TA_DATA TD 
       FULL OUTER JOIN NEW_DATA ND
       ON (TD.KEY = ND.KEY)
 ORDER BY NVL(TD.KEY, ND.KEY)

by 쿼리초보 [2018.08.09 17:02:16]

두분다 답변 감사합니다.... 근데 val 같은 컬럼이 매우 많거든요

WHEN A.VAL != B.VAL THEN 'U'

이런게 비교할경우 쿼리가 너무 길어질꺼 같은데...

NEW_DATA

MINUS

TA_DATA

로해서.... 업데이트나 인서트가 일어날 컬럼을 뽑고

(물론 본문처럼 I 인지 U 인지 추출은 못할꺼같긴 합니다.)

TA_DATA

MINUS

NEW_DATA

로 지워진 데이터를 뽑는 방법은 어떨까요 ㅠㅠ..

by 우리집아찌 [2018.08.09 17:43:41]

무슨 방법을 쓰던 비교컬럼이 많으면 길어질것 같은데요.

by 쿼리초보 [2018.08.09 17:50:19]

NEW_DATA

MINUS

TA_DATA

로 할경우 변경이 일어난 컬럼만 나오고

TA_DATA

MINUS

NEW_DATA

삭제된 컬럼만 나오고

둘을 유니온 올 하면

i 인지 u 인지 구분은 못하지만

변경이 일어난 로우와

삭제할 로우를 뽑을수 있을꺼 같아서요...

by 쿼리초보 [2018.08.09 17:53:11]

결과

key val
a 2
c 2
e 1

반대로

결과

d ''

이런식으로 나온거 .. 유니온 올 하면

변경된 목록과 삭제ㄷ할 목록을 뽑을수 있을꺼같은데

어떨까요???

by 잠만보 [2018.08.09 17:55:40]

키는 하나 인건가요?

키만 비교하는 걸로 했습니다.

WITH TA_DATA AS (
SELECT 'a' key , '1' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '1' val from dual
UNION ALL
SELECT 'd' key , '1' val from dual
) , NEW_DATA AS (
SELECT 'a' key , '2' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '2' val from dual
UNION ALL
SELECT 'e' key , '1' val from dual
)

SELECT BASIS.KEY BASIS_KEY,
       TD.KEY,
       TD.VAL,
       ND.KEY N_KEY,
       ND.VAL N_VAL,
       CASE WHEN TD.KEY IS NOT NULL AND ND.KEY IS NULL THEN 'DELETE'
            WHEN TD.KEY IS NULL AND ND.KEY IS NOT NULL THEN 'INSERT'
            WHEN TD.KEY IS NOT NULL AND ND.KEY IS NOT NULL THEN 'UPDATE'
       END STATUS
  FROM (SELECT DISTINCT KEY
          FROM ((SELECT * FROM NEW_DATA
                  UNION ALL
                 SELECT * FROM TA_DATA
                )
                
                  MINUS
                
                (SELECT * FROM NEW_DATA
                  INTERSECT
                 SELECT * FROM TA_DATA
                )
               )
       ) BASIS,
       TA_DATA TD,
       NEW_DATA ND
 WHERE BASIS.KEY = TD.KEY(+)
   AND BASIS.KEY = ND.KEY(+)

by 쿼리초보 [2018.08.09 17:57:01]

댓글 감사합니다 .... 테스트 해보겠습니다 ^^

by 마농 [2018.08.10 10:46:50]

WITH ta_data AS
(
SELECT 'a' key, 1 val1, 'a' val2, sysdate val3 FROM dual
UNION ALL SELECT 'b', 1, 'a', sysdate FROM dual
UNION ALL SELECT 'c', 1, 'a', sysdate FROM dual
UNION ALL SELECT 'd', 1, 'a', sysdate FROM dual
)
, new_data AS
(
SELECT 'a' key, 2 val1, 'a' val2, sysdate val3 FROM dual
UNION ALL SELECT 'b', 1, 'a', sysdate FROM dual
UNION ALL SELECT 'c', 2, 'a', sysdate FROM dual
UNION ALL SELECT 'e', 1, 'a', sysdate FROM dual
)
SELECT a.*
     , NVL2(b.key, 'u', 'i') status
  FROM (SELECT * FROM new_data
         MINUS
        SELECT * FROM ta_data
        ) a
     , ta_data b
 WHERE a.key = b.key(+)
 UNION ALL
SELECT b.*
     , 'd' status
  FROM new_data a
     , ta_data b
 WHERE a.key(+) = b.key
   AND a.key IS NULL
 ORDER BY 1
;

댓글등록

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

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

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

데이터베이스 질문과 답변