[ORACLE SQL 문제] 날짜별 모든 코드(빠진 데이터 없이)에 대한 자료 채우기

문제 링크: http://www.gurubee.net/lecture/2204

(내 댓글 참고)

[퀴즈] 날짜별 모든 코드에 대한 자료 채우기

---

내 코드:

WITH CODE AS
(
  SELECT 1 CD, '마이크로' NM FROM DUAL
  UNION ALL SELECT 2, '소프트' FROM DUAL
  UNION ALL SELECT 3, '웨어' FROM DUAL
)
, DATA AS
(
  SELECT '20120101' DT, 1 CD, 10 V FROM DUAL
  UNION ALL SELECT '20120101', 2, 20 FROM DUAL
  UNION ALL SELECT '20120101', 2, 21 FROM DUAL
  UNION ALL SELECT '20120101', 2, 22 FROM DUAL
  UNION ALL SELECT '20120101', 3, 30 FROM DUAL
  UNION ALL SELECT '20120102', 1, 10 FROM DUAL
  UNION ALL SELECT '20120102', 3, 30 FROM DUAL
  UNION ALL SELECT '20120104', 1, 10 FROM DUAL
  UNION ALL SELECT '20120104', 2, 40 FROM DUAL
  UNION ALL SELECT '20120105', 3, 50 FROM DUAL
)
SELECT   D.DT DT
       , CASE WHEN NM IS NULL THEN '소계' ELSE NM END NM
       , NVL(SUM(D.V),0) V
FROM     CODE C
       LEFT OUTER JOIN DATA D
       PARTITION BY (D.DT)
ON      C.CD = D.CD(+)
GROUP BY ROLLUP (D.DT, C.NM)
ORDER BY D.DT
;

---

깨달은 점

 

[내가 한 것]
rollup으로 소계 표시[내가 한 것]
1. rollup으로 소계 표시함 성공.

2. 없는 데이터도 표시해야 되네! -> outer join을 생각함 -> 실패 
이유 : 아우터 조인의 기준 테이블을 잘못 알고 접근했기 때문.
-> 우리는 '날짜(DT)별로' 코드(CD) 값들을 원하는 것인데, 코드(CD)만을 기준으로 생각한 것이 잘못됨.

[내가 못한 것]
1. 그냥 outer join 말고 left outer join을 해야 했다는 것.
2. partion outer join을 해야한다는 것

LEFT OUTER JOIN 설명: http://www.gurubee.net/lecture/1021
partion outer join 설명: https://tyboss.tistory.com/entry/Oracle-PARTITION-OUTER-JOIN

 

---

[PARTION OUTER JOIN 쓰지 않고 이중쿼리&OUTER JOIN으로 하는 법]

SELECT   x.dt
       , x.cd
       , x.nm
       , d.v
FROM     (SELECT   a.dt
                 , c.cd
                 , c.nm
          FROM    (SELECT DISTINCT dt FROM data) a, code c
                  ) x
                 , data d
WHERE    x.dt = d.dt(+)
AND      x.cd = d.cd(+)
;