데이터베이스 질문과 답변
-- 마농님의 조언을 들어 질문 수정을 좀 했습니다.
table T
| DD | WON | KEY1 |
| 20150413 | 10000 | 00001 |
| 20150425 | 20000 | 00002 |
| 20150429 | 30000 | 00003 |
table S
| NO | SEQ | KEY1 | GB | SCT |
| 01 | 001 | 00001 | A | a |
| 01 | 001 | 00001 | B | a |
| 02 | 002 | 00002 | C | a |
| 03 | 003 | 00003 | D | a |
업무 도중 Rollup을 사용하는 쿼리가 있는데 제 머리로는 도저히 풀리지가 않네요. 도움 부탁드립니다.
위와 같은 테이블이 두개 있습니다.
rollup을 사용하여 SCT 컬럼 기준 소계와 총계를 생성해야 하는데요.
현재 작성한 쿼리와 결과 입니다.
SELECT S.SCT
, NVL(S.GB,'Z') GB
, SUM(DECODE(SUBSTR(T.DD,5,2),'04',T.WON,0) AMT
FROM
(
WITH T AS
(
SELECT '20150413' DD, '10000' WON, '00001' KEY1 FROM DUAL
UNION ALL SELECT '20150425' DD, '20000' WON, '00002' KEY1 FROM DUAL
UNION ALL SELECT '20150429' DD, '30000' WON, '00003' KEY1 FROM DUAL
)
SELECT * FROM T
) T
,
(
WITH S AS
(
SELECT '01' NO, '001' SEQ, '00001' KEY1, 'A' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '01' NO, '001' SEQ, '00001' KEY1, 'B' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '02' NO, '002' SEQ, '00002' KEY1, 'C' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '03' NO, '003' SEQ, '00003' KEY1, 'D' GB, 'a' SCT FROM DUAL
)
SELECT * FROM S
) S
WHERE T.KEY1 = S.KEY1(+)
GROUP BY CUBE(S.SCT, S.GB) --ROLLUP(S.SCT), ROLLUP(S.GB)
ORDER BY S.SCT, SGB
| SCT | GB | AMT |
| a | A | 10000 |
| a | B | 10000 |
| a | C | 20000 |
| a | D | 30000 |
| a | Z | 70000 |
| A | 10000 | |
| B | 10000 | |
| C | 20000 | |
| D | 30000 | |
| Z | 70000 |
여기서 문제는 붉은색으로 표시한 합계 금액이 'SCT'컬럼 기준이 아니라 'S'테이블에 'SEQ' 기준으로 SUM 된 값을 보여줘야 하는 겁니다. 출력 양식은 이전과 같게 나오게 해야합니다.
SEQ가 같은건 합계 낼때 하나만 합계가 되는거죠.
예를 들면
SEQ 이 001 건은 2건이지만 SEQ가 동일하니 => 10000,
SEQ 이 002 건은 1건이라 => 20000,
SEQ 이 003 건은 1건이라 => 30000,
총계는 10000 + 20000 + 30000 = 60000 이어야 합니다.
ROLLUP이나 CUBE 시 사용했던 그룹 조건이랑 다른 조건으로 합계가 나와야 하는겁니다.
결론적으로 아래와 같은 표의 값이 나와야 하는거죠...
| SCT | GB | AMT | SEQ |
| a | A | 10000 | 001 |
| a | B | 10000 | 001 |
| a | C | 20000 | 002 |
| a | D | 30000 | 003 |
| a | Z | 60000 - S TABLE에 SEQ 컬럼 기준 SUM 값 | |
| A | 10000 | 001 | |
| B | 10000 | 001 | |
| C | 20000 | 002 | |
| D | 30000 | 003 | |
| Z | 60000 - S TABLE에 SEQ 컬럼 기준 SUM 값 |
설명이 잘 된건지 모르겠습니다. 쿼리랑 결과를 실제 실행한 값을 넣고 싶지만 망분리가 된 회사라 직접 작성해서 쿼리가 잘 실행 안될 수도 있네요 ㅠㅠ
부탁드립니다.^^;
1. Rollup 2개를 함께 사용할 수 있다는게 흥미롭네요.
이렇게 사용할 생각 자체를 안해봐서 이런 구문이 가능할지는 생각도 못햇네요.
그러나 rollup 2개 사용한 결과는 결국 Cube 를 한번 사용한거랑 같은 결과네요.
2. 예시 및 결과 자료 및 설명이 이해하기 어렵네요.
- 예시자료가 부족하고 설명이 난해합니다.
- 1가지가 아닌 2가지 이상의 예시를 가지고 결과자료를 만들어 설명해 보세요.
질문 수정했습니다. 부탁드립니다^^
WITH T AS
(
SELECT '20150413' DD, '10000' WON, '00001' KEY1 FROM DUAL
UNION ALL SELECT '20150425' DD, '20000' WON, '00002' KEY1 FROM DUAL
UNION ALL SELECT '20150429' DD, '30000' WON, '00003' KEY1 FROM DUAL
),
S AS
(
SELECT '01' NO, '001' SEQ, '00001' KEY1, 'A' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '01' NO, '001' SEQ, '00001' KEY1, 'B' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '02' NO, '002' SEQ, '00002' KEY1, 'C' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '03' NO, '003' SEQ, '00003' KEY1, 'D' GB, 'a' SCT FROM DUAL
)
SELECT SCT,GB,DECODE(GROUPING(GB),0,SUM(AMT),SUM(AMT/CNT)) AMT
FROM (
SELECT S.SCT
, COUNT(SEQ) OVER(PARTITION BY SEQ) CNT
, NVL(S.GB,'Z') GB
, DECODE(SUBSTR(T.DD,5,2),'04',T.WON,0) AMT
FROM T,S
WHERE T.KEY1 = S.KEY1(+))
GROUP BY CUBE(SCT,GB)
ORDER BY SCT, GB ;
감사합니다. 많은 도움이 되었네요..
덕분에 수월하게 진행할 수 있었습니다.
WITH T AS(
SELECT '20150413' DD, '10000' WON, '00001' KEY1 FROM DUAL
UNION ALL SELECT '20150425' DD, '20000' WON, '00002' KEY1 FROM DUAL
UNION ALL SELECT '20150429' DD, '30000' WON, '00003' KEY1 FROM DUAL
)
, S AS(
SELECT '01' NO, '001' SEQ, '00001' KEY1, 'A' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '01' NO, '001' SEQ, '00001' KEY1, 'B' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '02' NO, '002' SEQ, '00002' KEY1, 'C' GB, 'a' SCT FROM DUAL
UNION ALL SELECT '03' NO, '003' SEQ, '00003' KEY1, 'D' GB, 'a' SCT FROM DUAL
)
SELECT S.SCT
, NVL(S.GB,'Z') GB
, DECODE(S.GB,NULL, ROUND(SUM(DISTINCT MIN(T.WON) + MIN(T.KEY1)/10000000000) OVER(PARTITION BY S.SCT))
, SUM(DECODE(SUBSTR(T.DD,5,2),'04',T.WON,0))) AMT
FROM T, S
WHERE T.KEY1 = S.KEY1(+)
GROUP BY CUBE(S.SCT, S.GB) --ROLLUP(S.SCT), ROLLUP(S.GB)
ORDER BY S.SCT, S.GB
;
감사합니다. 주신 쿼리 잘 분석해서 머리에 새겨야 할 것 같습니다
) 버튼을 클릭하여 작성 하시면 됩니다.