SQL (PIVOT) - 제품 카테고리 별 매출 추이

문제

당신은 이커머스 쇼핑몰의 데이터 분석가입니다. 마케팅 팀에서 2023년 한 해 동안의 제품 카테고리별 매출 추이를 보고 싶다며 데이터를 요청했습니다.

orders 테이블과 products 테이블을 사용하여, 2023년의 각 카테고리별로 1분기(Q1)부터 4분기(Q4)까지의 **총 매출액(price의 합계)**을 보여주는 쿼리를 작성해주세요.

• 데이터베이스 스키마
  • orders 테이블 (주문 정보)
    • order_id: 주문 고유 ID
    • product_id: 주문한 상품 ID
    • order_date: 주문 날짜 (YYYY-MM-DD 형식)
  • products 테이블 (상품 정보)
    • product_id: 상품 고유 ID
    • category: 상품 카테고리 (예: 의류, 전자제품, 가구 등)
    • price: 상품 가격
• 결과 테이블 예시categorysales_q1sales_q2sales_q3sales_q4가구1500000230000012000004500000의류5000008000006500001200000전자제품3000000450000032000005000000

목표/접근법/사용함수

1. 데이터 필터링 및 연결
   • product_id로 주문과 상품 테이블 연결 (why 카테고리, 주문 날짜를 같이 활용하기 위해)
   • 2023년 데이터만 필터링
2. 조건부 합계 - CASE WHEN, SUM
3. 그룹화 - CATEGORY </aside>

코드

SELECT p.category
        ,SUM(CASE WHEN MONTH(o.order_date) BETWEEN 1 AND 3 THEN p.price END) AS sales_q1
        ,SUM(CASE WHEN MONTH(o.order_date) BETWEEN 4 AND 6 THEN p.price END) AS sales_q2
        ,SUM(CASE WHEN MONTH(o.order_date) BETWEEN 7 AND 9 THEN p.price END) AS sales_q3
        ,SUM(CASE WHEN MONTH(o.order_date) BETWEEN 10 AND 12 THEN p.price END) AS sales_q4
FROM orders o LEFT JOIN products p
ON o.product_id = p.product_id
WHERE YEAR(o.order_date) = 2023
GROUP BY p.category
ORDER BY p.category

주의사항

피벗이란?

중심을 잡고 방향을 바꾸는 것을 뜻하며, 맥락에 따라 회전축, 방향 전환, 또는 전략적 전환을 의미한다.

SQL에선 행에 있는 고유한 값들을 열로 변환하여 데이터를 재구성하고 요약하는 것이다. 열 기준으로 늘어놓는다고 생각하면 이해하기 쉽다.

 

피벗을 하기 위해선

1. CASE WHEN과 GROUP BY를 활용해야 한다.GROUP BY를 통해 SUM, AVG 등을 활용하여 집계하는 것이다.행 기준 - GROUP BY - ex 가구데이터 - SUM(CASE WHEN) -ex 합계
2. 열 기준 - CASE WHEN - ex 분기
3. 예를들어 가구, 분기 별 매출액 합계를 구하고자 하면 다음과 같다.
4. 즉 CASE WHEN으로 열 기준으로 값들을 재구성하고,