Pivot, Unpivot 그리고 시계열 데이터 분석

Pivot & Unpivot

📌 정의

구분	설명	변환 방향
Pivot	행을 열로 변환	반시계 방향
Unpivot	열을 행으로 변환	시계 방향

---

Pivot — CASE문 활용

목적: activebool별 고객 수를 한 행으로 표현

SELECT
  store_id,
  COUNT(CASE WHEN activebool THEN 1 END) AS active_customers,
  COUNT(CASE WHEN NOT activebool THEN 1 END) AS inactive_customers
FROM customer
GROUP BY store_id;

행 데이터 → 열 형태로 집계

---

Pivot — 성별(가정)별 고객 수

목적: 각 store_id별로 성별 고객 수 집계 (first_name의 앞 글자를 성별로 간주 예시)

SELECT
  store_id,
  SUM(CASE WHEN LEFT(first_name,1) BETWEEN 'A' AND 'M' THEN 1 ELSE 0 END) AS assumed_female,
  SUM(CASE WHEN LEFT(first_name,1) > 'M' THEN 1 ELSE 0 END) AS assumed_male
FROM customer
GROUP BY store_id;

---

Unpivot — UNION 활용

목적: 속성·값 형태로 열을 행으로 변환
예제: 한 고객의 이메일, 이름 정보를 "속성-값" 쌍으로 풀어보기

SELECT customer_id, 'first_name' AS field, first_name AS value FROM customer
UNION ALL
SELECT customer_id, 'last_name', last_name FROM customer
UNION ALL
SELECT customer_id, 'email', email FROM customer
ORDER BY customer_id;

---

시계열 데이터 분석

SQL 기반 분석의 장점

• DB 내 직접 처리 → 속도, 일관성, 자동화
• Excel/Python 대비 대용량 안정성
• View/Procedure로 스케줄링 가능
• Out-of-Memory 문제 방지

---

시계열 실습 항목

1. 날짜별 매출 집계
2. 7일 이동평균 추이
3. 당월 매출 누계
4. 월별 매출 전월대비 비교

---

① 날짜별 매출 집계

SELECT 
  DATE(payment_date) AS sales_date,
  COUNT(*) AS sales_count,
  AVG(amount)::numeric(10,2) AS average_sales,
  SUM(amount) AS total_sales
FROM payment
GROUP BY DATE(payment_date)
ORDER BY sales_date;

일자별 판매량, 평균, 총합

---

② 7일 이동평균 추이

SELECT 
  payment_date::date,
  SUM(amount) AS daily_total,
  ROUND(AVG(SUM(amount)) 
    OVER (ORDER BY payment_date::date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS moving_avg_7days
FROM payment
GROUP BY payment_date::date
ORDER BY payment_date;

최근 7일 매출 평균으로 변동성 완화

7일 평균을 구할 수 없는 6일까지는 null로 채우는 방법

SELECT payment_date::date, SUM(amount) AS daily_total
,ROUND(
  AVG(SUM(amount)) OVER (
    ORDER BY payment_date::date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2
) AS moving_avg_7days
, CASE
  WHEN 7 = COUNT(*) OVER (
    ORDER BY payment_date::date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  )
  THEN ROUND(
    AVG(SUM(amount)) OVER (
      ORDER BY payment_date::date 
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2
  )
END AS moving_avg_7days_strict

FROM payment
GROUP BY payment_date::date
ORDER BY Payment_date;

---

③ 당월 매출 누계

월별로 목표를 설정하는 업계에서는 날짜 별 매출 뿐만 아니라, 해당 월에 어느 정도의 매출이 누적되어 있는지를 동시에 확인 할 수 있어야 함.

SELECT 
  TO_CHAR(payment_date, 'YYYY-MM') AS year_month,
  DATE(payment_date) AS sales_date,
  SUM(amount) AS daily_sales,
  SUM(SUM(amount)) 
    OVER (PARTITION BY TO_CHAR(payment_date, 'YYYY-MM') ORDER BY DATE(payment_date)) AS cumulative_sales
FROM payment
GROUP BY TO_CHAR(payment_date, 'YYYY-MM'), DATE(payment_date)
ORDER BY year_month, sales_date;

월별 누적 매출 — 목표 대비 진척률 확인

---

④ 월별 매출 전월대비 비교

전월과 비교하여 성장률을 계산하기 위해서 LAG를 이용하여 전 달값(바로 앞의 행)을 참조함

SELECT 
  TO_CHAR(payment_date,'YYYY-MM') AS year_month,
  SUM(amount) AS monthly_sales,
  LAG(SUM(amount)) OVER (ORDER BY TO_CHAR(payment_date,'YYYY-MM')) AS pre_month_sales,
  ROUND(
    (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY TO_CHAR(payment_date,'YYYY-MM')))
    / NULLIF(LAG(SUM(amount)) OVER (ORDER BY TO_CHAR(payment_date,'YYYY-MM')), 0) * 100, 2
  ) AS growth_rate
FROM payment
GROUP BY TO_CHAR(payment_date,'YYYY-MM')
ORDER BY year_month;

전월 매출 대비 증감률 계산
LAG() = 이전 행(전월) 참조

---

요약

• Pivot → CASE문
• Unpivot → UNION
• 시계열 분석 → 집계함수 + 윈도우함수
• SQL 기반 전처리 → 자동화·일관성·성능 향상