데이터베이스 질문과 답변
so_gubun 조회를 빼먹었었네요. 수정했습니다.
WITH temp AS
(
SELECT 1 no, '19990601' uncash_date, '1' so_gubun, 100 bill_amt, 'TEST1' cust_name FROM dual
UNION ALL SELECT 2, '19990601', '2', 200, 'TEST2' FROM dual
UNION ALL SELECT 3, '19990601', '1', 300, 'TEST3' FROM dual
UNION ALL SELECT 4, '19990601', '2', 400, 'TEST4' FROM dual
UNION ALL SELECT 5, '19990602', '1', 500, 'TEST5' FROM dual
UNION ALL SELECT 6, '19990602', '1', 600, 'TEST6' FROM dual
UNION ALL SELECT 7, '19990602', '1', 700, 'TEST7' FROM dual
UNION ALL SELECT 8, '19990603', '2', 800, 'TEST8' FROM dual
UNION ALL SELECT 9, '19990603', '2', 900, 'TEST9' FROM dual
)
SELECT CASE GROUPING_ID(4,3,uncash_date,2,1,no)
WHEN 0 THEN uncash_date
WHEN 1 THEN '1계'
WHEN 3 THEN '2계'
WHEN 7 THEN '합계'
WHEN 15 THEN '총1계'
WHEN 31 THEN '총2계'
WHEN 63 THEN '총합계'
END AS uncash_date
, so_gubun
, CASE GROUPING_ID(4,3,uncash_date,2,1,no)
WHEN 0 THEN SUM(bill_amt)
WHEN 1 THEN SUM(DECODE(so_gubun,1,bill_amt))
WHEN 3 THEN SUM(DECODE(so_gubun,2,bill_amt))
WHEN 7 THEN SUM(bill_amt)
WHEN 15 THEN SUM(DECODE(so_gubun,1,bill_amt))
WHEN 31 THEN SUM(DECODE(so_gubun,2,bill_amt))
WHEN 63 THEN SUM(bill_amt)
END AS bill_amt
, cust_name
FROM temp
GROUP BY ROLLUP(4, 3, uncash_date, 2, 1, (no, so_gubun, cust_name))
;
테스트 해보시면 총합계가 가장아래로 와야하는데..
그게 힘드내요.. 5시간 삽질하다. GG 하고 있다 봐야지했는데..
역쉬~~~ 마농님 완전짱인듯... 커리이것좀 봐주세영~
WITH TEMP AS(
SELECT 1 NO,'19990601' UNCASH_DATE,'1' SO_GUBUN,100 BILL_AMT,'TEST1' CUST_NAME FROM DUAL UNION ALL
SELECT 2 NO,'19990601' UNCASH_DATE,'2' SO_GUBUN,200 BILL_AMT,'TEST2' CUST_NAME FROM DUAL UNION ALL
SELECT 3 NO,'19990601' UNCASH_DATE,'1' SO_GUBUN,300 BILL_AMT,'TEST3' CUST_NAME FROM DUAL UNION ALL
SELECT 4 NO,'19990601' UNCASH_DATE,'2' SO_GUBUN,400 BILL_AMT,'TEST4' CUST_NAME FROM DUAL UNION ALL
SELECT 5 NO,'19990602' UNCASH_DATE,'1' SO_GUBUN,500 BILL_AMT,'TEST5' CUST_NAME FROM DUAL UNION ALL
SELECT 6 NO,'19990602' UNCASH_DATE,'1' SO_GUBUN,600 BILL_AMT,'TEST6' CUST_NAME FROM DUAL UNION ALL
SELECT 7 NO,'19990602' UNCASH_DATE,'1' SO_GUBUN,700 BILL_AMT,'TEST7' CUST_NAME FROM DUAL UNION ALL
SELECT 8 NO,'19990603' UNCASH_DATE,'2' SO_GUBUN,800 BILL_AMT,'TEST8' CUST_NAME FROM DUAL UNION ALL
SELECT 9 NO,'19990603' UNCASH_DATE,'2' SO_GUBUN,900 BILL_AMT,'TEST9' CUST_NAME FROM DUAL
)
SELECT
MIN(NO)
, UNCASH_DATE
, MAX(SO_GUBUN) AS SO_GUBUN
, SUM(BILL_AMT)
, CUST_NAME
, RN
FROM
(
SELECT
NO
, DESCTION
, GROUP_ID
--, RN
--, CASE WHEN RN = 1 THEN RN+SO_GUBUN ELSE RN+5 END AS T
, DECODE(RN,0,ROW_NUMBER() OVER(PARTITION BY RN ORDER BY NO),RN) AS RN
, ASCE2
, UNCASH_DATE
, SO_GUBUN
, BILL_AMT
, CUST_NAME
FROM
(
SELECT
ROWNUM AS NO
, DESCTION
, GROUP_ID
--, RN
--, CASE WHEN RN = 1 THEN RN+SO_GUBUN ELSE RN+5 END AS T
, DECODE(RN,1,RN+SO_GUBUN, 2, RN+ 5 ,3,RN+10,4,0, RN) AS RN
, ASCE2
, UNCASH_DATE
, SO_GUBUN
, BILL_AMT
, CUST_NAME
FROM
(
SELECT
GROUPING(NO)+GROUPING(UNCASH_DATE)+GROUPING(UNCASH_DATE)+GROUPING(CUST_NAME) AS DESCTION
, GROUP_ID() AS GROUP_ID
, GROUPING(NO)+GROUPING(UNCASH_DATE)+GROUPING(UNCASH_DATE)+GROUPING(CUST_NAME)+GROUP_ID() AS RN
, UNCASH_DATE||DECODE(CUST_NAME,'TEST1','1','TEST2','2','TEST3','3','TEST4','4','TEST5','5','TEST6','6','TEST7','7','TEST8','8','9')||GROUP_ID()||SO_GUBUN AS ASCE2
, UNCASH_DATE
, (SO_GUBUN)
, SUM(BILL_AMT) AS BILL_AMT
, CUST_NAME
FROM TEMP
GROUP BY ROLLUP(NO,UNCASH_DATE,UNCASH_DATE,CUST_NAME ),SO_GUBUN
ORDER BY UNCASH_DATE||DECODE(CUST_NAME,'TEST1','1','TEST2','2','TEST3','3','TEST4','4','TEST5','5','TEST6','6','TEST7','7','TEST8','8','9')||GROUP_ID()||SO_GUBUN ASC
)
)
ORDER BY NO
)
GROUP BY
UNCASH_DATE
-- , SO_GUBUN
, CUST_NAME
, RN
ORDER BY MIN(NO)
ROLLUP(4, 3, uncash_date, 2, 1, (so_gubun, no, cust_name))
이거 살짝 설명좀 해주심 안될가요??
Rollup(a, b, c, d) 를 할 경우
결과는 5가지 Group by 결과가 나옵니다.
Group by 항목을 맨 오른쪽 항목부터 차례대로 없앤 결과입니다.
즉,
1. Group by a, b, c, d
2. Group by a, b, c
3. Group by a, b
4. Group by a
5. Group by ''
Rollup에 대한 이해가 끝났다면 다음엔 응용입니다.
Rollup 항목에 컬럼대신 상수값이 올 경우입니다.
이 경우에도 그 원리는 변하지 않고 그대로 적용됩니다.
간단하게
GROUP BY ROLLUP(uncash_date, 2, 1, no)을 했다면
1. GROUP BY uncash_date, 2, 1, no
2. GROUP BY uncash_date, 2, 1
3. GROUP BY uncash_date, 2
4. GROUP BY uncash_date
5. GROUP BY ''
이런식의 결과가 도출됩니다.
그러나 3, 4번은 결국 2번과 같은 결과가 한줄 씩 더 생긴다고 생각하시면 됩니다.
상수값은 group by에서 영향력이 없기 때문입니다.
같은 Group By 결과가 두개가 더 생겨났습니다.
이제 이 결과들을 구분할 필요가 있습니다.
이 똑같이 생긴 결과를 구분할 수 있도록 해주는것이 바로
Grouping(), grouping_id(), group_id() 등이 되겠습니다.
이것을 이용해 결과를 구분하여 적당히 가공하여 원하시는 결과를 얻도록 할 수 있습니다.
좋은 강의 잘 봤습니다 m(_ _)m
정말 감사합니다. 복받으실꺼에요~ (--)(__)
WITH T AS
(
SELECT 1 NO,'19990601' UNCASH_DATE,'1' SO_GUBUN,100 BILL_AMT,'TEST1' CUST_NAME FROM DUAL UNION ALL
SELECT 2 NO,'19990601' UNCASH_DATE,'2' SO_GUBUN,200 BILL_AMT,'TEST2' CUST_NAME FROM DUAL UNION ALL
SELECT 3 NO,'19990601' UNCASH_DATE,'1' SO_GUBUN,300 BILL_AMT,'TEST3' CUST_NAME FROM DUAL UNION ALL
SELECT 4 NO,'19990601' UNCASH_DATE,'2' SO_GUBUN,400 BILL_AMT,'TEST4' CUST_NAME FROM DUAL UNION ALL
SELECT 5 NO,'19990602' UNCASH_DATE,'1' SO_GUBUN,500 BILL_AMT,'TEST5' CUST_NAME FROM DUAL UNION ALL
SELECT 6 NO,'19990602' UNCASH_DATE,'1' SO_GUBUN,600 BILL_AMT,'TEST6' CUST_NAME FROM DUAL UNION ALL
SELECT 7 NO,'19990602' UNCASH_DATE,'1' SO_GUBUN,700 BILL_AMT,'TEST7' CUST_NAME FROM DUAL UNION ALL
SELECT 8 NO,'19990603' UNCASH_DATE,'2' SO_GUBUN,800 BILL_AMT,'TEST8' CUST_NAME FROM DUAL UNION ALL
SELECT 9 NO,'19990603' UNCASH_DATE,'2' SO_GUBUN,900 BILL_AMT,'TEST9' CUST_NAME FROM DUAL
)
select decode(gid,1,so_gubun||'계',3,'합계',5,'총'||so_gubun||'계',7,'총합계',uncash_date) uncash_date
, so_gubun
, bill_amt
, cust_name
from (
select uncash_date, so_gubun, cust_name, sum(bill_amt) bill_amt
, grouping_id(uncash_date, so_gubun, cust_name) gid
from T
group by cube(uncash_date, so_gubun, cust_name)
order by uncash_date, gid
)
where gid not in (2,4,6);
) 버튼을 클릭하여 작성 하시면 됩니다.