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

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


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

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
     , 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 문두 [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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입