이번 주에는 MySQL로 피벗을 만드는 방법을 배웠습니다.
배운 내용을 복습할 겸, 엑셀·파이썬 없이 순수 SQL만으로
“월 × 카테고리” 매출 표를 만들고 월 합계/연도 합계/전체 합계까지 한 번에 붙이는 과정을 다시 정리해보겠습니다 !
특히 MySQL의 ONLY_FULL_GROUP_BY 모드에서도 깨끗하게 동작하도록 서브쿼리에서 연·월을 먼저 계산하는 패턴을 사용합니다.
이번 글에서는 다음과 같은 테이블을 MySQL로 만들게 됩니다.
- "월 × 카테고리(전자/의류/식품)" 교차표를 열 피벗으로 만들기
- WITH ROLLUP으로 월 합계, 연도 합계, 전체 합계를 자동으로 붙이기
- GROUPING() 함수로 합계 행을 라벨링
- ONLY_FULL_GROUP_BY 환경에서도 안전하게 동작
0. 핵심 아이디어 한눈에 정리
(1) 피벗 (열로 펼치기)
: SUM(CASE WHEN category='전자' THEN amount ELSE 0 END) 형태로 조건부 집계를 사용합니다.
(2) 합계 자동화
: GROUP BY ... WITH ROLLUP을 붙이면 소계/총계가 자동 생성됩니다.
(3) 합계 식별/라벨링
: GROUPING(col)이 합계 행이면 1, 일반 행이면 0을 반환합니다.
(4) ONLY_FULL_GROUP_BY 대응
: DATE_FORMAT(sale_date, '%Y-%m')처럼 원본 컬럼을 SELECT에서 직접 참조하면 에러가 날 수 있습니다.
서브쿼리에서 YEAR(sale_date) AS yr, MONTH(sale_date) AS mo를 먼저 만들어 두고, 바깥 쿼리에서는 yr, mo만 사용합니다.
왜 서브쿼리(yr, mo)인가?
ONLY_FULL_GROUP_BY가 켜져 있으면, GROUP BY YEAR(sale_date), MONTH(sale_date)를 하면서
SELECT DATE_FORMAT(sale_date, '%Y-%m') 같은 원본 컬럼/함수를 함께 쓰면 함수적 종속성 위반으로 에러가 납니다.
따라서 안쪽 서브쿼리에서 YEAR(sale_date) AS yr, MONTH(sale_date) AS mo를 명시 컬럼으로 뽑아두고,
바깥쪽에선 yr, mo만으로 집계/라벨링을 합니다. 이 패턴이 가장 깔끔합니다.
1. [준비] 예제 스키마 & 더미 데이터
-- (옵션) 예제 전용 DB
-- CREATE DATABASE IF NOT EXISTS sales_demo
-- DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- USE sales_demo;
-- 기존 테이블이 있다면 삭제(없어도 경고만 나오고 통과)
DROP TABLE IF EXISTS sales;
-- 매출 테이블
CREATE TABLE sales (
id INT PRIMARY KEY AUTO_INCREMENT,
sale_date DATE NOT NULL,
region ENUM('서울','부산','대전','대구','광주') NOT NULL,
channel ENUM('온라인','오프라인') NOT NULL,
category ENUM('전자','의류','식품') NOT NULL,
product VARCHAR(50) NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(12,2) NOT NULL,
-- 금액 = 수량 * 단가 (생성 컬럼)
amount DECIMAL(14,2) AS (quantity * unit_price) STORED,
KEY idx_date (sale_date),
KEY idx_region_channel (region, channel),
KEY idx_category (category),
KEY idx_date_category (sale_date, category)
) ENGINE=InnoDB;
-- 2025-08, 2025-09 / 서울·부산 / 온·오프 / 전자·의류·식품 더미 데이터
INSERT INTO sales (sale_date, region, channel, category, product, quantity, unit_price) VALUES
('2025-08-05','서울','온라인','전자','노트북A', 5, 300000),
('2025-08-05','서울','온라인','의류','셔츠A', 8, 50000),
('2025-08-05','서울','온라인','식품','과자A', 20, 8000),
('2025-08-06','서울','오프라인','전자','노트북B', 3, 320000),
('2025-08-06','서울','오프라인','의류','셔츠B', 6, 60000),
('2025-08-06','서울','오프라인','식품','과자B', 15, 9000),
('2025-08-07','부산','온라인','전자','노트북C', 4, 280000),
('2025-08-07','부산','온라인','의류','셔츠C', 7, 45000),
('2025-08-07','부산','온라인','식품','과자C', 18, 7000),
('2025-08-08','부산','오프라인','전자','노트북D', 2, 310000),
('2025-08-08','부산','오프라인','의류','셔츠D', 5, 55000),
('2025-08-08','부산','오프라인','식품','과자D', 12, 8500),
('2025-09-10','서울','온라인','전자','노트북E', 6, 310000),
('2025-09-10','서울','온라인','의류','셔츠E', 10, 52000),
('2025-09-10','서울','온라인','식품','과자E', 22, 8200),
('2025-09-11','서울','오프라인','전자','노트북F', 4, 330000),
('2025-09-11','서울','오프라인','의류','셔츠F', 7, 61000),
('2025-09-11','서울','오프라인','식품','과자F', 16, 9200),
('2025-09-12','부산','온라인','전자','노트북G', 5, 285000),
('2025-09-12','부산','온라인','의류','셔츠G', 8, 46000),
('2025-09-12','부산','온라인','식품','과자G', 19, 7300),
('2025-09-13','부산','오프라인','전자','노트북H', 3, 315000),
('2025-09-13','부산','오프라인','의류','셔츠H', 6, 56000),
('2025-09-13','부산','오프라인','식품','과자H', 13, 8700);
SELECT COUNT(*) AS rows, SUM(amount) AS total_amount FROM sales;
-- 기대: rows=24, total_amount=13,853,400
2. 월 × 카테고리 피벗 + 월합계 + 연도합계 + 전체합계
패턴 요약
1. 서브쿼리에서 YEAR(sale_date) AS yr, MONTH(sale_date) AS mo 뽑기
2. 바깥에서 GROUP BY yr, mo WITH ROLLUP
3. SUM(CASE WHEN category=... THEN amount END)로 열 피벗
4. GROUPING(yr), GROUPING(mo)로 합계 행 라벨링
5. 정렬은 ORDER BY GROUPING(yr), yr, GROUPING(mo), mo
SELECT
CASE
WHEN GROUPING(yr)=1 AND GROUPING(mo)=1 THEN '전체 합계' -- grand total
WHEN GROUPING(mo)=1 THEN CONCAT(yr, '년 합계') -- year subtotal
ELSE CONCAT(yr, '-', LPAD(mo, 2, '0')) -- e.g. 2025-08
END AS '월 \\ 카테고리',
-- 카테고리별 금액(열 피벗)
SUM(CASE WHEN category='전자' THEN amount ELSE 0 END) AS 전자,
SUM(CASE WHEN category='의류' THEN amount ELSE 0 END) AS 의류,
SUM(CASE WHEN category='식품' THEN amount ELSE 0 END) AS 식품,
-- 월 합계
SUM(amount) AS '월 매출 합계'
FROM (
SELECT YEAR(sale_date) AS yr,
MONTH(sale_date) AS mo,
category,
amount
FROM sales
) s
GROUP BY yr, mo WITH ROLLUP
ORDER BY
GROUPING(yr), yr, -- 일반 연도 먼저, 마지막에 전체 합계
GROUPING(mo), mo; -- 각 연도 안에서 월들 먼저, 그 다음 연도 합계
연도 합계 행을 숨기고 싶으면 마지막에 아래를 추가합니다.
HAVING NOT (GROUPING(yr)=0 AND GROUPING(mo)=1);
옵션 : 출력 포맷/필터링/수량 피벗
(1) 숫자 포맷(3자리 콤마) : 시각화를 위해 표시용으로만 FORMAT()을 씁니다(문자형 반환 주의)
SELECT ...,
FORMAT(SUM(amount), 0) AS '월 매출 합계(표시용)'
FROM ...
(2) 특정 연도만 보고 싶을 때 : 인덱스 친화적으로 범위 조건을 권장합니다.
FROM sales
WHERE sale_date >= '2025-01-01' AND sale_date < '2026-01-01'
(3) 수량 기준 피벗 : amount 대신 quantity로 동일 패턴을 사용합니다.
SUM(CASE WHEN category='전자' THEN quantity ELSE 0 END) AS 전자_수량3. [응용 예시 1] 지역 × 채널 피벗 + 지역합계 + 전체합계
SELECT
CASE WHEN GROUPING(region)=1 THEN '전체 합계' ELSE region END AS '지역 \\ 채널',
SUM(CASE WHEN channel='온라인' THEN amount ELSE 0 END) AS 온라인,
SUM(CASE WHEN channel='오프라인' THEN amount ELSE 0 END) AS 오프라인,
SUM(amount) AS '지역 매출 합계'
FROM sales
GROUP BY region WITH ROLLUP
ORDER BY GROUPING(region), region;
4. [응용 예시 2] 카테고리 × 채널 — 건수와 매출을 동시에
SELECT
CASE WHEN GROUPING(category)=1 THEN '전체 합계' ELSE category END AS '카테고리 \\ 채널',
-- 주문 건수(행 수)
COUNT(CASE WHEN channel='온라인' THEN 1 END) AS '온라인 건수',
COUNT(CASE WHEN channel='오프라인' THEN 1 END) AS '오프라인 건수',
-- 매출 금액
SUM(CASE WHEN channel='온라인' THEN amount ELSE 0 END) AS '온라인 매출',
SUM(CASE WHEN channel='오프라인' THEN amount ELSE 0 END) AS '오프라인 매출',
SUM(amount) AS '카테고리 합계 매출'
FROM sales
GROUP BY category WITH ROLLUP
ORDER BY GROUPING(category), category;
5. 정렬 & 라벨링 핵심 정리
- 합계 행 인지 : GROUPING(col) -> 합계행이면 1, 일반행이면 0.
- 라벨링 규칙
- 월 라벨 : CONCAT(yr, '-', LPAD(mo,2,'0'))
- 연도 합계 : GROUPING(mo)=1
- 전체 합계 : GROUPING(yr)=1 AND GROUPING(mo)=1
- 정렬 추천 패턴일반 행 -> 연도합계 -> 전체합계 순서로 깔끔히 정렬됩니다.
- ORDER BY GROUPING(yr), yr, GROUPING(mo), mo
6. 성능/운영 팁
(1) 인덱스
- 시계열 집계에 sale_date(날짜), 축약 피벗에 region(지역), channel(채널), 카테고리 집계에 sale_date(날짜), category(카테고리) 등이 유리합니다.
-- 월×카테고리 피벗 (월 라벨은 ym 생성 컬럼 사용 시 최적)
CREATE INDEX idx_sales_ym_cat ON sales (/* ym 또는 sale_date */, category);
-- 지역×채널 피벗
CREATE INDEX idx_sales_region_channel_ym ON sales (region, channel, /* ym 또는 sale_date */);
-- 드릴다운/세부 조회
CREATE INDEX idx_sales_date_category ON sales (sale_date, category);
- 대량 데이터라면 EXPLAIN으로 type/rows/key/Extra 확인 필수
* type은 range 이상, rows는 기간 제한으로 낮아져야 합니다. key가 의도한 인덱스인지 확인하고, Extra에 Using temporary/Using filesort가 잦으면 인덱스 순서와 GROUP BY/ORDER BY를 재검토합니다.
EXPLAIN
SELECT YEAR(sale_date)*100+MONTH(sale_date) AS ym, category, SUM(amount)
FROM sales
WHERE sale_date >= '2025-08-01' AND sale_date < '2025-10-01'
GROUP BY ym, category;
(2) 생성 컬럼(amount)
- 읽기 많은 집계에서 계산 비용을 줄이고 일관성을 확보합니다.
* 읽기 많은 집계에서는 amount = quantity * unit_price를 생성(STORED) 컬럼으로 두면 매 쿼리마다 곱셈을 반복하지 않아 CPU 사용량과 변환 비용을 줄일 수 있습니다.
-- 이미 테이블이 있다면
ALTER TABLE sales
ADD COLUMN amount DECIMAL(14,2) AS (quantity * unit_price) STORED;
(3) 동적 피벗
- 카테고리/채널이 자주 바뀌면 고정 컬럼 피벗은 유지보수 비용이 큽니다.
* 동적 컬럼이 필요하면 애플리케이션 레이어에서 피벗하거나, MySQL에선 동적 SQL(프로시저)로 생성합니다.
(4) ROLLUP 과한 합계 숨기기
- ROLLUP은 월 합계 + 연도 합계 + 전체 합계를 모두 만듭니다. 필요 없는 합계는 HAVING으로 정리합니다.
* HAVING NOT (조건)으로 특정 합계 행을 제거해 시각적으로 단순화할 수 있습니다.
-- ex) 연도 합계 행만 제거
HAVING NOT (GROUPING(yr)=0 AND GROUPING(mo)=1);
-- ex) 전체 합계 행까지 제거
HAVING GROUPING(yr)=0 AND GROUPING(mo)=0;
이 글의 핵심은 서브쿼리에서 집계 키(yr/mo)를 먼저 생성해
ONLY_FULL_GROUP_BY 환경에서도 안전하게 ROLLUP + 피벗을 구현하는 패턴입니다.
이 패턴 하나면 "월×카테고리", "지역×채널", "상품군×고객등급" 등 대부분의 경영 리포트를
순수 SQL로 재사용 가능하게 만들 수 있습니다.
'SQL' 카테고리의 다른 글
| [TIL] MySQL 기본 문법부터 트랜잭션, 락까지 한번에 정리 (1) | 2025.10.10 |
|---|