피봇 테이블은 실무에서 데이터를 효율적으로 파악하기 위해 요약, 집계하여 원하는 형태로 재구성하는 방법으로 자주 사용되는 시각화 방법 중 하나이다.
(영어로는 ‘Pivot Table’이라고 하는데, 테이블을 피봇(Pivot)한다는 건 축을 바꾼다는 뜻에서 유래된 문구이다)
엑셀, 구글 스프레드시트에서 자주 사용하는 기능이지만, SQL에서도 비슷한 형태로 표현할 수 있다. SQL로 피봇테이블을 만들면 대용량 데이터에서도 빠르고 유연하게 분석이 가능하다.
● RFM 고객 세분화 분석에서 피봇테이블 활용
RFM 분석은 고객을 최근성(Recency), 빈도(Frequency), 구매금액(Monetary) 기준으로 분류한다. 이때 SQL로 피봇테이블을 만들어, 특정 RFM 조건을 만족하는 고객군의 수나 금액을 요약할 수 있다. 예를 들어, 최근 3개월 내 구매한 고객의 수, 6개월 이상 이탈한 고객의 구매금액 등을 한눈에 비교할 수 있다.
● 실무에서 피봇테이블이 활용되는 방법
특정 기간별 상품군별 판매 수량 비교
지역별 매출, 주문 건수 집계
고객 그룹별 구매 패턴 분석
프로모션별 할인 상품 수량 집계
충성고객/이탈고객 분포 파악
SQL로 피봇테이블을 만드는 방법
SQL에서는 CASE문과 집계 함수를 조합해 피봇테이블을 표현할 수 있다. CASE는 보통 SUM(), COUNT() 같은 집계 함수 안에서 사용된다. 기본 구조는 다음과 같다.
SELECT 기준컬럼 , SUM(CASE WHEN 조건1 THEN 수량 ELSE 0 END) AS 컬럼1 -- 조건에 따른 총 수량1 , SUM(CASE WHEN 조건2 THEN 수량 ELSE 0 END) AS 컬럼2 -- 조건에 따른 총 수량2 FROM 테이블명 GROUP BY 기준컬럼 -- 카테고리별로 묶기 예시 출력 데이터
CASE WHEN으로 조건을 설정하고, SUM()이나 COUNT() 안에 넣어 각 조건에 맞는 값을 합산하거나 집계한다.
예시 1: 할인율 구간별 판매 수량 집계 (SUM)
상황) records 테이블에서, - 할인율 50% 이상 상품 수량 - 할인율 80% 이상 상품 수량 - 전체 판매 수량 을 일별로 집계하고 싶을 때
SELECT order_date , SUM(CASE WHEN discount >= 0.5 THEN quantity ELSE 0 END) AS half_discount_items , SUM(CASE WHEN discount >= 0.8 THEN quantity ELSE 0 END) AS high_discount_items , SUM(quantity) AS all_items FROM records GROUP BY order_date ORDER BY order_date (ASC) -- ASC생략 가능
예시 2: 시간대별 주문 건수 세기
상황) tips 테이블에서, - 점심시간(Lunch) 주문 건수 - 저녁시간(Dinner) 주문 건수 을 요일(day)별로 세고 싶을 때
SELECT day , COUNT(CASE WHEN time = 'Lunch' THEN 1 END) AS lunch_orders -- "점심이면 1개 세어줘"라는 뜻 , COUNT(CASE WHEN time = 'Dinner' THEN 1 END) AS dinner_orders -- "저녁이면 1개 세어줘"라는 뜻 FROM tips GROUP BY day ORDER BY day
✨ TIP
SUM(CASE WHEN ~)은 "숫자값" 합산용,
COUNT(CASE WHEN ~)은 "행 개수" 세는 용도로 기억해두면 헷갈리지 않는다.
집계 결과에 NULL이 생기지 않도록 CASE 내부에는 항상 ELSE 0 또는 아예 명시적으로 처리하는 습관을 들이는 것이 좋다.
CASE 절 작성 시 유의할 점
ELSE 0을 명시적으로 넣는다. CASE 문에서 조건을 만족하지 않을 경우 결과가 NULL이 되는데, 집계 함수(SUM, COUNT)는 NULL을 무시하거나 의도치 않은 결과를 낼 수 있다. 명확하게 ELSE 0을 작성하면 쿼리 해석이 쉽고, 실수를 줄일 수 있다.
수량을 합산할 때는 DISTINCT를 불필요하게 쓰지 않는다. SUM(DISTINCT quantity)처럼 작성하면 중복 제거 후 합산되기 때문에 의도한 값보다 작아질 수 있다. 수량 집계에는 일반 SUM()을 사용한다.
CASE 안에 들어가는 결과값은 상황에 맞게 1, 컬럼값, 0 등을 넣는다. 건수 세는 경우는 THEN 1, 금액이나 수량을 더하는 경우는 THEN 컬럼명을 쓴다.
SQL에서도 피봇테이블은 CASE + SUM 또는 CASE + COUNT 조합으로 자유롭게 만들 수 있으며, 목적에 따라 기준 컬럼, 조건, 집계 방식을 명확히 설정하는 것이 핵심이다.
(본 내용은 데이터리안 ‘SQL 데이터 분석 캠프 입문반’을 수강하며 작성한 내용입니다.)
