조회 결과에 합계를 처리하기 위해서는 일반적으로 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
위와 같은 결과에 전체 주문건수를 추가하고자 할 때 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
위 결과에 중간합계인 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
위 결과에 대해 정렬 순서를 조정하고 싶다면, 아래와 같이 인라인 뷰에서 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
ROLLUP을 사용하면 UNION ALL보다 간단하게 중간합계와 전체합계를 처리할 수 있다.
아래는 ShopOperTp, 주문년월(DATE_FORMAT(T2.OrdDtm,’%Y%m’))별 주문 건수를 구하는 SQL이다. 아직 중간합계 처리는 하지 않고 GROUP BY 만 적용한 상태다.