쿼리 최적화를 위한 sql집계함수(ROLLUP, CUBE, GROUPING SETS)

슬슬 쿼리에 익숙해져서 원하는 형태의 쿼리를 쉽게 만들 수 있을때, 다음단계로서 비용을 최적화하는 쿼리를 짜는 습관을 들이면 좋을거같다!!

그렇기 위해 SELECT출력을 최소화 하여 집계할 수 있는 집계함수 세가지를 소개하고자 한다.

데이터는 쇼핑몰의 카테고리별 매출 데이터를 예시로 들어보겠습니다.

1. 데이터 예시

아래에 있는 데이터테이블 만드려고 한다. RAW데이터는 각각의 분류별 매출이 있다고 가정하고 만들어보자

대분류	소분류	매출
전체	전체	800,000
남성	전체	300,000
남성	가방	170,000
남성	신발	130,000
여성	전체	500,000
여성	가방	360,000
여성	신발	140,000

2. "GROUP BY" 만 사용시 생기는 문제

GROUP BY 만을 사용하게 되면 아래와 같은 시나리오가 연출된다.

SELECT 전체 상품의 매출 합계 
UNION ALL
SELECT 대분류별 매출 합계
UNION ALL
SELECT 대분류, 소분류별 매출합계

이렇게 사용할 시 SELECT구문을 세번 출력하게 된다. 그만큼 비용을 쓰게 된다는 것이다.
따라서 한번의 SELECT를 통해서 뎁스(분류)별로 집계를 하는 다양한 함수를 사용해보자.

3. ROLLUP

먼저, ROLLUP 함수의 경우는 소그룹에 대한 합계를 집계할 수 있게해주며 첫번째 컬럼에 대해서만 소그룹 합계를 집계해준다.

-- ROLLUP 사용 예시
SELECT 대분류,
	   소분류,
       SUM(매출액) AS 매출액	
FROM example_table
GROUP BY ROLLUP(대분류, 소분류);

결과 예시

대분류	소분류	매출
(null)	(null)	800,000
남성	(null)	300,000
남성	가방	170,000
남성	신발	130,000
여성	(null)	500,000
여성	가방	360,000
여성	신발	140,000

현 상태에서 우리가 원하는 전체라는 문구를 넣을수있는 COALESCE 함수만 아래처럼 추가하면 원하는 형태로 쿼리가 가능합니다.

SELECT COALESCE(대분류, 'all'),
	   COALESCE(소분류, 'all'),
       SUM(매출액) AS 매출액	
FROM example_table
GROUP BY ROLLUP(대분류, 소분류);

4. CUBE

CUBE 함수는 ROLLUP과 비슷하지만, 한가지 차이점으로 명시한 모든 차원에 대해서 소그룹 합계를 구해줍니다.

SELECT COALESCE(대분류, '전체') AS 대분류,
	   COALESCE(소분류, '전체') AS 소분류,
       SUM(매출액) AS 매출액	
FROM example_table
GROUP BY CUBE(대분류, 소분류);

결과예시

대분류	소분류	매출
(null)	(null)	800,000
(null)	가방	530,000
(null)	신발	270,000
남성	(null)	300,000
남성	가방	170,000
남성	신발	130,000
여성	(null)	500,000
여성	가방	360,000
여성	신발	140,000

5. GROUPING SETS

GROUPING SETS는 특정항목에 대한 소계만을 계산하는 함수입니다.

SELECT COALESCE(대분류, '전체') AS 대분류,
	   COALESCE(소분류, '전체') AS 소분류,
       SUM(매출액) AS 매출액	
FROM example_table
GROUP BY GROUPING SETS(대분류, 소분류);

결과 예시

대분류	소분류	매출
남성	(null)	300,000
여성	(null)	500,000
(null)	신발	270,000
(null)	가방	530,000