[Oracle] PARTITION OUTER JOIN

2012. 11. 22. 17:58

기획팀에서 이대리가 전산실에 와서 부탁을 한다.

기획실 이대리:"월별 부서별로 2002년도 실적을 좀 뽑아주실수 있나요?"

전산실 김대리:"네 오늘저녁 6시쯤 오시면 실적 보고서를 드릴수 있습니다."

기획실 이대리:"실적을 만드실때 주의사항이 있습니다.
   월별 부서별로 실적을 뽑을때 만약 20번 부서에서 5월, 7월에
   실적이 없다고 하더라고 5월,7월 실적을 0 으로 표시해주세요."

전산실 김대리:"네 알겠습니다. 그것은 별로 어렵지 않습니다."

년월만 들어있는 테이블과 월별부서별실적 테이블의 구조는 아래와 같다.

월별 부서별 실적테이블의 2002년 실적은 다음 그림과 같다.
모든 월에 실적이 있는것은 아니다.(예를 들면 10번 부서는 2002년도에 1,3,6,7,8,10,11 월에 실적이 없다. )

30번 부서부터는 지면관계상 그림에서 생략하였다.

기획실 이대리의 요구사항은 아래그림과 같다.
(실적이 없는달은 실적을 0 으로 표시함)

30번 부서의 실적부터는 지면관계상 그림에서 생략하였다.

다행히 월별, 부서별 실적 테이블이 존재하기 때문에 김대리는 묵묵히 월별 실적 SQL을 아래처럼 작성하였다.
아래처럼 작성한 이유는 부서가 20개(10번부터 200번까지) 있기 때문에 부서별로 무조건 12건(1월~12월)을 만들기 위해서 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
AND e.DEPTNO(+) = 10 --> 10번부서에 대해서 1월~12월 실적을 만듬.
AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
AND e.DEPTNO(+) = 20 --> 20번부서에 대해서 1월~12월 실적을 만듬.
AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
AND e.DEPTNO(+) = 30 --> 30번부서에 대해서 1월~12월 실적을 만듬.
AND m.yymm like '2002%'
Union all
...........................................................................................중간생략
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
AND e.DEPTNO(+) = 200 --> 200번부서에 대해서 1월~12월 실적을 만듬.
AND m.yymm like '2002%'

우연히 김대리의 작업을 지켜보던 전산실 박과장이 한마디 한다.
전산실 박과장 :"김대리 그작업할때 200번이나 노가다(Union All) 할생각이냐?
"SQL 공부좀해라"

김대리에게 호통을 친 박과장은 자신감 있게 아래의 SQL을 1분만에 만들었다.

SELECT dept_month.deptno, dept_month.yymm, NVL(e.sale_amt,0)
   FROM (SELECT d.deptno, m.yymm
   FROM ( SELECT c.deptno
   FROM DEPT c
WHERE EXISTS (SELECT 1
   FROM dept_sale_history d
   WHERE d.deptno = c.deptno
      AND d.yymm like '2002%')) d,
   ( SELECT m.yymm
      FROM year_month m
      WHERE m.yymm like '2002%' ) m
   ) dept_month,    --> 월별 부서별 집합을 먼저 만든다.
dept_sale_history e
WHERE dept_month.deptno(+) = e.deptno
AND dept_month.yymm(+) = e.yymm

위의 SQL 의 핵심은 모든 부서에 대하여 1월~12월 까지 와꾸?(틀)를 만들어 놓고
부서별 월별실적 테이블과 아우터 조인을 하기위해서 이다.
위의 SQL 에서 EXISTS 를 사용한 이유는 2002 년도에 실적이 있는 부서만 뽑기 위해서다.
하지만 위의 SQL 도 비효율이 있다.
부서별 월별 실적테이블을 2번이나 ACCESS 하였다.

박과장의 작업을 옆에서 지켜보던 신입사원이 고개를 기우뚱 하며 박과장에게 말을 건낸다.
전산실 신입사원:"dept_sale_history" 테이블을 2번 사용하지 않고도 같은 실적을 뽑을수 있습니다."
전산실 박과장 :"그래? 그럼 한번해봐"

신입사원을 지켜보던 박과장은 경악을 금치 못한다.
신입사원이 20초만에 SQL 을 작성하고도 성능은 신입사원의 SQL이 우수했기 때문이다.
단 4줄의 SQL 로 기획팀 이대리의 요구사항을 해결하였다.
박과장은 SQL 을 사용한지 10년이 넘는 배테랑 개발자 이지만 10g 신기능은 써보지 못한 상태였다.
아래의 SQL이 신입사원의 SQL 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m LEFT OUTER JOIN dept_sale_history e
PARTITION BY (e.deptno) ON (m.yymm = e.yymm )
WHERE m.yymm like '2002%';

신입사원이 위의 SQL 을 사용할수 있었던건 처음 배운 SQL 문법이 Oracle 10g 기준이었고
박과장은 Oracle 8 버젼의 SQL을 공부 해었기 때문이다.

위의 Partition Outer Join 은 10g 의 새기능이다.
Partition Outer Join 의 기능을 요약하면 부서별로 중간중에 빠진 월의 실적을 생성해주는 기능이다.

결론 :
Partition Outer Join 은 10g 의 신기능중 일부에 불과하다.
버전별로 New Features의 중요성을 다시한번 강조하지만 위의 경우와 같이
신기능을 모르면 작업량이 늘어날수 밖에 없고 대부분 성능도 느리다.
또한 Oracle 8.0 시절에 최적화된 SQL 이 항상 Oracle 10g 에서 최적화된 SQL 이라고 볼수 없다.
Oracle 9i 가 나온지는 10년이 됬으며 Oralce 10g 가 나온지도 6년이 지났고 2년전에 Oracle 11g 가 나왔다.
신버젼이 나올때 마다 알라딘의 요술램프처럼 주인님이 사용해주기를 기다리는 마술 같은 여러가지 신기능이 숨어있다는 점을 기억하자.

===========================================================================================
===========================================================================================

출처 : http://ukja.tistory.com/361

데이터 모델링을 하다보면 비정규화의 유혹에 빠지는 경우가 있습니다. 이유는 여러가지가 있겠지만, 대표적인 한가지 이유는 우리의 데이터 인식법과 관계형 데이터베이스의 표현 양식에서 차이가 있다는 것입니다. 가령 특정 테이블에 있는 인덱스별로 컬럼 목록을 보고 싶다면, 우리 눈에는 이렇게 보는 것이 가장 편합니다.

T1_N1  C1, C2, C3
T1_N2  C3
T1_N3  C2, C1

하지만 관계형 데이터베이스는 정규화 법칙에 의해 다음과 같이 데이터를 저장하게 됩니다. 보기가 어렵죠.

T1_N1  C1
T1_N1  C2
T1_N1  C3
T1_N2  C3
T1_N3  C2
T1_N3  C1

정규화된 형태에서 데이터를 추출하면 우리가 데이터를 인식하는 방법과 달라서 대단히 불편하게 느끼게 됩니다. 이런 이유 때문에 비정규화의 유혹이 생길 수 있습니다. (엄격하게 말하면 위의 예는 제 1 정규형에 해당합니다)

즉 비정규화란 "우리가 데이터를 인식하는 방식 그대로" 표현하고자 하는 욕구를 의미한다고 할 수 있습니다. 비정규화를 하면 쿼리가 간단해지는 것도 이런 이유 때문입니다. 더 정확하게 말하면 우리 눈에 간단해보이는거죠. 물론 성능을 위한 적절한 비정규화는 삶의 양념같은 역할을 하기도 합니다. 너무 빡빡한 삶은 재미가 없잖아요? ^^;

다행스러운 것은 오라클이 제공하는 (비교적 최신의) 기능들을 잘 이용하면 관계형으로 저장된 데이터를 비교적 간단하게 우리가 원하는 비정규화된 형태로 추출할 수 있다는 것입니다.

간단한 예제를 볼텐데요, 우선 다음과 같이 테이블 T1과 총 6개의 인덱스(Primary Key 포함)을 만듭니다.

create table t1(
c1 number primary key,
c2 number,
c3 number,
c4 number
);

create index t1_n1 on t1(c1, c2);
create index t1_n2 on t1(c2, c3);
create index t1_n3 on t1(c3);
create index t1_n4 on t1(c3, c1, c2);
create index t1_n5 on t1(c1, c2, c4);

우리의 목적은 다음과 같은 표 형태로 인덱스별, 컬럼별 순서와 Unique 여부를 보는 것입니다. U가 붙으면 Unique이고, T1_N4의 경우에는 (C3, C1, C2)로 이루어져 있다는 의미입니다.

Index_name    C1   C2   C3   C4
-----------          --   --   --   --
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3

위의 같은 표현 양식에는 일반적으로 표현하기 힘든 두 가지 쿼리 패턴이 숨어 있습니다.

Missing Value 채우기: 인덱스 T1_N1의 경우를 보면 컬럼 (C1, C2)로 구성되어 있습니다. 하지만 전체 컬럼은 (C1, C2, C3, C4)로 이루어져 있죠. 이때 C3, C4가 Missing Value가 됩니다. Missing Value까지 다 포함해야 위와 같은 형태의 결과를 얻을 수 있습니다.
Row 값을 Column으로 변환하기: 인덱스 T1_N1의 경우를 보면 (C1(1), C2(2), C3(X), C4(X)) 로 구성됩니다. Missing Value 채우기를 통해 데이터를 만들었다고 하더라도 이 데이터들을 Row로 되어 있기 때문에 컬럼 형태로 변환해야 합니다.

위의 두 가지 요구 사항은 매우 보편적으로 발생합니다. 최신 버전의 오라클에서는 Partition Outer Join과 Listagg 함수를 이용하면 비교적 간단하게 구현할 수 있습니다. 그렇지 않다면 좀 더 수고스러운 과정을 거쳐야할 것이구요.

우선 Missing Value를 채우는 가장 보편적인 방법은 값을 중복해서 복사하는 것입니다. 가령 다음과 같이 인덱스별, 컬럼별로 모든 조합이 다 나오도록 중복된 데이터 셋을 만들면 됩니다.

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
SYS_C0016792         C1
SYS_C0016792         C2
SYS_C0016792         C3
SYS_C0016792         C4
T1_N1                C1
T1_N1                C2
T1_N1                C3
T1_N1                C4
T1_N2                C1
T1_N2                C2
T1_N2                C3
T1_N2                C4
T1_N3                C1
T1_N3                C2
T1_N3                C3
T1_N3                C4
T1_N4                C1
T1_N4                C2
T1_N4                C3
T1_N4                C4
T1_N5                C1
T1_N5                C2
T1_N5                C3
T1_N5                C4

위와 같은 형태의 데이터를 만드는 가장 보편적인 방법은 복사를 위한 피봇(Pivot) 테이블을 이용하는 것입니다. 아래와 같이 DBA_TAB_COLUMNS 뷰를 피봇 테이블로 활용할 수 있습니다. DBA_TAB_COLUMNS와 DBA_INDEXES 뷰는 정상적인 조인이 불가능합니다. DBA_INDEXES 뷰에는 컬럼 정보가 없기 때문입니다. 따라서 Cartesian Join이 발생하게 되고 그로 인해 인덱스별로 컬럼 수만큼 복사가 발생하게 됩니다.

-- pivot table
select
i.index_name,
i.uniqueness,
tc.column_id,
tc.column_name
from
dba_tab_columns tc,
dba_indexes i
where
tc.owner = 'TPACK'
and tc.table_name = 'T1'
and tc.owner = i.owner
and tc.table_name = i.table_name
-- 컬럼에 대한 조인 필터 없음 ==> Cartesian Join
order by
i.index_name, tc.column_id
;

-- 그 덕분에 다음과 같이 복사가 이루어짐
INDEX_NAME           UNIQUENES COLUMN_ID COLUMN_NAME
-------------------- --------- ---------- --------------------
SYS_C0016792         UNIQUE             1 C1
SYS_C0016792         UNIQUE             2 C2
SYS_C0016792         UNIQUE             3 C3
SYS_C0016792         UNIQUE             4 C4
T1_N1                NONUNIQUE          1 C1
T1_N1                NONUNIQUE          2 C2
T1_N1                NONUNIQUE          3 C3
T1_N1                NONUNIQUE          4 C4
T1_N2                NONUNIQUE          1 C1
T1_N2                NONUNIQUE          2 C2
T1_N2                NONUNIQUE          3 C3
T1_N2                NONUNIQUE          4 C4
T1_N3                NONUNIQUE          1 C1
T1_N3                NONUNIQUE          2 C2
T1_N3                NONUNIQUE          3 C3
T1_N3                NONUNIQUE          4 C4
T1_N4                NONUNIQUE          1 C1
T1_N4                NONUNIQUE          2 C2
T1_N4                NONUNIQUE          3 C3
T1_N4                NONUNIQUE          4 C4
T1_N5                NONUNIQUE          1 C1
T1_N5                NONUNIQUE          2 C2
T1_N5                NONUNIQUE          3 C3
T1_N5                NONUNIQUE          4 C4

Missing Value를 채운 후, 그 데이터를 DBA_IND_COLUMNS 뷰와 조인하면 우리가 원하는 완전한 형태의 정보를 얻을 수 있습니다.

select
c.index_name,
decode(ic.column_position,null,'',
  decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
c.column_name,
c.column_id,
ic.column_position
from
(
select
  i.table_owner,
  i.table_name,
  i.index_name,
  i.uniqueness,
  tc.column_id,
  tc.column_name
from
  dba_tab_columns tc,
  dba_indexes i
where
  tc.owner = 'TPACK'
  and tc.table_name = 'T1'
  and tc.owner = i.owner
  and tc.table_name = i.table_name
order by
  i.index_name, tc.column_id
) c,
dba_ind_columns ic
where
c.table_owner = ic.table_owner(+)
and c.table_name = ic.table_name(+)
and c.index_name = ic.index_name(+)
and c.column_name = ic.column_name(+)
order by
c.index_name, c.column_id
;

INDEX_NAME           I COLUMN_NAME           COLUMN_ID COLUMN_POSITION
-------------------- - -------------------- ---------- ---------------
SYS_C0016792         U C1                            1               1
SYS_C0016792           C2                            2
SYS_C0016792           C3                            3
SYS_C0016792           C4                            4
T1_N1                  C1                            1               1
T1_N1                  C2                            2               2
T1_N1                  C3                            3
T1_N1                  C4                            4
T1_N2                  C1                            1
T1_N2                  C2                            2               1
T1_N2                  C3                            3               2
T1_N2                  C4                            4
T1_N3                  C1                            1
T1_N3                  C2                            2
T1_N3                  C3                            3               1
T1_N3                  C4                            4
T1_N4                  C1                            1               2
T1_N4                  C2                            2               3
T1_N4                  C3                            3               1
T1_N4                  C4                            4
T1_N5                  C1                            1               1
T1_N5                  C2                            2               2
T1_N5                  C3                            3
T1_N5                  C4                            4               3

이제 남은 일은 Row로 존재하는 데이터를 Column 형태로 표현하는 것입니다. 아래와 같이 DECODE 함수와 MAX 함수를 조합하면 완전한 형태의 피보팅(Pivoting)이 이루어집니다.

-- pivoting and decode
col col_list format a50
with x as (
select
  c.index_name,
  decode(ic.column_position,null,'',
   decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
  c.column_name,
  c.column_id,
  ic.column_position
from
  (
  select
   i.table_owner,
   i.table_name,
   i.index_name,
   i.uniqueness,
   tc.column_id,
   tc.column_name
  from
   dba_tab_columns tc,
   dba_indexes i
  where
   tc.owner = 'TPACK'
   and tc.table_name = 'T1'
   and tc.owner = i.owner
   and tc.table_name = i.table_name
  order by
   i.index_name, tc.column_id
  ) c,
  dba_ind_columns ic
where
  c.table_owner = ic.table_owner(+)
  and c.table_name = ic.table_name(+)
  and c.index_name = ic.index_name(+)
  and c.column_name = ic.column_name(+)
order by
  c.index_name, c.column_id
)
select
x.index_name,
rpad(max(decode(x.column_id,1,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
rpad(max(decode(x.column_id,2,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
rpad(max(decode(x.column_id,3,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
rpad(max(decode(x.column_id,4,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')
  as col_list
from
x
group by
index_name
;

INDEX_NAME           COL_LIST
-------------------- --------------------------------------------------
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3

Oracle 11gR2에서는 LISTAGG 함수를 이용하면 훨씬 간단한 방법으로 구현할 수 있습니다. 그 이전 버전이라면 XMLAGG 함수를 사용할 수 있을 것입니다.

-- pivoting and listagg
col col_list format a50
with x as (
select
  c.index_name,
  decode(ic.column_position,null,'',
   decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
  c.column_name,
  c.column_id,
  ic.column_position
from
  (
  select
   i.table_owner,
   i.table_name,
   i.index_name,
   i.uniqueness,
   tc.column_id,
   tc.column_name
  from
   dba_tab_columns tc,
   dba_indexes i
  where
   tc.owner = 'TPACK'
   and tc.table_name = 'T1'
   and tc.owner = i.owner
   and tc.table_name = i.table_name
  order by
   i.index_name, tc.column_id
  ) c,
  dba_ind_columns ic
where
  c.table_owner = ic.table_owner(+)
  and c.table_name = ic.table_name(+)
  and c.index_name = ic.index_name(+)
  and c.column_name = ic.column_name(+)
order by
  c.index_name, c.column_id
)
select
x.index_name,
listagg(rpad(nvl(x.column_position||'',' ')||x.is_unique, 5, ' '), '')
  within group (order by x.column_id)
  as col_list
from
x
group by
x.index_name
;

Partition Outer Join을 사용하면 Cartesian Join을 사용할 필요없이 훨씬 효율적으로 Missing Value를 채울 수 있습니다.

아래 결과를 보면 DBA_TAB_COLUMNS 뷰와 DBA_IND_COLUMNS 뷰를 Outer Join하는 것만으로 Missing Value를 채울 수가 없다는 것을 알 수 있습니다.

-- what is partition outer join?
select
ic.index_name,
tc.column_name,
ic.column_position
from
(select
  column_id,
  column_name
from
  dba_tab_columns
where
  owner = 'TPACK'
  and table_name = 'T1'
) tc
left outer join
(select
  index_name,
  column_position,
  column_name
from
  dba_ind_columns
where
  table_owner = 'TPACK'
  and table_name = 'T1'
) ic
on (tc.column_name = ic.column_name)
;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_C0016793         C1                                 1
T1_N1                C1                                 1
T1_N1                C2                                 2
T1_N2                C2                                 1
T1_N2                C3                                 2
T1_N3                C3                                 1
T1_N4                C3                                 1
T1_N4                C1                                 2
T1_N4                C2                                 3
T1_N5                C1                                 1
T1_N5                C2                                 2
T1_N5                C4                                 3

하지만 다음과 같이 Partition Outer Join을 사용하면 DBA_IND_COLUMNS.INDEX_NAME 별로 그룹핑해서 Outer Join을 수행합니다. 따라서 Missing Value를 완벽하게 채울 수 있습니다.

select
ic.index_name,
tc.column_name,
ic.column_position
from
(select
  column_id,
  column_name
from
  dba_tab_columns
where
  owner = 'TPACK'
  and table_name = 'T1'
) tc
left outer join
(select
  index_name,
  column_position,
  column_name
from
  dba_ind_columns
where
  table_owner = 'TPACK'
  and table_name = 'T1'
) ic
partition by (ic.index_name)   -- DBA_IND_COLUMNS.INDEX_NAME 별로 그룹핑해서 아우터 조인
on (tc.column_name = ic.column_name)
;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_C0016793         C1                                 1
SYS_C0016793         C2
SYS_C0016793         C3
SYS_C0016793         C4
T1_N1                C1                                 1
T1_N1                C2                                 2
T1_N1                C3
T1_N1                C4
T1_N2                C1
T1_N2                C2                                 1
T1_N2                C3                                 2
T1_N2                C4
T1_N3                C1
T1_N3                C2
T1_N3                C3                                 1
T1_N3                C4
T1_N4                C1                                 2
T1_N4                C2                                 3
T1_N4                C3                                 1
T1_N4                C4
T1_N5                C1                                 1
T1_N5                C2                                 2
T1_N5                C3
T1_N5                C4                                 3

위에서 얻은 결과를 LISTAGG 함수를 이용하면 원하던 포맷의 결과를 얻을 수 있습니다.

-- partition outer join & listagg
col col_list format a50
with x as (
select
  i.index_name,
  tc.column_id,
  ic.column_position,
  tc.column_name,
  decode(ic.column_position,null,'',
   decode(i.uniqueness,'UNIQUE','U','')) as is_unique
from
  (
   (
   select
    column_id,
    column_name
   from
    dba_tab_columns
   where
    owner = 'TPACK'
    and table_name = 'T1'
   ) tc
   left outer join
   (select
    index_name,
    column_position,
    column_name
   from
    dba_ind_columns
   where
    table_owner = 'TPACK'
    and table_name = 'T1'
   ) ic
   partition by (ic.index_name)
   on (tc.column_name = ic.column_name)
   left outer join
   (select
    index_name,
    uniqueness
   from
    dba_indexes
   where
    table_owner = 'TPACK'
    and table_name = 'T1'
   ) i
   on (i.index_name = ic.index_name)
  )
order by
  ic.index_name, tc.column_id
)
select
x.index_name,
listagg(rpad(nvl(x.column_position||'',' ')||x.is_unique, 5, ' '), '')
  within group (order by x.column_id)
  as col_list
from
x
group by
x.index_name
;

적다보니 너무 길어졌네요. 오라클이 제공하는 기능을 잘 이용하면 그다지 어렵지 않게(?) 정규화된 관계 모델로부터 우리가 인식하는 형식의 데이터로 표현할 수 있다는 정도의 의도로 봐주시면 되겠습니다.

===========================================================================================
===========================================================================================

출처 : http://blog.naver.com/savemyface/130020495511

Oracle 10g SQL의 개선 사항 중, 보고서 작성에 유용한 기능을 한가지 알아봅니다.

Partitioned Outer Join은 기존의 Outer Join을 개선한 것으로서, OLAP 환경의 Dimension 테이블과 Fact 테이블을 조인하는 경우, 유용하게 사용 할 수 있습니다.

구체적인 설명 보다는 간단한 예를 들어 살펴보겠습니다.

times 테이블에는 2006년도 1월~12월의 시계열 데이터가 포함되어 있습니다.

SQL> create table times
2 (year number,
3 month number);

테이블이 생성되었습니다.

SQL>
SQL> insert into times(year, month)
2 select 2006, rownum from all_objects where rownum <=12;

12 개의 행이 만들어졌습니다.

SQL>
SQL> select * from times;

      YEAR      MONTH
---------- ----------
      2006          1
      2006          2
      2006          3
      2006          4
      2006          5
      2006          6
      2006          7
      2006          8
      2006          9
      2006         10
      2006         11
      2006         12

12 개의 행이 선택되었습니다.

sales 테이블에는 년도 및 월별 제품 판매량 데이터가 포함되어 있습니다.

SQL> create table sales
2 (year number,
3   month number,
4   prod varchar2(10),
5   quantity number);

테이블이 생성되었습니다.

SQL> insert into sales values(2006, 1, 'PS3', 25);

SQL> insert into sales values(2006, 5, 'PS3', 32);

SQL> insert into sales values(2006, 5, 'X-box', 54);

SQL> insert into sales values(2006, 6, 'PS3', 21);

SQL> insert into sales values(2006, 6, 'X-box', 67);

SQL> insert into sales values(2006, 7, 'PS3', 21);

SQL> insert into sales values(2006, 7, 'X-box', 26);

SQL> insert into sales values(2006, 8, 'X-box', 15);

SQL> insert into sales values(2006, 11, 'PS3', 27);

SQL> insert into sales values(2006, 12, 'PS3', 25);

SQL> insert into sales values(2006, 12, 'X-box', 15);

SQL> select * from sales
2 order by prod, year, month, quantity;

      YEAR      MONTH PROD         QUANTITY
---------- ---------- ---------- ----------
      2006          1          PS3                25
      2006          5          PS3                32
      2006          6          PS3                21
      2006          7          PS3                21
      2006         11          PS3                27
      2006         12          PS3                25
      2006          5         X-box              54
      2006          6         X-box              67
      2006          7         X-box              26
      2006          8         X-box            15
      2006         12         X-box              15

11 개의 행이 선택되었습니다.

작성하고자하는 결과 형식은 다음과 같습니다.

즉, 빨간색으로 표시된 행은 해당 년도 및 월에 해당 제품의 판매량이 존재하지 않음을 의미합니다.

예를 들어, PS3는 2006년도 2월에 판매량이 전혀 없음을 나타냅니다.

제품명           년도          월           수량
---------- ---------- ---------- ----------
PS3               2006          1             25
PS3               2006          2
PS3               2006          3
PS3               2006          4
PS3               2006          5             32
PS3               2006          6             21
PS3               2006          7             21
PS3               2006          8
PS3               2006          9
PS3               2006         10
PS3               2006         11            27
PS3               2006         12            25
X-box            2006          1
X-box            2006          2
X-box            2006          3
X-box            2006          4
X-box            2006          5             54
X-box            2006          6             67
X-box            2006          7             26
X-box            2006          8             15
X-box            2006          9
X-box            2006         10
X-box            2006         11
X-box            2006         12            15

이런 결과를 만들기 위해서는 sales 테이블을 prod 컬럼의 종류에 따라 파티션하고,

각 파티션을 times 테이블과 outer join을 수행해야합니다.

이를 위해서, Oracle 10g 부터는 손쉽게 사용 할 수 있는 Partitioned Outer Join을 소개하였습니다.

SQL> select s.prod, t.year, t.month, s.quantity
2 from sales s partition by (s.prod)
3 right outer join times t
4 on s.year=t.year and s.month=t.month;

PROD           YEAR      MONTH    QUANTITY
---------- ---------- ---------- ----------
PS3               2006          1             25
PS3               2006          2
PS3               2006          3
PS3               2006          4
PS3               2006          5             32
PS3               2006          6             21
PS3               2006          7             21
PS3               2006          8
PS3               2006          9
PS3               2006         10
PS3               2006         11            27
PS3               2006         12            25
X-box            2006          1
X-box            2006          2
X-box            2006          3
X-box            2006          4
X-box            2006          5             54
X-box            2006          6             67
X-box            2006          7             26
X-box            2006          8             15
X-box            2006          9
X-box            2006         10
X-box            2006         11
X-box            2006         12            15

24 개의 행이 선택되었습니다.

Thë 굽은ㄴr무™

[Oracle] PARTITION OUTER JOIN

+ Recent posts

티스토리툴바