10-1-1. UNION ALL
조회 결과에 합계를 처리하기 위해서는 일반적으로 ROLLUP을 사용한다. ROLLUP을 살펴보기 전에 UNION ALL을 사용한 합계 처리 기술을 살펴보자.
아래는 ShopOperTp(매장운영유형)별 주문년월별 주문건수를 조회하는 SQL이다.
-- [SQL-10-1-1-a]
SELECT T1.ShopOperTp
,DATE_FORMAT(T2.OrdDtm,'%Y%m') YM
,COUNT(*) OrdCnt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210301','%Y%m%d')
GROUP BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m')
ORDER BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m');
ShopOperTp YM OrdCnt
---------- ------ ------
DIST 202101 44
DIST 202102 163
DRCT 202101 18
DRCT 202102 120
FLAG 202101 439
FLAG 202102 327
SQL
복사
위와 같은 결과에 전체 주문건수를 추가하고자 할 때 UNION ALL을 사용할 수 있다. 아래와 같이 위의 SQL과 전체 주문 건수를 구하는 SQL을 각각 작성해 UNION ALL로 결합하면 된다.
-- [SQL-10-1-1-b]
SELECT T1.ShopOperTp
,DATE_FORMAT(T2.OrdDtm,'%Y%m') YM
,COUNT(*) OrdCnt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210301','%Y%m%d')
GROUP BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m')
-- > ORDER BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m'): 이 곳에 ORDER BY 사용 불가
UNION ALL
SELECT 'Total' ShopOperTp
,'Total' YM
,COUNT(*) OrdCnt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210301','%Y%m%d')
ORDER BY ShopOperTp ,YM;
ShopOperTp YM OrdCnt
---------- ------ ------
DIST 202101 44
DIST 202102 163
DRCT 202101 18
DRCT 202102 120
FLAG 202101 439
FLAG 202102 327
Total Total 1111
SQL
복사
위 결과에 중간합계인 ShopOperTp별 주문 건수도 필요하다면 UNION ALL을 하나 더 추가하면 된다. 그러나 SQL 자체가 너무 길어지므로 아래와 같이 WITH 절을 활용해 간략화 할 수 있다. 기본 SQL인 GROUP BY SQL을 WITH 블록에서 정의한 후에, 해당 블록을 반복 사용해 필요한 중간합계를 구현하는 것이다.
-- [SQL-10-1-1-c]
WITH W1 AS(
SELECT T1.ShopOperTp
,DATE_FORMAT(T2.OrdDtm,'%Y%m') YM
,COUNT(*) OrdCnt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210301','%Y%m%d')
GROUP BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m')
)
SELECT 'Total' ShopOperTp ,'Total' YM ,SUM(T1.OrdCnt) OrdCnt
FROM W1 T1
UNION ALL
SELECT T1.ShopOperTp ,'Total' YM ,SUM(T1.OrdCnt) OrdCnt
FROM W1 T1
GROUP BY T1.ShopOperTp
UNION ALL
SELECT T1.ShopOperTp ,T1.YM ,T1.OrdCnt
FROM W1 T1;
ShopOperTp YM OrdCnt
---------- ------ ------
Total Total 1111
FLAG Total 766
DIST Total 207
DRCT Total 138
FLAG 202101 439
DIST 202101 44
DRCT 202101 18
FLAG 202102 327
DIST 202102 163
DRCT 202102 120
SQL
복사
위 결과에 대해 정렬 순서를 조정하고 싶다면, 아래와 같이 인라인 뷰에서 Sort 컬럼 두개를 임의로 추가해 처리할 수 있다. Sort1과 Sort2가 모두 0이면 전체 합계가 되고, Sort2만 0이면 ShopOperTp별 중간합계다.
-- [SQL-10-1-1-d]
WITH W1 AS(
SELECT T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m') YM ,COUNT(*) OrdCnt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210301','%Y%m%d')
GROUP BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m')
)
SELECT T2.ShopOperTp ,T2.YM ,T2.OrdCnt
FROM (
SELECT 0 Sort1 ,'Total' ShopOperTp ,0 Sort2 ,'Total' YM ,SUM(T1.OrdCnt) OrdCnt
FROM W1 T1
UNION ALL
SELECT 1 Sort1 ,T1.ShopOperTp ,0 Sort2 ,'Total' YM ,SUM(T1.OrdCnt) OrdCnt
FROM W1 T1
GROUP BY T1.ShopOperTp
UNION ALL
SELECT 1 Sort1 ,T1.ShopOperTp ,1 Sort2 ,T1.YM ,T1.OrdCnt
FROM W1 T1
) T2
ORDER BY T2.Sort1 DESC ,T2.ShopOperTp ASC ,T2.Sort2 DESC ,T2.YM ASC;
ShopOperTp YM OrdCnt
---------- ------ ------
DIST 202101 44
DIST 202102 163
DIST Total 207
DRCT 202101 18
DRCT 202102 120
DRCT Total 138
FLAG 202101 439
FLAG 202102 327
FLAG Total 766
Total Total 1111
SQL
복사
10-1-2. ROLLUP
ROLLUP을 사용하면 UNION ALL보다 간단하게 중간합계와 전체합계를 처리할 수 있다.
아래는 ShopOperTp, 주문년월(DATE_FORMAT(T2.OrdDtm,’%Y%m’))별 주문 건수를 구하는 SQL이다. 아직 중간합계 처리는 하지 않고 GROUP BY 만 적용한 상태다.
-- [SQL-10-1-2-a] 기본 GROUP BY 데이터
SELECT T1.ShopOperTp
,DATE_FORMAT(T2.OrdDtm,'%Y%m') YM
,COUNT(*) OrdCnt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210301','%Y%m%d')
GROUP BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m')
ORDER BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m');
ShopOperTp YM OrdCnt
---------- ------ ------
DIST 202101 44
DIST 202102 163
DRCT 202101 18
DRCT 202102 120
FLAG 202101 439
FLAG 202102 327
SQL
복사
위 SQL의 GROUP BY 마지막 부분에 WITH ROLLUP만 추가하면 자동으로 중간합계와 전체합계가 만들어진다.
-- [SQL-10-1-2-b] ROLLUP으로 중간합계와 전체합계 추가
SELECT T1.ShopOperTp
,DATE_FORMAT(T2.OrdDtm,'%Y%m') YM
,COUNT(*) OrdCnt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210301','%Y%m%d')
GROUP BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m') WITH ROLLUP -- > ROLLUP 추가
ORDER BY T1.ShopOperTp ,DATE_FORMAT(T2.OrdDtm,'%Y%m');
ShopOperTp YM OrdCnt
---------- ------ ------
NULL NULL 1111 -- > 전체합계
DIST NULL 207 -- > ShopOperTp(DIST) 중간합계
DIST 202101 44
DIST 202102 163
DRCT NULL 138 -- > ShopOperTp(DRCT) 중간합계
DRCT 202101 18
DRCT 202102 120
FLAG NULL 766 -- > ShopOperTp(FLAG) 중간합계
FLAG 202101 439
FLAG 202102 327
SQL
복사
ROLLUP은 GROUP BY 컬럼 순서에 따라 다른 중간합계를 만든다. 위 SQL에서 GROUP BY 순서만 변경해서 실행해보자.
-- [SQL-10-1-2-c] GROUP BY 컬럼 순서 변경
SELECT T1.ShopOperTp
,DATE_FORMAT(T2.OrdDtm,'%Y%m') YM
,COUNT(*) OrdCnt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210301','%Y%m%d')
GROUP BY DATE_FORMAT(T2.OrdDtm,'%Y%m') ,T1.ShopOperTp WITH ROLLUP -- > GROUP BY 컬럼 순서 변경
ORDER BY T1.ShopOperTp, DATE_FORMAT(T2.OrdDtm,'%Y%m');
ShopOperTp YM OrdCnt
---------- ------ ------
NULL NULL 1111 -- > 전체합계
NULL 202101 501 -- > YM(202101) 중간합계
NULL 202102 610 -- > YM(202102) 중간합계
DIST 202101 44
DIST 202102 163
DRCT 202101 18
DRCT 202102 120
FLAG 202101 439
FLAG 202102 327
SQL
복사
방금 실행한 두 SQL을 비교해보면 다음 그림과 같다.
위 그림의 왼쪽은 GROUP BY 순서가 ShopOperTp, YM이고 오른쪽은 YM, ShopOperTp다. 이 순서의 차이로 두 SQL은 다른 중간합계 데이터를 만든다.
•
GROUP BY ShopOperTp, YM
◦
ShopOperTp, YM별 집계 데이터(GROUP BY 컬럼)
◦
ShopOperTp별 중간합계 데이터
◦
전체합계 데이터
•
GROUP BY YM, ShopOperTp
◦
YM, ShopOperTp별 집계 데이터(GROUP BY 컬럼)
◦
YM별 중간합계 데이터
◦
전체합계 데이터
ROLLUP은 GROUP BY에 지정된 순서에 따라 가장 오른쪽 컬럼부터 차례대로 하나씩 제거하면서 중간합계를 만들어낸다. GROUP BY A, B, C, D와 GROUP BY B, A, C, D가 있다면, 각각 다음과 같은 중간합계가 구성된다.
구분 | GROUP BY A, B, C, D | GROUP BY B, A, C, D | 데이터 구분 |
첫 번째 중간합계 | A, B, C 별 중간합계 | B, A, C 별 중간합계 | 같은 데이터 |
두 번째 중간합계 | A, B 별 중간합계 | B, A 별 중간합계 | 같은 데이터 |
세 번째 중간합계 | A 별 중간합계 | B 별 중간합계 | 다른 데이터 |
첫 번째와 두 번째 중간합계는 컬럼 순서만 다른 같은 같은 데이터다. 하지만 세 번째 중간합계는 서로 다른 중간합계다. 우리가 원하는 중간합계가 무엇인지에 따라 GROUP BY 의 컬럼 순서를 적절하게 조정해야 한다.
Tip. 오라클의 중간합계
오라클은 ROLLUP 외에도 GROUPING SETS라는 중간합계 처리에 유용한 방법을 제공한다. 또한 ROLLUP 컬럼에도 괄호를 추가해 좀 더 유연하게 중간합계를 조정할 수 있다.
Tip. CUBE
DBMS에 따라 CUBE라는 합계 기능을 제공하기도 한다. CUBE는 다차워 합계로서 GROUP BY에 지정된 컬럼의 순서에 상관없이 가능한 모든 합계를 만들어낸다. 가능한 모든 합계를 만드므로 부하가 큰 편이다. 안타깝게도 MySQL(8.0.35 기준)은 CUBE 기능을 제공하지 않는다.
10-1-3. ROLLUP과 GROUPING
ROLLUP과 함께 사용하는 GROUPING이라는 함수가 있다. GROUPING은 함수에 입력한 컬럼이 집계된 데이터인지 판별해주는 기능을 한다. GROUPING에 입력된 컬럼이 집계된 상태면 1을 집계 상태가 아니면 0을 출력해준다.
-- [SQL-10-1-3-a]
SELECT T1.ItemCat ,GROUPING(T1.ItemCat) GR_ItemCat
,T1.HotColdCd ,GROUPING(T1.HotColdCd) GR_HotColdCd
,COUNT(*) ItemCnt
FROM startdb.Item T1
GROUP BY T1.ItemCat ,T1.HotColdCd WITH ROLLUP;
ItemCat GR_ItemCat HotColdCd GR_HotColdCd ItemCnt
------- ---------- --------- ------------ -------
BEV 0 COLD 0 3
BEV 0 HOT 0 2
BEV 0 NULL 1 5 -- > ItemCat별 중간합계, HotColdCd는 집계된 상태
BKR 0 COLD 0 2
BKR 0 HOT 0 2
BKR 0 NULL 1 4 -- > ItemCat별 중간합계, HotColdCd는 집계된 상테
COF 0 COLD 0 6
COF 0 HOT 0 6
COF 0 NULL 1 12 -- > ItemCat별 중간합계, HotColdCd는 집계된 상태
NULL 1 NULL 1 21 -- > 전체합계, ItemCat와 HotColdCd는 집계된 상태
SQL
복사
GROUPING 결과에 따라 명칭을 Total로 치환해서 보여줄 수 있다. 아래는 CASE를 사용해 GROUPING 결과에 따라 명칭을 처리한 SQL이다. GROUPING의 결과가 1이면 ‘Total’로 아니면 서브쿼리를 사용해 명칭을 처리한다.
-- [SQL-10-1-3-b]
SELECT CASE WHEN GROUPING(T1.ItemCat) = 1 THEN 'Total'
ELSE (SELECT MAX(A.ItemCatNm)
FROM startdb.ItemCat A
WHERE A.ItemCat = T1.ItemCat)
END ItemCat
,CASE WHEN GROUPING(T1.HotColdCd) = 1 THEN 'Total'
ELSE (SELECT MAX(A.BaseCdNm)
FROM startdb.BaseCd A
WHERE A.BaseCdDv = 'HotColdCd'
AND A.BaseCd = T1.HotColdCd)
END HotColdCd
,COUNT(*) ItemCnt
FROM startdb.Item T1
GROUP BY T1.ItemCat ,T1.HotColdCd
WITH ROLLUP;
ItemCat HotColdCd ItemCnt
-------- --------- -------
Beverage Cold 3
Beverage Hot 2
Beverage Total 5
Bakery Cold 2
Bakery Hot 2
Bakery Total 4
Coffee Cold 6
Coffee Hot 6
Coffee Total 12
Total Total 21
SQL
복사
위와 같이 GROUP BY와 CASE를 동시에 사용하는 것이 부담스럽다면, 인라인-뷰를 활용해 단계적으로 SQL을 개발하는 것도 좋다. MySQL의 경우 GROUP BY에서 컬럼을 변형하면서 ROLLUP과 GROUPING 그리고 CASE를 동시에 사용하면 SQL이 제대로 실행되지 않을 수도 있다. (버젼과 설정, SQL에 따라 달라진다.) 아래 패턴을 참고해보기 바란다.
-- [SQL-10-1-3-c]
SELECT CASE WHEN GR_ItemCat = 1 THEN 'Total'
ELSE (SELECT MAX(A.ItemCatNm)
FROM startdb.ItemCat A
WHERE A.ItemCat = T2.ItemCat)
END ItemCat
,CASE WHEN GR_HotColdCd = 1 THEN 'Total'
ELSE (SELECT MAX(A.BaseCdNm)
FROM startdb.BaseCd A
WHERE A.BaseCdDv = 'HotColdCd'
AND A.BaseCd = T2.HotColdCd)
END HotColdCd
,T2.ItemCnt
FROM (
SELECT T1.ItemCat ,T1.HotColdCd
,GROUPING(T1.ItemCat) GR_ItemCat
,GROUPING(T1.HotColdCd) GR_HotColdCd
,COUNT(*) ItemCnt
FROM startdb.Item T1
GROUP BY T1.ItemCat ,T1.HotColdCd
WITH ROLLUP
) T2;
SQL
복사
10-1. BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 10-1-1
현재 Active한 회원에 대해 회원등급별 회원수를 보여주세요. 회원등급은 코드값이 아닌 명칭으로 보여주세요. 전체 합계도 추가해서 표시해주세요.
•
대상 테이블: 회원(Member)
•
조회 조건: MemberSt가 ACTV인 데이터
•
조회 컬럼: MemberGdNm, MemberCnt
•
추가 조건
◦
ROLLUP을 사용해 전체 합계를 추가하시오.
◦
BaseCd 테이블을 사용해 MemberGd의 명칭을 보여주시요.(MemberGdNm)
◦
GROUPING을 사용해 합계인 경우에는 MemberGdNm을 Total로 보여주세요.
MemberGdNm MemberCnt
---------- ---------
Gold 3146
Platinum 1144
Silver 5509
Total 9799
SQL
복사
BOOSTER QUIZ 10-1-2
2020년에 시작한 매장에 대해 시작년월별 매장운영유형별 매장건수를 보여주세요. 시작년월별 중간합계와 전체합계도 포함해서 보여주세요.
•
대상 테이블: 매장(Shop)
•
조회 조건: ShopStartYmd가 2020년인 데이터
◦
ShopStartYmd는 날짜를 관리하지만 문자형 자료형임에 주의가 필요
•
조회 컬럼: ShopStartYm, ShopOperTpNm, ShopCnt
•
추가 조건
◦
ShopStartYm: ShopStartYmd를 년월로 처리(SUBSTR(ShopStartYmd,1,6))
◦
ShopStartYm, ShopOperTp별 GROUP BY 처리하세요.
▪
ROLLUP을 사용해 ShopStartYm별 중간합계와 전체 합계도 구해주세요.
◦
ShopOperTpNm: ShopOperTp에 따른 명칭을 보여주세요.
▪
BaseCd 테이블을 사용해 코드에 따른 명칭을 가져와 처리합니다.
▪
계인 경우에는 Total로 보여주세요.
◦
전체합계 데이터일 때는 ShopStartYm을 Total로 보여주세요.
ShopStartYm ShopOperTpNm ShopCnt
----------- ------------ -------
202001 Distributor 10
202001 Directly 9
202001 Total 19
202007 Distributor 27
202007 Directly 16
202007 Total 43
202008 Distributor 29
202008 Directly 18
202008 Total 47
Total Total 109
SQL
복사
BOOSTER QUIZ 10-1-3
2020년 1월과 2020년 2월 주문에 대해, ItemCat별, 주문년월별 주문수량을 보여주세요. ItemCat별 주문수량 합계와 전체합계도 추가해서 보여주세요.
•
대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item), 상품카테고리(ItemCat)
•
조회 조건: OrdDtm이 2020년 1월부터 2020년 2월말까지의 주문 데이터
•
조회 컬럼: ItemCatNm, OrdYm, SUM_OrdQty
•
추가 조건
◦
ItemCat, OrdYm별 GROUP BY 처리하시오.
◦
SUM_OrdQty: ItemCat, OrdYm별 주문수량(OrdQty) 합계
◦
ROLLUP을 사용해 ItemCat별 중간합계와 전체합계를 추가하세요
◦
GROUPING 함수를 사용해 합계인 경우에는 Total로 표시해주세요.
ItemCatNm OrdYm SUM_OrdQty
--------- ------ ----------
Beverage 202001 80
Beverage 202002 113
Beverage Total 193
Bakery 202001 80
Bakery 202002 110
Bakery Total 190
Coffee 202001 218
Coffee 202002 298
Coffee Total 516
Total Total 899
SQL
복사
