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

데이터를 보고 결과를 일치 및 불일치 결과를 출력부탁드립니다.

by 허승호 [SQL Query] [2015.04.16 15:12:31]

자 아래 데이터를 보시면 이렇습니다.

GRP	SUB_GRP	NAME	EMAIL	PHONE
1	1	홍길동	hong1@kil.dong	010-7878-9999
1	2	홍길동
1	2	홍길동	hong1@daum.net
1	2	홍길동		010-7878-9999
1	2	홍길동	hong1@kil.dong
2	3	성춘향	sch@naver.com	123-456-7899
2	3	성춘향
2	4	성춘향	sch@korea.org
2	4	성춘향	sch@naver.com
2	5	성춘향
2	5	성춘향
2	5	성춘향
2	6	성춘향	sch@naver.com
2	6	성춘향	xtm@ybm.com
2	6	성춘향
3	7	이도령	ldr@daum.net	010-1111-2222
3	7	이도령		010-333-6666
3	7	이도령
3	8	이도령	mmo@korea.org
3	8	이도령		010-888-9999
3	8	이도령		010-555-7777
3	8	이도령	ldr2@daum.net

질문의 요지는 이렇습니다. NAME이라는 컬럼이 있습니다. 중복되는 이름이 여럿있지요

홍길동을 우선 기준으로 설명드립니다. 이름 좌측에 GRP와 SUB_GRP라는 컬럼이 있습니다.

GRP는 SUB_GRP의 그룹입니다.

즉 SUB, SUB_GRP내에서 홍길동이라는 사람이 동일인인지 아닌지를 메일과 전화번호로 판별하여 맞추어야 합니다.

결과는 아래와 같이 표시되어야 합니다.

GRP	SUB_GRP	NAME	EMAIL	PHONE	결과
1	1	홍길동	hong1@kil.dong	010-7878-9999	일치
1	2	홍길동			일치
1	2	홍길동	hong1@daum.net		일치
1	2	홍길동		010-7878-9999	일치
1	2	홍길동	hong1@kil.dong		일치
2	3	성춘향	sch@naver.com	123-456-7899	일치
2	3	성춘향			일치
2	4	성춘향	sch@korea.org		일치
2	4	성춘향	sch@naver.com		일치
2	5	성춘향			일치
2	5	성춘향			일치
2	5	성춘향			일치
2	6	성춘향	sch@naver.com		일치
2	6	성춘향	xtm@ybm.com		일치
2	6	성춘향			일치
3	7	이도령	ldr@daum.net	010-1111-2222	불일치
3	7	이도령		010-333-6666	불일치
3	7	이도령			불일치
3	8	이도령	mmo@korea.org		불일치
3	8	이도령		010-888-9999	불일치
3	8	이도령		010-555-7777	불일치
3	8	이도령	ldr2@daum.net		불일치

위의 홍길동과 성춘향은 SUB_GRP간에 메일, 전화번호 하나라도 맞으니 동일인으로 판단하여 일치가 표시되고

이도령은 SUB_GRP간에 이메일과 핸드폰이 맞는게 없으니 불일치로 뜹니다.

즉 (PARTITION BY GRP) 이되 COUNT(DISTINCT SUB_GRP) >= 2 일때 두 SUB_GRP끼리 메일과 핸드폰으로 비교해서 일치, 불일치인지 판단하여야 합니다.

머리가 안돌아가는군요 ㅜㅜ 도와주십시요.

by 마농 [2015.04.16 17:22:40]

WITH t AS
(
SELECT 1 grp, 1 sub_grp, '홍길동' name, 'hong1@kil.dong' email, '010-7878-9999' phone FROM dual
UNION ALL SELECT 1, 2, '홍길동', ''              , ''　 　         FROM dual
UNION ALL SELECT 1, 2, '홍길동', 'hong1@daum.net', ''　            FROM dual
UNION ALL SELECT 1, 2, '홍길동', ''　            , '010-7878-9999' FROM dual
UNION ALL SELECT 1, 2, '홍길동', 'hong1@kil.dong', '' 　           FROM dual
UNION ALL SELECT 2, 3, '성춘향', 'sch@naver.com' , '123-456-7899'  FROM dual
UNION ALL SELECT 2, 3, '성춘향', ''　 　         , ''              FROM dual
UNION ALL SELECT 2, 4, '성춘향', 'sch@korea.org' , '' 　           FROM dual
UNION ALL SELECT 2, 4, '성춘향', 'sch@naver.com' , '' 　           FROM dual
UNION ALL SELECT 2, 5, '성춘향', ''              , ''　            FROM dual
UNION ALL SELECT 2, 5, '성춘향', ''              , ''　 　         FROM dual
UNION ALL SELECT 2, 5, '성춘향', ''              , ''　            FROM dual
UNION ALL SELECT 2, 6, '성춘향', 'sch@naver.com' , '' 　           FROM dual
UNION ALL SELECT 2, 6, '성춘향', 'xtm@ybm.com' 　, ''              FROM dual
UNION ALL SELECT 2, 6, '성춘향', ''　 　         , ''              FROM dual
UNION ALL SELECT 3, 7, '이도령', 'ldr@daum.net'  , '010-1111-2222' FROM dual
UNION ALL SELECT 3, 7, '이도령', ''　            , '010-333-6666'  FROM dual
UNION ALL SELECT 3, 7, '이도령', ''　 　         , ''              FROM dual
UNION ALL SELECT 3, 8, '이도령', 'mmo@korea.org' , '' 　           FROM dual
UNION ALL SELECT 3, 8, '이도령', ''　            , '010-888-9999'  FROM dual
UNION ALL SELECT 3, 8, '이도령', ''　            , '010-555-7777'  FROM dual
UNION ALL SELECT 3, 8, '이도령', 'ldr2@daum.net' , '' 　           FROM dual
)
SELECT grp, sub_grp, name, email, phone
     , CASE WHEN COUNT(email) OVER(PARTITION BY grp)
              != COUNT(DISTINCT email) OVER(PARTITION BY grp)
              OR COUNT(phone) OVER(PARTITION BY grp)
              != COUNT(DISTINCT phone) OVER(PARTITION BY grp)
            THEN '일치' ELSE '불일치' END result
  FROM t
 ORDER BY grp, sub_grp
;

by 창조의날개 [2015.04.16 19:37:56]


WITH TT(GRP,	SUB_GRP,	NAME,	EMAIL,	PHONE) AS(
    SELECT 1 , 1 , '홍길동' , 'hong1@kil.dong' , '010-7878-9999'  FROM dual
    UNION ALL SELECT 1, 2, '홍길동', ''              , ''　 　         FROM dual
    UNION ALL SELECT 1, 2, '홍길동', 'hong1@daum.net', ''　            FROM dual
    UNION ALL SELECT 1, 2, '홍길동', ''　            , '010-7878-9999' FROM dual
    UNION ALL SELECT 1, 2, '홍길동', 'hong1@kil.dong', '' 　           FROM dual
    UNION ALL SELECT 2, 3, '성춘향', 'sch@naver.com' , '123-456-7899'  FROM dual
    UNION ALL SELECT 2, 3, '성춘향', ''　 　         , ''              FROM dual
    UNION ALL SELECT 2, 4, '성춘향', 'sch@korea.org' , '' 　           FROM dual
    UNION ALL SELECT 2, 4, '성춘향', 'sch@naver.com' , '' 　           FROM dual
    UNION ALL SELECT 2, 5, '성춘향', ''              , ''　            FROM dual
    UNION ALL SELECT 2, 5, '성춘향', ''              , ''　 　         FROM dual
    UNION ALL SELECT 2, 5, '성춘향', ''              , ''　            FROM dual
    UNION ALL SELECT 2, 6, '성춘향', 'sch@naver.com' , '' 　           FROM dual
    UNION ALL SELECT 2, 6, '성춘향', 'xtm@ybm.com' 　, ''              FROM dual
    UNION ALL SELECT 2, 6, '성춘향', ''　 　         , ''              FROM dual
    UNION ALL SELECT 3, 7, '이도령', 'ldr@daum.net'  , '010-1111-2222' FROM dual
    UNION ALL SELECT 3, 7, '이도령', ''　            , '010-333-6666'  FROM dual
    UNION ALL SELECT 3, 7, '이도령', ''　 　         , ''              FROM dual
    UNION ALL SELECT 3, 8, '이도령', 'mmo@korea.org' , '' 　           FROM dual
    UNION ALL SELECT 3, 8, '이도령', ''　            , '010-888-9999'  FROM dual
    UNION ALL SELECT 3, 8, '이도령', ''　            , '010-555-7777'  FROM dual
    UNION ALL SELECT 3, 8, '이도령', 'ldr2@daum.net' , '' 　           FROM dual
)
SELECT BB.* 
     , CASE WHEN MAX(REGEXP_COUNT(AA.CHK_EMAIL,BB.EMAIL)) OVER(PARTITION BY BB.GRP) > 1
              OR MAX(REGEXP_COUNT(AA.CHK_PHONE,BB.PHONE)) OVER(PARTITION BY BB.GRP) > 1
       THEN '일치' ELSE '불일치' END "결과"
FROM (SELECT GRP, WM_CONCAT(EMAIL) CHK_EMAIL, WM_CONCAT(PHONE) CHK_PHONE
      FROM TT GROUP BY GRP) AA
    , TT BB
WHERE AA.GRP = BB.GRP
ORDER BY 1,2
;

by 허승호 [2015.04.17 15:25:45]

감사합니다. 하룻밤 자고 나니 머리가 돌아가는군요. ㅎㅎ

답변 자료 덕분에  참고가 되어 해결되었나 봅니다. 




WITH t AS
(
SELECT 1 grp, 1 sub_grp, '홍길동' name, 'hong1@kil.dong' email, '010-7878-9999' phone FROM dual
UNION ALL SELECT 1, 2, '홍길동', ''              , ''　 　         FROM dual
UNION ALL SELECT 1, 2, '홍길동', 'hong1@daum.net', ''　            FROM dual
UNION ALL SELECT 1, 2, '홍길동', ''　            , '010-7878-9999' FROM dual
UNION ALL SELECT 1, 2, '홍길동', 'hong1@kil.dong', '' 　           FROM dual
UNION ALL SELECT 2, 3, '성춘향', 'sch@naver.com' , '123-456-7899'  FROM dual
UNION ALL SELECT 2, 3, '성춘향', ''　 　         , ''              FROM dual
UNION ALL SELECT 2, 4, '성춘향', 'sch@korea.org' , '' 　           FROM dual
UNION ALL SELECT 2, 4, '성춘향', 'sch@naver.com' , '' 　           FROM dual
UNION ALL SELECT 2, 5, '성춘향', ''              , ''　            FROM dual
UNION ALL SELECT 2, 5, '성춘향', ''              , ''　 　         FROM dual
UNION ALL SELECT 2, 5, '성춘향', ''              , ''　            FROM dual
UNION ALL SELECT 2, 6, '성춘향', 'sch@naver.com' , '' 　           FROM dual
UNION ALL SELECT 2, 6, '성춘향', 'xtm@ybm.com' 　, ''              FROM dual
UNION ALL SELECT 2, 6, '성춘향', ''　 　         , ''              FROM dual
UNION ALL SELECT 3, 7, '이도령', 'ldr@daum.net'  , '010-1111-2222' FROM dual
UNION ALL SELECT 3, 7, '이도령', ''　            , '010-333-6666'  FROM dual
UNION ALL SELECT 3, 7, '이도령', ''　 　         , ''              FROM dual
UNION ALL SELECT 3, 8, '이도령', 'mmo@korea.org' , '' 　           FROM dual
UNION ALL SELECT 3, 8, '이도령', ''　            , '010-888-9999'  FROM dual
UNION ALL SELECT 3, 8, '이도령', ''　            , '010-555-7777'  FROM dual
UNION ALL SELECT 3, 8, '이도령', 'ldr2@daum.net' , '' 　           FROM dual
)
SELECT X.*
	   ,DECODE (COUNT( (SELECT DECODE(COUNT(*),0,NULL,1) 
	              FROM T 
				 WHERE GRP = X.GRP 
				   AND SUB_GRP <> X.SUB_GRP 
				   AND (EMAIL = X.EMAIL OR  PHONE = X.PHONE))
			 ) OVER (PARTITION BY GRP) 
			 , 0 ,' 불일치'
			 ,'일치') AS COMPARE
  FROM T X

댓글등록

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

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

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

데이터베이스 질문과 답변