[PostgreSQL] ROLLUP을 사용해 소계 구하기

먼저 아래와 같은 데이터가 있다고 가정합니다.

dt	order_id	user_id	item_id	price	category	sub_category
2017-01-01	1	1	1	10000	men	jacket
2017-01-01	1	1	2	5000	food	fish
2017-01-01	1	1	3	2500	book	business
2017-01-01	2	2	1	10000	men	jacket
2017-01-01	2	2	5	50000	women	bag
2017-01-01	3	2	2	5000	food	fish
2017-01-01	4	3	1	10000	men	jacket
2017-01-01	5	4	4	40000	cd	classic

이러한 데이터에서 카테고리별 총 매출과 소계를 계산하고자 합니다. 소계란 전체가 아닌 부분의 합을 말합니다. 즉, 아래 표와 같이 모든 카테고리의 매출(총계)과 sub_category 별 매출(소계), category별 매출(소계)을 집계하고자 하는 것입니다.

category	sub_category	매출
all	all	~~~
men	all	~~~
men	jacket	~~~
women	all	~~~

 

먼저 ROLLUP을 사용하지 않고 이 소계와 총계를 한번에 출력하고자 한다면, 각 카테고리 별로 집계한 결과를 같은 컬럼이 되게 변환한 다음 UNION ALL로 1개의 테이블화 시키면 됩니다.

WITH
sub_category_amount AS (
    SELECT 
           category,
           sub_category,
           SUM(price) AS amount
    FROM purchase_log
    GROUP BY category, sub_category
)
, category_amount AS (
    SELECT 
           category,
           'all' AS sub_category,
           SUM(price) AS amount
    FROM purchase_log
    GROUP BY category
)
, total_amount AS (
    SELECT 
           'all' AS category,
           'all' AS sub_category,
           SUM(price) AS amount
    FROM purchase_log
)

SELECT * FROM sub_category_amount
UNION ALL 
SELECT * FROM category_amount
UNION ALL 
SELECT * FROM total_amount;

이와 같이 WITH문과 UNION ALL문을 사용해 계산할 순 있지만 UNION ALL의 계산 비용은 비싼 편이라 성능이 좋지 않습니다. ROLLUP 구문을 사용하면 이러한 문제를 해결할 수 있습니다.

SELECT 
       COALESCE(category, 'all') AS category,
       COALESCE(sub_category, 'all') AS sub_category,
       SUM(price) AS amount
FROM purchase_log
GROUP BY ROLLUP(category, sub_category);

ROLLUP

ROLLUP문을 사용할 때, 주의해야 할 점은 지정한 순서대로 소계하기 때문에 순서가 변경되면 결과가 달라질 수 있습니다. 위 ROLLUP을 사용한 쿼리의 결과는 아래와 같이 나올 수 있습니다.

category	sub_category	amount
men	jacket	30000
men	all	30000
women	bag	50000
women	all	50000
all	all	132500

ROLLUP은 아래와 같은 형태로 사용하면 됩니다.

SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY ROLLUP (C1, C2, C3);

-- 특정 컬럼 제외 가능

SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY C1 ROLLUP (C2, C3);