꿈꾸는 개발자, DBA 커뮤니티 구루비

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

by 마농 partition outer join rollup [2011.12.21 11:34:36]

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

코드테이블에는 3가지 코드값이 들어 있습니다.
이력테이블에는 일자별 코드에 해당하는 값이 저장되어 있습니다.
3개 코드가 모두 입력되는 날도 있지만 일부 코드만 입력되는 날도 있습니다.
일자별 코드별 코드명과 합계가 출력되는 쿼리를 작성하세요.
일부 코드만 입력된 날짜도 3개 코드가 모두 나와야 합니다.
일자별 소계도 함께 출력하세요.

CODE 테이블		DATA 테이블			결과 테이블
CD	NM	DT	CD	V	DT	NM	V
1	PC	20111201	1	10	20111201	PC	10
2	Monitor	20111201	2	20	20111201	Monitor	63
3	NoteBook	20111201	2	21	20111201	NoteBook	30
		20111201	2	22	20111201	소계	103
		20111201	3	30	20111202	PC	10
		20111202	1	10	20111202	Monitor	0
		20111202	3	30	20111202	NoteBook	30
		20111204	1	10	20111202	소계	40
		20111204	2	40	20111204	PC	10
		20111205	3	50	20111204	Monitor	40
					20111204	NoteBook	0
					20111204	소계	50
					20111205	PC	0
					20111205	Monitor	0
					20111205	NoteBook	50
					20111205	소계	50

다음은 원본 테이블 자료입니다.

[정답보기] <=== 트리플클릭
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))
;

by guest [2011.12.22 16:22:33]

SELECT A.DT, DECODE(GROUPING(A.NM),0,A.NM,'소계') AS NM, SUM(NVL(B.V,0)) AS V
FROM (
SELECT B.DT, A.CD, MIN(A.NM) NM
FROM CODE A,
DATA B
GROUP BY B.DT, A.CD
) A ,
(
SELECT DT, CD, SUM(V) V
FROM DATA
GROUP BY DT, CD
ORDER BY DT
) B
WHERE A.DT = B.DT(+)
AND A.CD = B.CD(+)
GROUP BY ROLLUP(A.DT,A.NM)
HAVING GROUPING(A.DT) = 0

by 문두 [2011.12.23 10:41:50]

WITH code AS
(
SELECT 1 cd, 'PC' nm FROM dual
UNION ALL SELECT 2, 'Monitor' FROM dual
UNION ALL SELECT 3, 'NoteBook' FROM dual
)
, data AS
(
SELECT '20111201' dt, 1 cd, 10 v FROM dual
UNION ALL SELECT '20111201', 2, 20 FROM dual
UNION ALL SELECT '20111201', 2, 21 FROM dual
UNION ALL SELECT '20111201', 2, 22 FROM dual
UNION ALL SELECT '20111201', 3, 30 FROM dual
UNION ALL SELECT '20111202', 1, 10 FROM dual
UNION ALL SELECT '20111202', 3, 30 FROM dual
UNION ALL SELECT '20111204', 1, 10 FROM dual
UNION ALL SELECT '20111204', 2, 40 FROM dual
UNION ALL SELECT '20111205', 3, 50 FROM dual
)
, TEMP AS
(
SELECT DISTINCT
dt
FROM data
)
SELECT A.DT
, CASE WHEN GROUPING(A.CD) = 0
THEN MAX(A.NM)
ELSE '소계'
END AS NM
, NVL(SUM(B.V),0) AS V
FROM (
SELECT *
FROM CODE A
, TEMP B
) A
, data B
WHERE B.DT(+) = A.DT
AND B.CD(+) = A.CD
GROUP BY ROLLUP(A.DT, A.CD)
HAVING GROUPING(A.DT) = 0

by xx [2011.12.23 13:40:18]

WITH code AS
(
SELECT 1 cd, 'PC' nm FROM dual
UNION ALL SELECT 2, 'Monitor' FROM dual
UNION ALL SELECT 3, 'NoteBook' FROM dual
)
, data AS
(
SELECT '20111201' dt, 1 cd, 10 v FROM dual
UNION ALL SELECT '20111201', 2, 20 FROM dual
UNION ALL SELECT '20111201', 2, 21 FROM dual
UNION ALL SELECT '20111201', 2, 22 FROM dual
UNION ALL SELECT '20111201', 3, 30 FROM dual
UNION ALL SELECT '20111202', 1, 10 FROM dual
UNION ALL SELECT '20111202', 3, 30 FROM dual
UNION ALL SELECT '20111204', 1, 10 FROM dual
UNION ALL SELECT '20111204', 2, 40 FROM dual
UNION ALL SELECT '20111205', 3, 50 FROM dual
)
select d.dt,
decode(grouping_id(c.cd,c.nm),0,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))
order by d.dt,c.cd

by xx [2011.12.23 13:42:41]

관련자료...
http://cafe.daum.net/jino1234/IL9V/14?docid=12VUL|IL9V|14|20090513101240&q=%BF%C0%B6%F3%C5%AC10g%20outer%20join

by 문두 [2011.12.23 14:03:35]

감사합니다...또하나배워가네요!

by ljw [2011.12.23 16:19:18]

WITH code AS(
SELECT 1 cd, 'PC' nm FROM dual
UNION ALL SELECT 2, 'Monitor' FROM dual
UNION ALL SELECT 3, 'NoteBook' FROM dual
),

calendar AS(
SELECT '20111201' dt FROM dual
UNION ALL
SELECT '20111202' dt FROM dual
UNION ALL
SELECT '20111203' dt FROM dual
UNION ALL
SELECT '20111204' dt FROM dual
UNION ALL
SELECT '20111205' dt FROM dual
)

, data AS
(
SELECT '20111201' dt, 1 cd, 10 v FROM dual
UNION ALL SELECT '20111201', 2, 20 FROM dual
UNION ALL SELECT '20111201', 2, 21 FROM dual
UNION ALL SELECT '20111201', 2, 22 FROM dual
UNION ALL SELECT '20111201', 3, 30 FROM dual
UNION ALL SELECT '20111202', 1, 10 FROM dual
UNION ALL SELECT '20111202', 3, 30 FROM dual
UNION ALL SELECT '20111204', 1, 10 FROM dual
UNION ALL SELECT '20111204', 2, 40 FROM dual
UNION ALL SELECT '20111205', 3, 50 FROM dual
)

select decode(GROUPING(aa.nm), 0, aa.nm, '일별소계')nm, aa.dt, nvl(sum(b.v), 0) score
from (select a.cd, a.nm, b.dt
from code a,
calendar b) aa, data b
where aa.dt = b.dt(+)
and aa.cd = b.cd(+)
group by ROLLUP( aa.nm), aa.dt
order by aa.dt, aa.nm

--partition by를 이용한 방법도 있군요, 잘 배웠습니다, 감사합니다 +_ +

by v상이v [2011.12.23 17:05:56]

SELECT A.DT,DECODE(GROUPING(B.NM),1,'소계',B.NM) NM,SUM(NVL(C.V,0)) V
FROM (SELECT DISTINCT DT FROM DATA) A
CROSS JOIN (SELECT CD,NM FROM CODE) B
LEFT OUTER JOIN (SELECT DT,CD,SUM(V) V FROM DATA GROUP BY DT,CD) C
ON A.DT = C.DT
AND B.CD = C.CD
GROUP BY A.DT, ROLLUP((A.DT,B.NM))

by 마농 [2011.12.26 09:17:23]

xx 님 정답입니다.

by xx [2011.12.26 14:13:21]

ㅎㅎ 저도 partition outer join 사용할 일이 없어서 까먹고 있었는데 태그보고 생각나더군요 ㅎ

by 선모 [2011.12.28 11:43:35]

select b.dt, a.cd , decode(grouping(a.nm), 1, '소계', a.nm) nm , sum(nvl(b.v, 0)) v
from code a left outer join
(select dt, cd, sum(v) v from data
group by dt, cd ) b partition by(b.dt)
on a.cd = b.cd
group by b.dt, rollup((a.cd, a.nm))

by 손님 [2012.01.10 09:20:39]

SELECT DT, DECODE(CD,NULL,'소계',NM) AS NM , V
FROM (
SELECT D.DT, C.CD, MAX(DECODE(C.CD,D.CD,C.NM,C.NM)) AS NM, SUM(DECODE(C.CD,D.CD,D.V,0)) AS V
FROM code C
,data D
GROUP BY ROLLUP(D.DT, C.CD)
HAVING GROUPING(DT) <> 1
)

partition outer join 이라는게 있다는건 처음 알았네영.. --a
오라클 쿼리는 어디까지인지..

by 이강호 [2012.03.13 11:19:50]

저도 잘배웠습니다. 파티션도 유용할듯하고요 rollup 사용시 괄호를 한개더 붙여줬을 때 다른 출력이 나오는것도 유용할듯 하네요.

by 손님 [2012.09.26 16:47:28]

SELECT *
FROM   (
    SELECT d.dt dt ,
   NVL( c.nm , '소계' ) nm ,
   NVL( SUM( d.v ) , 0 ) v
    FROM   code_t c left outer join data_t d PARTITION BY ( d.dt ) ON c.cd = d.cd
    GROUP BY rollup( d.dt , c.nm )
   )
WHERE dt IS NOT NULL

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 퀴즈 및 튜닝 문제