2021-12-09 19:00 작성

My SQL basic 메모

Table of contents

ROLLUP 샘플 table 세팅하기
ROLLUP 개요
GROUPING() function

ROLLUP 샘플 table 세팅하기

다음의 구문은 product lines와 years로 요약된 order 값들을 저장하는 sales table을 생성한다. 데이터는 products, orders 그리고 orderDetails tables로부터 온다.

CREATE TABLE sales
SELECT
    productLine,
    YEAR(orderDate) orderYear,
    SUM(quantityOrdered * priceEach) orderValue
FROM
    orderdetails
    INNER JOIN
        orders USING (orderNumber)
    INNER JOIN
        products USING (productCode)
GROUP BY
    productLine,
    YEAR(orderDate);

SELECT * FROM sales;

output:

productLine	orderYear	orderValue
Classic Cars	2003	1374832.22
Classic Cars	2004	1763136.73
Classic Cars	2005	715953.54
Motorcycles	2003	348909.24
Motorcycles	2004	527243.84
Motorcycles	2005	245273.04
Planes	2003	309784.20
Planes	2004	471971.46
Planes	2005	172881.88
Ships	2003	222182.08

…

ROLLUP 개요

Grouping set은 그룹화하고자 하는 columns의 집합이다. 예를 들어, 다음의 쿼리는 productline으로 grouping set을 생성한다.

SELECT 
    productline, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline;

output:

productline	totalOrderValue
Classic Cars	3853922.49
Motorcycles	1121426.12
Planes	954637.54
Ships	663998.34
Trains	188532.92
Trucks and Buses	1024113.57
Vintage Cars	1797559.63

다음의 쿼리는 ()를 통해 빈 grouping set을 생성한다:

SELECT 
    SUM(orderValue) totalOrderValue
FROM
    sales;

output:

totalOrderValue
9604190.61

만약, 하나의 쿼리 안에서 두 개 혹은 그 이상의 groping sets를 만들어내고 싶다면 UNION ALL 연산자가 도움이 될 수 있다.

SELECT
    productline,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY
    productline
UNION ALL
SELECT
    NULL,
    SUM(orderValue) totalOrderValue
FROM sales;

output: 조건이 서로 다른 그룹들을 묶어 결과를 반환한다.

productline	totalOrderValue
Classic Cars	3853922.49
Motorcycles	1121426.12
Planes	954637.54
Ships	663998.34
Trains	188532.92
Trucks and Buses	1024113.57
Vintage Cars	1797559.63
NULL	9604190.61

UNION ALL에서 모든 쿼리는 columns의 수가 같아야 하기 때문에 위의 예시에서는 두 번째 쿼리에서 NULL을 넣어 조건을 만족시켰다.

productLine column의 NULL은 totalOrderValue의 전체 합계를 표시한다.

위의 쿼리는 productLine 별 totalOrderValue을 보여줄 뿐만 아니라 총 합계도 보여주지만 두 가지 문제점이 산재한다.

쿼리가 길다.
데이터베이스는 내부적으로 두 가지 별개의 쿼리를 실행시킨 후에 하나로 결합하기 때문에 퍼포먼스 이슈가 생길 수 있다.

위의 문제점들을 고치기 위해 ROLLUP을 사용할 수 있다.

ROLLUP은 GROUP BY 절의 확장형으로 사용된다:

SELECT
    select_list
FROM
    table_name
GROUP BY
    c1, c2, c3 WITH ROLLUP;

ROLLUP은 GROUP BY 절에 명시된 columns나 expressions에 기반한 다수의 grouping sets를 만들어낸다. 예를 들어:

SELECT
    productLine,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY
    productLine WITH ROLLUP;

output: 두 개의 쿼리를 실행하지 않고도 하나의 쿼리로 같은 결과값을 기대할 수 있다.

productLine	totalOrderValue
Classic Cars	3853922.49
Motorcycles	1121426.12
Planes	954637.54
Ships	663998.34
Trains	188532.92
Trucks and Buses	1024113.57
Vintage Cars	1797559.63
NULL	9604190.61

결과에서 보여진 것처럼 ROLLUP은 소계(subtotals) 뿐만 아니라 총계를 계산한다. 만약, 복수의 columns가 GROUP BY 절에서 명시되었다면 ROLLUP은 입력 columns 사이에서 계층을 추정한다.

예를 들어:

GROUP BY c1, c2, c3 WITH ROLLUP

ROLLUP은 다음의 계층에 따라 추정한다:

c1 > c2 > c3

그리고 다음과 같은 grouping sets를 만든다:

(c1, c2, c3)
(c1, c2)
(c1)
()

GROUP BY에서 명시된 두 개의 columns가 존재한다고 해보자:

GROUP BY c1, c2 WITH ROLLUP

그러면 ROLLUP은 다음의 grouping sets을 만든다.

(c1, c2)
(c1)
()

다음의 예시 쿼리를 보자:

SELECT
    productLine,
    orderYear,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY
    productLine,
    orderYear
WITH ROLLUP;

output:

rollup

ROLLUP은 productLine이 변경될 때 마다 소계를 만들며 끝에 총계를 만든다.

이 경우에 계층은 다음과 같다:

productLine > orderYear

만약 계층을 뒤집으면:

SELECT
    orderYear,
    productLine,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY
    orderYear,
    productLine
WITH ROLLUP;

output:

rollup

ROLLUP은 year가 바뀔 때 마다 소계를 만들며 끝에 총계를 만든다.

예시의 계층은 다음과 같다:

orderYear > productLine

GROUPING() function

Result set의 NULL이 소계와 총계를 나타내는지 여부를 확인하는데 있어 GROUPING() function을 사용한다.

GROUPING() function은 초집합체(super-aggregate) row에서 NULL 발생 시 1을 반환하고 그렇지 않으면 0을 반환한다.

GROUPING() function은 SELECT, HAVING 그리고 ORDER BY(MySQL 8.0.12 버전)에서 사용할 수 있다.

다음 쿼리를 보자:

SELECT
    orderYear,
    productLine,
    SUM(orderValue) totalOrderValue,
    GROUPING(orderYear),
    GROUPING(productLine)
FROM
    sales
GROUP BY
    orderYear,
    productLine
WITH ROLLUP;

output:

rollup

GROUPING(orderYear)은 초집합체 row의 orderYear column에서 NULL이 발생하면 1을 반환하고 그렇지 않으면 0을 반환한다.

비슷하게 GROUPING(prouductLine)은 초집합체 row의 productLine에서 NULL이 발생할 때 1을 반환한다. 그렇지 않으면 0을 반환한다.

GROUPING() function은 종종 초집합체 NULL을 직접적으로 보여주기보다 의미있는 라벨로 교체하여 보여주기 위한 용도로 사용된다.

다음의 예시는 IF() function이 GROUPING() function과 함께 어떻게 orderYear와 productLine columns에서 NULL을 의미있는 대체 라벨로 치환하는지 보여준다.

SELECT
    IF(GROUPING(orderYear),
        'All Years',
        orderYear) orderYear,
    IF(GROUPING(productLine),
        'All Product Lines',
        productLine) productLine,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY
     orderYear,
     productLine
WITH ROLLUP;

output:

orderYear	productLine	totalOrderValue
2003	Classic Cars	1374832.22
2003	Motorcycles	348909.24
2003	Planes	309784.20
2003	Ships	222182.08
2003	Trains	65822.05
2003	Trucks and Buses	376657.12
2003	Vintage Cars	619161.48
2003	All Product Lines	3317348.39
2004	Classic Cars	1763136.73
2004	Motorcycles	527243.84
2004	Planes	471971.46
2004	Ships	337326.10
2004	Trains	96285.53
2004	Trucks and Buses	465390.00
2004	Vintage Cars	854551.85
2004	All Product Lines	4515905.51
2005	Classic Cars	715953.54
2005	Motorcycles	245273.04
2005	Planes	172881.88
2005	Ships	104490.16
2005	Trains	26425.34
2005	Trucks and Buses	182066.45
2005	Vintage Cars	323846.30
2005	All Product Lines	1770936.71
All Years	All Product Lines	9604190.61

GROUPING(orderYear)은 연도의 끝에서 GROUPING(productLine)은 GROUP BY의 orderYear 조건을 충족시키는 productLine 끝에서 NULL이 발생하는 것을 알 수 있다.