안녕하세요 join 관련해서 궁금한게 있습니다.
데이터
user_id regist_cd cnt
--------------------------------
test_1 B 2
test_2 A 16
test_2 B 21
test_3 A 2
test_4 A 1
코드 테이블
cd_id cd_value
------------------
A AAA
B BBB
C CCC
해당 두 테이블을 조인하여
user_id regist_cd cnt
--------------------------------
test_1 A 0
test_1 B 2
test_1 C 0
test_2 A 16
test_2 B 21
test_2 C 0
test_3 A 2
test_3 B 0
test_3 C 0
test_4 A 1
test_4 B 0
test_4 C 0
이런 결과를 만들어야 하는데요, outer join을 거니 아래와 같이 나왔습니다..
USER_ID REGIST_CD CNT
--------------------------------------
test_1 C 2
test_1 A 2
test_1 B 2
test_2 C 16
test_2 C 21
test_2 A 16
test_2 A 21
test_2 B 16
test_2 B 21
test_3 C 2
test_3 A 2
test_3 B 2
test_4 C 1
test_4 A 1
test_4 B 1
이런식으로 코드값과 count값이 중복되서 나오고 있습니다...
작성한 쿼리문입니다.
select user_id, cd_id, count(regist_cd) as cnt from data_table a right outer join code_table b on 1=1 group by user_id, cd_id, regist_cd
해결책이나 힌트를 부탁드립니다ㅠㅠ 감사합니다.
-- db2 에서 처리 oracle에서 보시려면 SYSIBM.SYSDUMMY1 ---> dual로 변경하시길
WITH a AS (
SELECT 'test_1' AS user_id, 'B' regist_cd, 2 cnt FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'test_2' AS user_id, 'A' regist_cd, 16 cnt FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'test_2' AS user_id, 'B' regist_cd, 21 cnt FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'test_3' AS user_id, 'A' regist_cd, 2 cnt FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'test_4' AS user_id, 'A' regist_cd, 1 cnt FROM SYSIBM.SYSDUMMY1
),
b AS (
SELECT 'AAA' AS cd_value, 'A' cd_id FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BBB' AS cd_value, 'B' cd_id FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CCC' AS cd_value, 'C' cd_id FROM SYSIBM.SYSDUMMY1
)
SELECT a.user_id
, b.cd_id
, sum(CASE WHEN b.cd_id = a.regist_cd THEN cnt ELSE 0 END) cnt
FROM a
INNER JOIN b
ON 1 = 1
GROUP BY a.user_id
, b.cd_id
ORDER BY 1
오라클의 경우 Partition Outer Join 을 지원하는데
티베로도 동일하게 동작되는지 확인해 보세요.
안될 경우엔 2번 방법 사용하세요.
참고 : http://gurubee.net/lecture/2204
WITH data_table AS ( SELECT 'test_1' user_id, 'B' regist_cd, 2 cnt FROM dual UNION ALL SELECT 'test_2', 'A', 16 FROM dual UNION ALL SELECT 'test_2', 'B', 21 FROM dual UNION ALL SELECT 'test_3', 'A', 2 FROM dual UNION ALL SELECT 'test_4', 'A', 1 FROM dual ) , code_table AS ( SELECT 'A' cd_id, 'AAA' cd_value FROM dual UNION ALL SELECT 'B', 'BBB' FROM dual UNION ALL SELECT 'C', 'CCC' FROM dual ) -- 1. Partition Outer Join SELECT d.user_id , c.cd_id , c.cd_value , NVL(d.cnt, 0) cnt FROM code_table c LEFT OUTER JOIN data_table d PARTITION BY (d.user_id) ON c.cd_id = d.regist_cd ORDER BY user_id, cd_id ; -- 2. Cross Join 으로 기준집합(유저 + 코드)을 만든 뒤 아우터 조인 SELECT u.user_id , c.cd_id , c.cd_value , NVL(d.cnt, 0) cnt FROM (SELECT DISTINCT user_id FROM data_table) u CROSS JOIN code_table c LEFT OUTER JOIN data_table d ON c.cd_id = d.regist_cd AND u.user_id = d.user_id ORDER BY user_id, cd_id ;