퀴즈로 배우는 SQL
목록
[퀴즈] 날짜별 모든 코드에 대한 자료 채우기 3 9 99,999+

by 마농 PARTITION OUTER JOIN ROLLUP LEFT OUTER JOIN OUTER JOIN [2012.08.22]

이번 퀴즈로 배워보는 SQL 시간에는 날짜별 모든 코드에 대한 자료를 채워 조회하는 쿼리를 어떻게 작성하는지에 대해 알아본다.

지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한후 정답과 해설을 참조하길 바란다.

공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자.

문제

코드테이블([표 1] 참조)에는 3가지 코드값이 들어 있습니다.

이력테이블([표 2] 참조)에는 일자별 코드에 해당하는 값이 저장되어 있습니다.

일자 별로 3개 코드가 모두 입력되는 날도 있지만 일부 코드만 입력되는 날도 있습니다. 일자별 코드별 코드명과 합계가 출력되는 쿼리를 작성하세요.

일부 코드만 입력된 날짜도 3개 코드가 모두 나와야 합니다. 일자별 소계도 함께 출력하세요.

[리스트 1]의 CODE와 DATA 테이블을 이용한 두 집합의 조인 결과는 [표 3]의 원본 테이블과 같습니다.

[리스트 1]을 참고하여 [표 4]의 결과 테이블 자료가 조회되는 쿼리를 작성하세요.

  • [표 1] 코드 테이블
CD NM
1 마이크로
2 소프트
3 웨어

  • [표 2] 이력 테이블
DT CD V
20120101 1 10
20120101 2 20
20120101 2 21
20120101 2 22
20120101 3 30
20120102 1 10
20120102 3 30
20120104 1 10
20120104 2 40
20120105 3 50

  • [리스트1] 원본 리스트
CREATE TABLE code AS
(
  SELECT 1 cd, '마이크로' nm FROM dual
  UNION ALL SELECT 2, '소프트' FROM dual
  UNION ALL SELECT 3, '웨어' FROM dual
);

CREATE TABLE 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
     , d.cd
     , c.nm
     , d.v
FROM code c
   , data d
WHERE c.cd = d.cd
;

  • [표 3] 원본 테이블
DT                       CD NM                               V
---------------- ---------- ------------------------ ----------
20120104                  1 마이크로                         10
20120101                  1 마이크로                         10
20120102                  1 마이크로                         10
20120104                  2 소프트                           40
20120101                  2 소프트                           21
20120101                  2 소프트                           22
20120101                  2 소프트                           20
20120102                  3 웨어                             30
20120105                  3 웨어                             50
20120101                  3 웨어                             30

  • [표 4] 결과 테이블
DT NM V
20120101 마이크로 10
20120101 소프트 63
20120101 웨어 30
20120101 소계 103
20120102 마이크로 10
20120102 소프트 0
20120102 웨어 30
20120102 소계 40
20120104 마이크로 10
20120104 소프트 40
20120104 웨어 0
20120104 소계 50
20120105 마이크로 0
20120105 소프트 0
20120105 웨어 50
20120105 소계 50

문제설명

이 문제는 날짜, 코드, 값을 가진 이력테이블과 코드테이블을 조인하여 코드명칭을 가져오는 문제입니다.

단, 모든 날짜에 대해 모든 코드가 다 존재하지는 않습니다. 또한 특정 일자에는 코드가 중복되어 들어 있습니다. 중복되어 저장된 코드는 하나로 합쳐야 하며 저장되지 않고 누락된 코드 또한 함께 보여줘야만 합니다.

각 일자별로 3개의 코드값에 대한 코드명칭과 코드별 합계, 그리고, 일별 소계까지 함께 보여주는 문제입니다.

정답

문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.

  • [리스트2] 정답 리스트
SELECT d.dt
     , NVL(c.nm, '소계') 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 d.dt, ROLLUP((c.cd, c.nm))
;

? 어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

생소한 구문이 사용되었네요. 오라클 10G 버전부터 제공되는 Partition Outer Join 기능을이용하여 문제를 해결했습니다.

이번 퀴즈는 Partition Outer Join 에 대해 소개하는 시간입니다. 우선은, 이 기능에 대해 몰랐다면? 어떤 형태로 SQL을 작성해야 했었는지 살펴보고, 비교해 봄으로써 Partition Outer Join 이 얼마나 편리한 기능인지를 알아보도록 하겠습니다.

없는 코드도 나타나야 한다면? 우리는 어떤 기능을 사용해야 할까요? 바로 아우터 조인 기능을 사용해야 합니다. 간단하게 아우터 조인을 통해 우리가 원하는 결과가 나오는지 살표보겠습니다.

[리스트 1] 원본 리스트 쿼리에 아우터 조인을 해봤습니다.([리스트 3] 아우터 조인)

  • [리스트 3] 아우터 조인
WITH code AS
(
SELECT 1 cd, '마이크로' nm FROM dual
UNION ALL SELECT 2, '소프트' FROM dual
UNION ALL SELECT 3, '웨어' FROM dual
UNION ALL SELECT 4, '마농' 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
     , d.cd
     , c.nm
     , d.v
FROM code c
   , data d
WHERE c.cd = d.cd(+)
;

  • [표 5] 아우터 조인 결과
DT                       CD NM                                V
---------------- ---------- ------------------------ ----------
20120101                  1 마이크로                         10
20120101                  2 소프트                           20
20120101                  2 소프트                           21
20120101                  2 소프트                           22
20120101                  3 웨어                             30
20120102                  1 마이크로                         10
20120102                  3 웨어                             30
20120104                  1 마이크로                         10
20120104                  2 소프트                           40
20120105                  3 웨어                             50
                            마농

[표 5]의 아우터 조인 결과를 보시면 우리가 원하는 결과가 나오지 않은 것을 알 수 있습니다.

왜 코드테이블을 기준으로 아우터 조인했는데도 코드가 다 나오지 않았을까요? 이해를 돕기 위해 코드값(4, 마농)을 하나 추가해서 테스트했습니다.

[표 5]의 아우터 조인 결과에 “마농”이라는 코드명이 나온것을 보면 이력테이블에 없는 코드값 4에 대해서도 결과가 나온것을 알 수 있습니다. 즉, 아우터 조인은 성립된 셈이죠.

그러나 우리가 원하는 결과인 각 날짜별로 4번코드의 값이 각각 나오지는 않았습니다. 이는 우리가 최초에 아우터 조인의 기준테이블을 잘못 알고 접근했기 때문입니다.

우리가 원하는 것은 날짜별 코드별 값이므로 코드만을 기준으로 생각한 것이 잘못입니다.

기준이 되는 집합은 날짜와 코드가 모두 있는 집합이 되어야만 합니다.

  • [리스트 4] 기준 집합 만들기
SELECT a.dt
     , c.cd
     , c.nm
FROM (SELECT DISTINCT dt FROM data) a
    , code c
ORDER BY dt, cd
;

  • [표 6] 기준 집합
DT                       CD NM
---------------- ---------- -----------
20120101                  1 마이크로
20120101                  2 소프트
20120101                  3 웨어
20120102                  1 마이크로
20120102                  2 소프트
20120102                  3 웨어
20120104                  1 마이크로
20120104                  2 소프트
20120104                  3 웨어
20120105                  1 마이크로
20120105                  2 소프트
20120105                  3 웨어

[리스트 4]와 같이 날짜만의 집합과 코드집합을 조건 없이 조인하면 우리가 원하는 기준 테이블이 만들어 집니다. 날짜는 이력테이블에서 중복을 제거하고 가져왔습니다.

이제 만들어진 기준 테이블과 이력테이블을 아우터 조인하면 원하는 결과가 나오겠지요.

  • [리스트 5] 아우터 조인 시도
SELECT a.dt
     , c.cd
     , c.nm
     , d.v
FROM (SELECT DISTINCT dt FROM data) a
   , code c
   , data d
WHERE a.dt = d.dt(+)
  AND c.cd = d.cd(+)
;

ORA-01417: 하나의 테이블은 하나의 다른 테이블과 포괄 조인할 수 있습니다

[리스트 5]에서 아우터 조인을 시도했지만 에러가 발생했습니다.

아우터 조인 대상 집합인 d 에대해 아우터 조인의 기준이 되는 집합이 a와 c 두 개 집합이되어 발생한 에러입니다. 이를 해결하기 위해서는 a와 c 두 개 집합을 하나로 만들어 조인하면 되겠지요.

  • [리스트 6] 인라인 뷰 이용 아우터 조인 리스트
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(+)
;

  • [표 7] 인라인 뷰 이용 아우터 조인 결과
DT                       CD NM                                V
---------------- ---------- ------------------------ ----------
20120101                  1 마이크로                         10
20120101                  2 소프트                           20
20120101                  2 소프트                           21
20120101                  2 소프트                           22
20120101                  3 웨어                             30
20120102                  1 마이크로                         10
20120102                  2 소프트                               
20120102                  3 웨어                             30
20120104                  1 마이크로                         10
20120104                  2 소프트                           40
20120104                  3 웨어                                 
20120105                  1 마이크로                             
20120105                  2 소프트                               
20120105                  3 웨어                             50

[리스트 6]에서 인라인뷰를 이용해 두 집합을 하나로 만들어 아우터 조인하였습니다. 그 결과 [표 7] 을 보면 음영으로 표시된 것과 같이 날짜별로 없는 코드에 대한 값이 표시 되었습니다.

인라인뷰를 사용함으로 쿼리가 좀 더 복잡해졌습니다. 인라인 뷰를 없애볼까요?

  • [리스트 7] ANSI SQL을 이용한 아우터 조인 리스트
SELECT a.dt
     , c.cd
     , c.nm
     , d.v
  FROM (SELECT DISTINCT dt FROM data) a
 CROSS JOIN code c
  LEFT OUTER JOIN data d
    ON a.dt = d.dt
   AND c.cd = d.cd
 ORDER BY dt, cd
;

[리스트 5]에서 아우터 조인을 시도했지만 에러가 발생했던 쿼리를 [리스트 7]과 같이 ANSI SQL 문법으로 바꿔 보았더니 에러 없이 수행이 됩니다.

[리스트 6]보다는 쿼리가 간결해 졌지만 이력테이블을 두 번 읽을 수밖에 없는 구조입니다.

이제 정답쿼리에서 사용된 Partition Outer Join 으로 바꿔볼까요?

  • [리스트 8] Partition Outer Join
SELECT d.dt
     , c.cd
     , c.nm
     , d.v
  FROM code c
  LEFT OUTER JOIN data d
  PARTITION BY (d.dt)
    ON c.cd = d.cd
;

Partition Outer Join 을 사용하여 이력테이블도 한번만 읽고 SQL이 간결해 졌습니다.

아우터 조인 대상이 되는 집합에서 PARTITION BY 구문을 통해 그룹을 지정하게 되어, 해당 그룹별로 아우터 조인을 하게 되는 것입니다.

즉, 이력테이블의 날짜 그룹별로 코드테이블을 기준으로 아우터 조인을 수행하게 되는 것입니다.

이제 없는 코드도 보이게 하는 부분은 Partition Outer Join 을 통해 해결이 되었습니다.

마지막으로 코드별 합계와 소계를 구해볼까요? 이는 Group By, Sum 과 Rollup 을 이용하시면 됩니다.

이번 퀴즈시간은 Partition Outer Join 에 대한 소개 시간이므로 나머지 합계 부분은 독자여러분께서 직접 해결해 보세요.

- 강좌 URL : http://www.gurubee.net/lecture/2204

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
by 손님 [2012.08.24 12:01:13]
딴지는 아니고요

표1의 DT와 리스트1의 DT가 다르네요.

표1
20111201 마이크로 10

리스트 1
SELECT '20120101' dt, 1 cd, 10 v FROM dual
by 김정식 [2012.08.24 14:50:32]
감사합니다. 모두 수정하였습니다. ^^
by Always [2013.04.13 11:49:46]

아무리해도 아웃조인이 안 걸리더라구요. ㅡㅜ
역시 기준을 잡는데서.. ㅎ ANSI SQL 배워야겠어요~ 감사합니다. ^^
by 장비 [2013.05.13 13:08:14]
파티션 아우터 조인은 오라클 아우터조인'(+)' 기호로는 따로 정의가  안되있는거 같더라고요 .
by Incruel [2013.08.08 14:15:15] 
SELECT res.dt, c.cd, res.v, res.ssum
 FROM (SELECT st.dt, nvl(st.nm, '소계') V, nvl(SUM(d.v), 0) ssum
  FROM (SELECT *
   FROM (SELECT DISTINCT dt
    FROM data),
   code) st,
  data d
  WHERE st.dt = d.dt(+)
  AND st.cd = d.cd(+)
  GROUP BY ROLLUP(st.dt, st.nm)
  ORDER BY dt) res,
 code c
 WHERE c.nm(+) = res.v
 ORDER BY dt, cd;

rollup 부분에 st.dt, st.cd, st.nm 이런식으로 넣었더니 값이 이상하게 나오네요..
Group by로 3개 이상 묶으려고 할때 rollup을 어떤식으로 써야 하나요?
표시되는걸 20120101 1 마이크로 10 이렇게 4개의 컬럼이 나오도록 하고 싶어서요..
by cuteleem [2015.04.23 12:35:46]

이렇게 저렇게 인라인뷰로 정답은 맞췃는데 답보고 깜짝 놀랐습니다

와 이렇게 간결할수가 정말 너무 멋지세요^^

by 심심해죽을맛 [2016.02.03 17:57:26]

재미있네요 유익한글 감사합니다

by 고수가되고싶어요 [2017.09.06 13:49:37] 
select a.dt
	  --,b.cd
      ,CASE WHEN b.nm IS NULL THEN '소계'
       WHEN b.nm IS NOT NULL THEN b.nm END nm 
      ,NVL(SUM(c.v),0) v 
FROM(SELECT DISTINCT dt FROM data ) a , code b,data c  
WHERE a.dt = c.dt(+)
AND  b.cd= c.cd (+)
GROUP BY a.dt ,ROLLUP((b.cd ,b.nm)) 
ORDER BY a.dt ,b.cd

요로케 해도 나오네용...ㅎ

by sarahpark [2020.03.04 11:38:12] 
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
--       , MAX(D.CD)
       , CASE WHEN NM IS NULL THEN '소계' ELSE NM END NM
       , SUM(D.V) V
FROM     CODE C
       , DATA D
WHERE    1 = 1
AND      C.CD = D.CD(+)
GROUP BY ROLLUP (D.DT, C.NM)
ORDER BY D.DT
;

OUTER JOIN 부분 빼고 완료했네요..

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입