데이터베이스 질문과 답변
by 손님 [SQL Query] with full outer join [2010.05.18 11:29:06]
with 절과 full outer join을 처음 사용해 봤는데 왠지 잘 안되네요.고수님의 조언 부탁드립니다.
with절의 view
custkey --고객키
codetp --종류
name -- 이름
cnt -- 갯수
date as dt -- 날자
원하는 output은 아래와 같습니다.
코드별, 이름별, 날자별로 갯수를 sum해서 그 결과를 보여주는데요
코드별로 생성되는 레코드수는 각기 다릅니다. 그 코드패턴 세가지 list를 고객키로 묶어서 행으로 보여줘야 합니다.
이상한것은 with뷰를 full outer join한 WTB1, WTB2의 조인까지는 무리없이 원하는 대로 진행됩니다.
WTB1(codetp=1),WTB2(codetp=2)의 조인 결과를 WTB3(codetp=3)랑 묶으려고 하면 ora 00942에러가 뜹니다.(뷰가 존재하지 않는다는 메세지)
왜일까요? 현재는 전부 inline subquary로 치덕치덕 집어넣어서 돌리고 있습니다..TT
codetp=1 codetp=2 codetp=3
custkey name sum(cnt) date name sum(cnt) date name sum(cnt) date
1001 AAA 3 2010/03/03 DDD 9 2010/03/12 FFF 4 2010/03/12
AAA 5 2010/03/11 EEE 3 2010/03/12
BBB 1 2010/03/11
1002 DDD 4 2010/03/22 GGG 1 2010/03/12
WITH WTB_GRP07 AS
(
(SELECT 1001, 1, 'AAA', 3, '2010/03/03' FROM DUAL) UNION
(SELECT 1001, 1, 'BBB', 1, '2010/03/12' FROM DUAL) UNION
(SELECT 1001, 1, 'AAA', 5, '2010/03/11' FROM DUAL) UNION
(SELECT 1001, 2, 'DDD', 4, '2010/03/12' FROM DUAL) UNION
(SELECT 1001, 2, 'DDD', 5, '2010/03/12' FROM DUAL) UNION
(SELECT 1001, 2, 'EEE', 3, '2010/03/12' FROM DUAL) UNION
(SELECT 1002, 2, 'DDD', 4, '2010/03/22' FROM DUAL) UNION
(SELECT 1001, 3, 'FFF', 2, '2010/03/12' FROM DUAL) UNION
(SELECT 1001, 3, 'FFF', 2, '2010/03/12' FROM DUAL) UNION
(SELECT 1002, 3, 'GGG', 1, '2010/03/12' FROM DUAL)
)
SELECT NVL(WTB4.custkey, WTB3.custkey)
, NVL(WTB4.RK, WTB3.RK) AS RK
, WTB4.wtb1name
, WTB4.wtb1sumcnt
, WTB4.wtb1dt
, WTB4.wtb2name
, WTB4.wtb2sumcnt
, WTB4.wtb2dt
, WTB3.name
, WTB3.sumcnt
, WTB3.dt
FROM
(
SELECT NVL(WTB1.custkey, WTB2.custkey) AS custkey
, NVL(WTB1.RK, WTB2.RK) AS RK
, WTB1.name AS wtb1name
, WTB1.sumcnt AS wtb1sumcnt
, WTB1.dt AS wtb1dt
, WTB2.name AS wtb2name
, WTB2.sumcnt AS wtb2sumcnt
, WTB2.dt AS wtb2dt
FROM
(
SELECT custkey
, codetp
, name
, SUM(cnt) AS sumcnt
, dt
, ROW_NUMBER() OVER (PARTITION BY custkey, codetp ORDER BY dt) AS RK
FROM WTB_GRP07
WHERE codetp = '1'
GROUP BY custkey, codetp, name, dt
) WTB1 FULL OUTER JOIN
(
SELECT custkey
, codetp
, name
, SUM(cnt) AS sumcnt
, dt
, ROW_NUMBER() OVER (PARTITION BY custkey, codetp ORDER BY dt) AS RK
FROM WTB_GRP07
WHERE codetp = '2'
GROUP BY custkey, codetp, name, dt
) WTB2 ON (WTB1.custkey = WTB2.custkey AND WTB1.RK = WTB2.RK )
) WTB4
FULL OUTER JOIN
(
SELECT custkey
, codetp
, name
, SUM(cnt) AS sumcnt
, dt
, ROW_NUMBER() OVER (PARTITION BY custkey, codetp ORDER BY dt) AS RK
FROM WTB_GRP07
WHERE codetp = '3'
GROUP BY custkey, codetp, name, dt
) WTB3 ON (WTB4.custkey = WTB3.custkey AND WTB4.RK = WTB3.RK )
ORDER BY custkey, rk
(
SELECT 1001 custkey, 1 codetp, 'AAA' name, 3 cnt, '2010/03/03' dt FROM dual
UNION ALL SELECT 1001, 1, 'BBB', 1, '2010/03/12' FROM dual
UNION ALL SELECT 1001, 1, 'AAA', 5, '2010/03/11' FROM dual
UNION ALL SELECT 1001, 2, 'DDD', 4, '2010/03/12' FROM dual
UNION ALL SELECT 1001, 2, 'DDD', 5, '2010/03/12' FROM dual
UNION ALL SELECT 1001, 2, 'EEE', 3, '2010/03/12' FROM dual
UNION ALL SELECT 1002, 2, 'DDD', 4, '2010/03/22' FROM dual
UNION ALL SELECT 1001, 3, 'FFF', 2, '2010/03/12' FROM dual
UNION ALL SELECT 1001, 3, 'FFF', 2, '2010/03/12' FROM dual
UNION ALL SELECT 1002, 3, 'GGG', 1, '2010/03/12' FROM dual
)
SELECT custkey, rn
, MIN(DECODE(codetp,1,name)) name_1
, MIN(DECODE(codetp,1,cnt)) cnt_1
, MIN(DECODE(codetp,1,dt)) dt_1
, MIN(DECODE(codetp,2,name)) name_2
, MIN(DECODE(codetp,2,cnt)) cnt_2
, MIN(DECODE(codetp,2,dt)) dt_2
, MIN(DECODE(codetp,3,name)) name_3
, MIN(DECODE(codetp,3,cnt)) cnt_3
, MIN(DECODE(codetp,3,dt)) dt_3
FROM
(
SELECT custkey
, codetp
, name
, dt
, SUM(cnt) cnt
, ROW_NUMBER() OVER(PARTITION BY custkey, codetp ORDER BY dt) rn
FROM wtb_grp07
GROUP BY custkey, codetp, name, dt
)
GROUP BY custkey, rn
ORDER BY custkey, rn
;
한수 배우고 갑니다. ^^
발상전환만으로 정말 간단하게 해결이 가능하군요..감탄 ^^
그런데 제 쿼리가 일단 지저분한건 둘째치고 왜 에러가 났을까요?
이론적으론 문제가 없어뵈는데.ㅋ
결국 실행되는 코드는 예전의 방식대로 풀리게 됩니다.
a 기준 아우터조인 결과와 b 기준 a에 없는 결과를 union all 하는 쿼리로
쿼리 변형이 일어나서 실행되는거죠.
이러한 쿼리 변형과정에서 버그가 발생한게 아닐까? 의심되네요.
full outer join 쓸 일이 거의 없고,(가끔은 있겠죠)
그러나 중복으로 여러번 사용할 일은 아예 없다고 생각됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.