데이터 분석에서 합계나 소계를 계산하는 것은 매우 중요한 작업입니다. MySQL에서는 이러한 작업을 수행하기 위한 다양한 방법을 제공하는데, 그 중에서도 소계쿼리(Subtotal Query)는 데이터를 다양한 레벨에서 집계할 수 있는 강력한 도구입니다.
소계쿼리란 무엇인가?
소계쿼리는 데이터를 그룹화하고 각 그룹별로 집계 값을 계산한 다음, 이러한 집계를 다양한 레벨에서 제공하는 쿼리입니다. 예를 들어, 제품별 판매량, 카테고리별 판매량, 그리고 전체 판매량을 한 번의 쿼리로 확인할 수 있습니다.
GROUP BY와 WITH ROLLUP
MySQL에서 소계를 구현하는 가장 기본적인 방법은 GROUP BY절과 함께 WITH ROLLUP 수정자를 사용하는 것입니다.
SELECT
category,
product,
SUM(sales) AS total_sales
FROM
sales_data
GROUP BY
category, product
WITH ROLLUP;이 쿼리는 카테고리와 제품별로 판매량을 집계한 후, 추가적으로 다음과 같은 집계 행을 생성합니다:
- 각 카테고리의 총 판매량 (product 열이 NULL)
- 전체 판매량 (category와 product 열이 모두 NULL)
GROUPING() 함수
WITH ROLLUP으로 생성된 소계 행에서는 NULL 값이 나타납니다. 이것이 실제 데이터의 NULL 값인지 소계를 위한 NULL 값인지 구분하기 위해 GROUPING() 함수를 사용할 수 있습니다.
SELECT
IF(GROUPING(category) = 1, '모든 카테고리', category) AS category,
IF(GROUPING(product) = 1, '모든 제품', product) AS product,
SUM(sales) AS total_sales
FROM
sales_data
GROUP BY
category, product
WITH ROLLUP;이 쿼리는 소계 행에 '모든 카테고리', '모든 제품'과 같은 레이블을 표시하여 가독성을 높입니다.
GROUPING() 함수는 MySQL 8.0 이상에서 지원됩니다. 이전 버전에서는 IFNULL()을 사용하여 유사한 결과를 얻을 수 있지만, 실제 NULL 값과 소계용 NULL 값을 구분할 수 없습니다.UNION을 이용한 소계 구현
WITH ROLLUP이 MySQL 5.0 이상에서 지원되지만, 더 세밀한 제어가 필요하거나 이전 버전을 사용해야 하는 경우 UNION을 활용할 수 있습니다.
-- 제품별 판매량
SELECT category, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY category, product
UNION ALL
-- 카테고리별 판매량
SELECT category, '모든 제품' AS product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY category
UNION ALL
-- 전체 판매량
SELECT '모든 카테고리' AS category, '모든 제품' AS product, SUM(sales) AS total_sales
FROM sales_data;이 방식은 코드가 길어지지만, 레이블을 더 명확하게 지정할 수 있고 소계 행의 순서를 더 잘 제어할 수 있습니다.
다중 레벨 소계와 순서 제어
복잡한 분석에서는 여러 레벨의 소계가 필요할 수 있습니다. 이런 경우 ORDER BY 절을 추가하여 결과의 순서를 제어할 수 있습니다.
SELECT
IF(GROUPING(year) = 1, '모든 연도', year) AS year,
IF(GROUPING(quarter) = 1, '모든 분기', quarter) AS quarter,
IF(GROUPING(month) = 1, '모든 월', month) AS month,
SUM(sales) AS total_sales
FROM
sales_data
GROUP BY
year, quarter, month
WITH ROLLUP
ORDER BY
year, quarter, month;이 쿼리는 연도, 분기, 월별로 데이터를 집계하고, 각 레벨에서 소계를 생성합니다.
실무 활용 예시: 판매 데이터 분석
실제 비즈니스 환경에서 소계 쿼리가 어떻게 활용되는지 살펴보겠습니다.
SELECT
IF(GROUPING(region) = 1, '전체 지역', region) AS region,
IF(GROUPING(product_category) = 1, '전체 카테고리', product_category) AS category,
SUM(sales_amount) AS total_sales,
ROUND(AVG(sales_amount), 2) AS avg_sales,
COUNT(DISTINCT customer_id) AS customer_count
FROM
sales_transactions
WHERE
transaction_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY
region, product_category
WITH ROLLUP
ORDER BY
GROUPING(region),
region,
GROUPING(product_category),
product_category;이 쿼리는 지역별, 제품 카테고리별로 매출 합계, 평균 매출, 고객 수를 계산하고 소계를 제공합니다. ORDER BY 절에서 GROUPING() 함수를 사용하여 소계 행이 관련 그룹 바로 아래에 표시되도록 정렬합니다.
피벗 테이블과 소계 결합하기
데이터 분석에서 행과 열을 교차시켜 보여주는 피벗 테이블은 매우 유용합니다. MySQL에서는 CASE 표현식을 사용하여 피벗 테이블을 구현할 수 있으며, 이를 소계 쿼리와 결합할 수 있습니다.
SELECT
IF(GROUPING(product_category) = 1, '총계', product_category) AS category,
SUM(CASE WHEN quarter = 'Q1' THEN sales_amount ELSE 0 END) AS Q1_sales,
SUM(CASE WHEN quarter = 'Q2' THEN sales_amount ELSE 0 END) AS Q2_sales,
SUM(CASE WHEN quarter = 'Q3' THEN sales_amount ELSE 0 END) AS Q3_sales,
SUM(CASE WHEN quarter = 'Q4' THEN sales_amount ELSE 0 END) AS Q4_sales,
SUM(sales_amount) AS yearly_total
FROM
sales_data
WHERE
year = 2024
GROUP BY
product_category
WITH ROLLUP;이 쿼리는 제품 카테고리별로 분기별 매출과 연간 총매출을 보여주는 피벗 테이블을 생성하고, 마지막에 전체 합계 행을 추가합니다.
| category | Q1_sales | Q2_sales | Q3_sales | Q4_sales | yearly_total |
|---|---|---|---|---|---|
| 전자기기 | 125000 | 138000 | 142000 | 185000 | 590000 |
| 가구 | 84000 | 92000 | 87000 | 110000 | 373000 |
| 의류 | 67000 | 75000 | 82000 | 115000 | 339000 |
| 총계 | 276000 | 305000 | 311000 | 410000 | 1302000 |
성능 고려사항
소계 쿼리는 데이터량이 많을 경우 성능에 영향을 줄 수 있습니다. 최적화를 위한 몇 가지 팁은 다음과 같습니다:
- 필요한 열만 선택하여 쿼리 처리 속도를 향상시킵니다.
- 적절한 인덱스를 사용하여 GROUP BY 연산을 최적화합니다.
- 대용량 데이터의 경우, 임시 테이블을 사용하거나 미리 집계된 데이터를 준비할 수 있습니다.
EXPLAIN명령을 사용하여 쿼리 실행 계획을 분석하고 병목 현상을 파악합니다.
WITH ROLLUP을 사용한 쿼리는 임시 테이블을 생성할 가능성이 높으며, 대용량 데이터에서는 메모리 사용량이 증가할 수 있습니다. 대규모 데이터셋에서는 성능 테스트를 진행하고 필요에 따라 쿼리를 최적화하는 것이 중요합니다.결론
MySQL의 소계 쿼리는 데이터 분석과 보고서 작성에 필수적인 도구입니다. WITH ROLLUP, GROUPING() 함수, 또는 UNION 접근 방식을 통해 다양한 레벨에서의 집계 정보를 얻을 수 있습니다. 이를 통해 의사 결정에 필요한 다양한 각도에서의 데이터 인사이트를 한 번의 쿼리로 얻을 수 있어, 업무 효율성과 데이터 분석의 품질을 크게 향상시킬 수 있습니다.
- MySQL 공식 문서의 GROUP BY 수정자 섹션
- MySQL 8.0의 GROUPING() 함수 문서