기본쿼리는 이제 좀 알겠는데 ROLLUP을 아직 잘 이해가 안가네요 0 4 189

by 권오창 [2019.09.09 21:00:52]


합산 SUB TOTAL을 좀 구해볼려고 ROLLUP이란걸 보고 있습니다.

WITH T AS
(
    SELECT '1-1' AS CT,'서부' AS 지역,'A' AS 등급,'1C' AS 클래스,'38' AS 서브코드,'AMD' AS CPU,'5*5*6' AS 공간,'Prod' AS 구매형태,'PC' AS 종류,'SN12345' AS 그룹SN,'30' AS 수량,'3.58' AS 단가,'0.58' AS 부가세,'M197-34227' AS 입고코드,'DANAWA' AS 대행사,'0.373' AS 이자 FROM DUAL UNION ALL
    SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','34','0.57','1.1','M197-34269','DANAWA','0.125' FROM DUAL UNION ALL
    SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','32','0.56','2.03','M197-34270','DANAWA','0.553' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','29','0.52','0.54','M198-33535','DANAWA','0.124' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','18','0.45','0.52','M198-32509','DANAWA','0.334' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','13','0.51','0.49','M198-33533','DANAWA','0.441' FROM DUAL 
)
SELECT * FROM T

이런 데이터가 있습니다.

1-1 서부 A 1C 38 AMD 5*5*6 Prod PC SN12345 30 3.58 0.58 M197-34227 DANAWA 0.373
1-1 서부 A 1C 38 AMD 5*5*6 Prod PC SN12345 34 0.57 1.1 M197-34269 DANAWA 0.125
1-1 서부 A 1C 38 AMD 5*5*6 Prod PC SN12345 32 0.56 2.03 M197-34270 DANAWA 0.553
1-2 동부 B 2C 14 INTEL 5*5*6 Prod PC SN67890 29 0.52 0.54 M198-33535 DANAWA 0.124
1-2 동부 B 2C 14 INTEL 5*5*6 Prod PC SN67890 18 0.45 0.52 M198-32509 DANAWA 0.334
1-2 동부 B 2C 14 INTEL 5*5*6 Prod PC SN67890 13 0.51 0.49 M198-33533 DANAWA

0.441

이런 데이터 입니다

그런데 저 중간중간 행에 첫번째 열에 대해서 합산값을 표시해야 됩니다. 수량, 단가, 부가세, 이자 만 밑에 줄에 나와야 됩니다.

1-1 서부 A 1C 38 AMD 5*5*6 Prod PC SN12345 30 3.58 0.58 M197-34227 DANAWA 0.373
1-1 서부 A 1C 38 AMD 5*5*6 Prod PC SN12345 34 0.57 1.1 M197-34269 DANAWA 0.125
1-1 서부 A 1C 38 AMD 5*5*6 Prod PC SN12345 32 0.56 2.03 M197-34270 DANAWA 0.553
                    96 4.71 3.71     1.05
1-2 동부 B 2C 14 INTEL 5*5*6 Prod PC SN67890 29 0.52 0.54 M198-33535 DANAWA 0.124
1-2 동부 B 2C 14 INTEL 5*5*6 Prod PC SN67890 18 0.45 0.52 M198-32509 DANAWA 0.334
1-2 동부 B 2C 14 INTEL 5*5*6 Prod PC SN67890 13 0.51 0.49 M198-33533 DANAWA 0.441
                    60 1.48 1.55     0.899

 

WITH T AS
(
    SELECT '1-1' AS CT,'서부' AS REGION,'A' AS GRADE,'1C' AS CLAS,'38' AS SUBCD,'AMD' AS CPU,'5*5*6' AS AREA,'Prod' AS BUYTYPE,'PC' AS TYPE,'SN12345' AS GRPSN,'30' AS QTY,'3.58' AS PRICE,'0.58' AS ADDER,'M197-34227' AS INCD,'DANAWA' AS SITE,'0.373' AS COST FROM DUAL UNION ALL
    SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','34','0.57','1.1','M197-34269','DANAWA','0.125' FROM DUAL UNION ALL
    SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','32','0.56','2.03','M197-34270','DANAWA','0.553' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','29','0.52','0.54','M198-33535','DANAWA','0.124' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','18','0.45','0.52','M198-32509','DANAWA','0.334' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','13','0.51','0.49','M198-33533','DANAWA','0.441' FROM DUAL 
)
SELECT 
    CT
    , REGION
    , GRADE
    , CLAS
    , SUBCD
    , CPU
    , AREA
    , BUYTYPE
    , TYPE
    , GRPSN
    , QTY
    , PRICE
    , ADDER
    , INCD
    , SITE
    , COST
FROM T
GROUP BY
    ROLLUP(~~~~~)

 

이 뒤를 아무리 해봐도 잘 모르겠습니다.

도와주세요..

by jkson [2019.09.10 08:54:28]

예전에 마농님이 rollup을 아주 쉽게 설명해주셨던 자료가 있었는데
어디 놔뒀는지 모르겠네요.
요약하자면 rollup은 나열된 컬럼을 오른쪽에서 부터 하나씩 제거하면서 group by 한다고 생각하시면 됩니다.

with t(gb1, gb2, gb3, val) as

(
select 'A', 'B', 'C', 1 from dual union all
select 'A', 'B', 'C', 2 from dual union all
select 'A', 'B', 'C', 3 from dual
)
select gb1, gb2, gb3, sum(val)
  from t
 group by rollup(gb1, gb2, gb3)
-> gb1, gb2, gb3 으로 group by 한 것
-> gb1, gb2로 group by 한 것
-> gb1로 group by 한 것
-> 전체 group by 한 것

만약

group by rollup((gb1, gb2), gb3) 로 그룹바이 하면
-> gb1, gb2, gb3으로 group by 한 것
-> gb1, gb2으로 group by 한 것
-> 전체 group by 한 것

이런 개념으로 생각하시면 이해가 쉬우실듯하고요.

질문하신 것은 같은 원리로

SELECT CT, 지역, 등급, 클래스, 서브코드, CPU, 공간, 구매형태, 종류, 그룹SN, SUM(수량), SUM(단가), SUM(부가세), 입고코드, 대행사, SUM(이자)
  FROM T
 GROUP BY CT, ROLLUP((지역, 등급, 클래스, 서브코드, CPU, 공간, 구매형태, 종류, 그룹SN, 입고코드, 대행사))

요렇게 하시면 되겠네요.
전체 group by 는 필요 없고 ct별 sum만 필요하니 위와 같이 해주시면 되겠습니다.


by 꼬랑지 [2019.09.10 08:56:11]
WITH T AS
(
    SELECT '1-1' AS CT,'서부' AS 지역,'A' AS 등급,'1C' AS 클래스,'38' AS 서브코드,'AMD' AS CPU,'5*5*6' AS 공간,'Prod' AS 구매형태,'PC' AS 종류,'SN12345' AS 그룹SN,'30' AS 수량,'3.58' AS 단가,'0.58' AS 부가세,'M197-34227' AS 입고코드,'DANAWA' AS 대행사,'0.373' AS 이자 FROM DUAL UNION ALL
    SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','34','0.57','1.1','M197-34269','DANAWA','0.125' FROM DUAL UNION ALL
    SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','32','0.56','2.03','M197-34270','DANAWA','0.553' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','29','0.52','0.54','M198-33535','DANAWA','0.124' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','18','0.45','0.52','M198-32509','DANAWA','0.334' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','13','0.51','0.49','M198-33533','DANAWA','0.441' FROM DUAL 
)
SELECT 
CT,지역,등급,클래스,서브코드,CPU,공간,구매형태,종류,그룹SN,SUM(수량),SUM(단가),SUM(부가세),입고코드,대행사,SUM(이자)
FROM T
GROUP BY (CT,지역,등급),ROLLUP((클래스,서브코드,CPU,공간,구매형태,종류,그룹SN,입고코드,대행사,수량,단가,부가세,이자))

 


by 농부지기 [2019.09.10 09:25:13]

저도 rollup (( ) ) 괄호 2개가 궁금했는데.. 자세한 설명 감사합니다.
안쪽 괄호는 sum은 하지만 ..괄호안의 각 컬럼별 sum은 안하는 구조군요.


by 춘 [2019.09.11 00:26:11]
WITH T AS
(
    SELECT '1-1' AS CT,'서부' AS 지역,'A' AS 등급,'1C' AS 클래스,'38' AS 서브코드,'AMD' AS CPU,'5*5*6' AS 공간,'Prod' AS 구매형태,'PC' AS 종류,'SN12345' AS 그룹SN,'30' AS 수량,'3.58' AS 단가,'0.58' AS 부가세,'M197-34227' AS 입고코드,'DANAWA' AS 대행사,'0.373' AS 이자 FROM DUAL UNION ALL
    SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','34','0.57','1.1','M197-34269','DANAWA','0.125' FROM DUAL UNION ALL
    SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','32','0.56','2.03','M197-34270','DANAWA','0.553' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','29','0.52','0.54','M198-33535','DANAWA','0.124' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','18','0.45','0.52','M198-32509','DANAWA','0.334' FROM DUAL UNION ALL
    SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','13','0.51','0.49','M198-33533','DANAWA','0.441' FROM DUAL 
)
SELECT
    CT,지역,등급,클래스,서브코드,CPU,공간,구매형태,종류,그룹SN,SUM(수량),SUM(단가),SUM(부가세),입고코드,대행사,SUM(이자)
FROM T
GROUP BY CT,지역,등급,클래스,서브코드,CPU,공간,구매형태,종류,그룹SN,대행사, ROLLUP(입고코드)
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입