MySQL 피벗(Pivot) 완벽 가이드: CASE 문 사용하기

 

데이터베이스에서 데이터를 조회할때 가끔씩 로우에 있는 데이터를 커럼으로 변경해서 조회를 해야하는 경우가 있습니다.

이런 방식을 '피벗'이라고 하는데 MYSQL에서도 로우와 컬럼의 위치를 변경하는 피벗 기능에 대해서 알아보게습니다.

1. 피벗(Pivot)이란 무엇인가?

피벗의 사전적 의미는 '회전축' 또는 '축을 중심으로 회전시키다'입니다. 데이터 처리에서 피벗은 행(Row)으로 나열된 데이터를 열(Column)로 회전시켜 재구성하는 작업을 말합니다.

• 목적: 대량의 로우 데이터를 요약하여 한눈에 파악하기 쉬운 통계표(Matrix) 형태로 변환하기 위함입니다.
• 핵심 동작: 1. 기준이 되는 열을 정함 (예: 연도) 2. 열로 올릴 항목을 정함 (예: 월) 3. 교차 지점에 들어갈 값을 계산함 (예: 합계, 평균)

2. MYSQL에서의 피벗(Pivot)

결론부터 말하자면 MYSQL에서는 피벗 기능을 별도로 지원하지 않습니다.

왜냐?

반응형

① 관계형 모델의 철학 (Relational Purity)

데이터베이스의 근본은 데이터를 안전하게 저장하고 정규화하는 것입니다. 피벗은 데이터를 '보여주는' 방식, 즉 프레젠테이션(Presentation) 영역에 가깝습니다. SQL 표준은 행을 필터링하고 조인하는 데 집중하며, 표의 모양을 바꾸는 것은 전통적으로 애플리케이션(UI)의 역할로 여겨져 왔습니다.

② 고정되지 않은 스키마의 위험성

진정한 피벗이 작동하려면 데이터 값에 따라 결과 열(Column)의 개수가 가변적으로 변해야 합니다. 하지만 SQL은 쿼리가 실행되기 전에 결과셋의 열 이름과 개수가 확정되어야 하는 구조를 가지고 있습니다.

• 데이터 값에 따라 열이 늘어나는 기능은 데이터베이스 엔진의 예측 가능성을 떨어뜨리고 최적화를 어렵게 만듭니다.

③ 유연한 대체 방식의 존재

앞서 보여드린 CASE 문이나 IF 문을 활용하면 표준 SQL만으로도 피벗과 동일한 결과를 완벽하게 만들어낼 수 있습니다. MySQL 개발팀 입장에서는 이미 구현 가능한 기능을 위해 복잡한 전용 엔진을 추가할 우선순위가 낮았을 가능성이 큽니다.

위와 같은 이유로 피벗 기능은 따로 지원하고 있지 않습니다.

대신 CASE문을 사용해서 피벗기능 처럼 구현은 가능합니다.

3.CASE문으로 피벗하기

MYSQL에서 CASE문으로 피벗을 하는 예제를 통해서 알아보겠습니다.

-- 1. 테이블 생성
CREATE TABLE sales_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year INT NOT NULL,
    month VARCHAR(10) NOT NULL,
    sales DECIMAL(10, 2) NOT NULL
);

-- 2. 테스트 데이터 삽입
INSERT INTO sales_data (year, month, sales) VALUES
(2023, 'Jan', 1500.00),
(2023, 'Feb', 2000.00),
(2023, 'Mar', 1800.00),
(2024, 'Jan', 1700.00),
(2024, 'Feb', 2100.00),
(2024, 'Mar', 1900.00);

기본적으로 위의 쿼리를 조회하면 아래와 같이 테이블이 조회 됩니다.

그렇다면 여기서 "각 년도의 월별 최대 매출이 얼마인지를 구하여라" 라고 한다면 어떻게 하시겠습니까?

풀이

우선 year는 년도별의 gorup으로 묶여있어야 하기에 컬럼으로 있어야하며
month 같은 경우에는 현재는 월별로 각각의 로우에 데이터로 삽입 되어있지만 이것이 column으로 가야 조회할때 보기가 편하게 됩니다.
따라서 피벗할 데이터는 month의 각 월별 데이터를 컬럼에 위치하도록 해야합니다.

피벗을 할 컬럼을 알았으니 피벗을 진행하는 법을 알아야하는데 mysql에서 피벗은 아래와 같습니다.

SELECT 
    year,
    CASE WHEN month = 'Jan' THEN sales ELSE 0 END AS Jan_Sales,
    CASE WHEN month = 'Feb' THEN sales ELSE 0 END AS Feb_Sales,
    CASE WHEN month = 'Mar' THEN sales ELSE 0 END AS Mar_Sales
FROM sales_data

 위처럼 case when 문을 사용해서 특정 문자열일때의 값을 각각의 컬럼으로 만들어주면 아래처럼 각각의 월별로 컬럼이 생성되게 됩니다.

※(NULL 처리: CASE 문에서 데이터가 없는 경우를 대비해 ELSE 0을 넣어주는 것이 통계 계산 시 객관적인 데이터 산출에 유리합니다.)

그리고 이렇게 되면 각각의 년도별 월 매출 데이터는 나오지만 최대매출을 알수는 없습니다.

그렇기 때문에 이 데이터를 MAX함수와 GROUP BY 를 통해서 묶어서 최대 매출을 조회해보겠습니다.

SELECT 
    year,
    MAX(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan_Sales,
    MAX(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb_Sales,
    MAX(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar_Sales
FROM sales_data
GROUP BY year;

이렇게 조회를하면 2023년의 1,2,3월의 매출 데이터가 나오게 되며 요구조건에 따른 피벗을 통해서 데이터를 간편하게 뽑을수 있게됩니다.

4.마무리

4-1.피벗을 사용하는 근본적인 이유: "인지 효율성"

데이터베이스에 저장된 데이터는 대개 '롱 포맷(Long Format)' 형태입니다. 이는 컴퓨터가 데이터를 처리하고 저장하기에는 최적화되어 있지만, 인간이 트렌드를 읽기에는 부적합합니다.

• 가독성 증대: 수천 행의 데이터를 한 화면에 들어오는 요약 표로 압축합니다.
• 비교 분석 용이: 흩어져 있는 데이터를 가로로 나열하여 항목 간의 차이(예: 전월 대비 매출 변화)를 즉각적으로 파악하게 합니다.
• 데이터 차원 축소: 불필요한 세부 정보를 제거하고 핵심 지표(합계, 평균 등)만 남겨 의사결정을 돕습니다.

4-2.언제 피벗을 쓰는가?

위는 근본적인 이유에 대해 나열했다면 실제 실무에선 보고서 작성, 통계 분석, 데이터 시각화 전처리 단계에서 사용됩니다.

① 시계열 분석 (Time-series Analysis)

• 상황: 매일 발생하는 거래 내역을 월별, 연별로 비교하고 싶을 때.
• 활용: 행에는 '상품명', 열에는 '1월, 2월, 3월...'을 배치하여 월별 판매 추이를 한눈에 확인합니다.

② 속성 간 교차 분석 (Cross-tabulation)

• 상황: 사용자 그룹별로 선호하는 콘텐츠 카테고리가 무엇인지 알고 싶을 때.
• 활용: 행에는 '연령대', 열에는 '영화 장르(액션, 코미디, 로맨스)'를 배치하여 특정 연령층의 선호도를 수치화합니다.

③ 실적 및 성과 지표 비교

• 상황: 여러 지점의 분기별 목표 달성률을 비교할 때.
• 활용: 행에는 '지점명', 열에는 '1분기, 2분기...'를 두어 지점 간의 성과 격차를 객관적으로 분석합니다.

4-3.피벗의 주의사항

 

• 데이터 소실 주의: 피벗은 기본적으로 집계를 전제로 합니다. 상세 내역이 합쳐지는 과정에서 세부적인 특이값이 묻힐 수 있습니다.
• 열의 확장성: 피벗할 대상(열로 올라갈 항목)이 너무 많으면 표가 너무 넓어져 오히려 가독성을 해칩니다. (예: 365일을 모두 열로 피벗하는 경우)
• 성능 부하: MySQL에서 대량의 데이터를 동적 피벗으로 처리할 경우 연산 비용이 커질 수 있으므로, 인덱스 설계가 선행되어야 합니다.

 

위의 상황과 피벗의 주의사항을 지키며 피벗을 사용해야하는 상황에서 써야하고 항상 중요한 성능부하를 고려하면서 쿼리를 짜야하합니다.