"이건 Python으로 해야 해요" — SQL로 Pivot, JSON, UTM, RFM 전부 끝내기

Python 스크립트 100줄 vs SQL 한 줄

"이거 Python으로 ETL 짜야 해요" — 이 말을 들을 때마다 의문이 듭니다. Airflow 세팅하고, 의존성 관리하고, 스케줄러 설정하고... 정말 그래야 할까요?

실무에서 마주치는 데이터 가공 작업의 80%는 SQL만으로 충분합니다.

이 글에서는 흔히 "Python 필요"라고 생각하는 Pivot, JSON 파싱, UTM 추출, RFM 세그먼테이션을 SQL 하나로 끝내는 패턴을 다룹니다.

1. SQL vs Python: 언제 무엇을 선택할까

SQL이 더 나은 경우

• 집계/그룹화: DB 엔진의 병렬 처리가 pandas보다 빠릅니다
• Pivot/Unpivot: BigQuery, Snowflake 네이티브 지원
• JSON 파싱: JSON_EXTRACT, JSON_VALUE 등 내장 함수
• 정규식 추출: REGEXP_EXTRACT로 UTM 등 추출
• 대용량 조인: 분산 처리, 브로드캐스트 조인

Python이 필요한 경우

• 외부 API 호출: REST API, 크롤링
• ML 모델 적용: 예측, 클러스터링
• 복잡한 NLP: 토큰화, 형태소 분석
• 상태 유지 처리: 이전 행 결과에 따른 누적 계산

비용 비교

2. Pivot — 행 데이터를 리포트로 변환

문제 상황

DB는 행 중심으로 저장하지만, 리포트는 열 중심이어야 읽기 좋습니다.

CASE WHEN 방식 (범용)

SELECT
  category,
  COALESCE(SUM(CASE WHEN MONTH(order_date) = 1 THEN revenue END), 0) AS jan,
  COALESCE(SUM(CASE WHEN MONTH(order_date) = 2 THEN revenue END), 0) AS feb,
  COALESCE(SUM(CASE WHEN MONTH(order_date) = 3 THEN revenue END), 0) AS mar
FROM orders
GROUP BY category;

핵심: COALESCE로 NULL 방어. NULL + 숫자 = NULL이므로 집계가 왜곡될 수 있습니다.

BigQuery PIVOT 연산자

SELECT * FROM (
  SELECT category, EXTRACT(MONTH FROM order_date) AS month, revenue
  FROM orders
  WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'
)
PIVOT (
  SUM(revenue)
  FOR month IN (1 AS jan, 2 AS feb, 3 AS mar)
)
ORDER BY category;

네이티브 PIVOT은 CASE WHEN 대비 10-30% 성능 향상이 있습니다.

3. JSON 파싱 — 웹 이벤트 로그 다루기

왜 JSON인가

GA4, Amplitude 등 모든 이벤트 로그가 JSON입니다. API 응답도 JSON으로 저장하면 스키마 변경에 유연합니다.

기본 파싱

SELECT
  user_id,
  JSON_EXTRACT_SCALAR(event_params, '$.page_url') AS page_url,
  JSON_EXTRACT_SCALAR(event_params, '$.campaign') AS campaign,
  JSON_EXTRACT_SCALAR(event_params, '$.device') AS device
FROM events
WHERE DATE(event_timestamp) = '2024-01-15';

성능 최적화: CTE로 한 번만 파싱

-- Good: CTE에서 한 번만 파싱
WITH parsed AS (
  SELECT *, JSON_EXTRACT_SCALAR(event_params, '$.page_url') AS page_url
  FROM events
)
SELECT * FROM parsed WHERE page_url LIKE '%/products%';

중첩 JSON 배열 처리

WITH order_items AS (
  SELECT order_id, item_json
  FROM orders, UNNEST(JSON_EXTRACT_ARRAY(items, '$')) AS item_json
)
SELECT
  order_id,
  JSON_EXTRACT_SCALAR(item_json, '$.product_id') AS product_id,
  CAST(JSON_EXTRACT_SCALAR(item_json, '$.qty') AS INT64) AS quantity
FROM order_items;

4. UTM 파라미터 추출 — 채널 성과 자동화

정규식으로 추출

WITH url_normalized AS (
  SELECT session_id,
    LOWER(REGEXP_REPLACE(
      REGEXP_REPLACE(landing_page, r'#.*$', ''),
      r'%20', ' '
    )) AS clean_url
  FROM sessions
)
SELECT
  session_id,
  REGEXP_EXTRACT(clean_url, r'utm_source=([^&]+)') AS utm_source,
  REGEXP_EXTRACT(clean_url, r'utm_medium=([^&]+)') AS utm_medium,
  REGEXP_EXTRACT(clean_url, r'utm_campaign=([^&]+)') AS utm_campaign
FROM url_normalized;

주의: 해시 프래그먼트(#) 제거, URL 디코딩, 소문자 변환을 해야 엣지 케이스를 처리할 수 있습니다.

채널 그룹핑

CASE
  WHEN utm_medium IN ('cpc', 'ppc', 'paidsearch') THEN 'Paid Search'
  WHEN utm_medium IN ('email', 'newsletter') THEN 'Email'
  WHEN utm_source IN ('facebook', 'instagram') AND utm_medium = 'cpc' THEN 'Paid Social'
  WHEN utm_source IS NULL THEN 'Direct'
  ELSE 'Other'
END AS channel_group

5. RFM 세그먼테이션 — 고객 등급 자동 분류

RFM이란

• R (Recency): 마지막 구매가 언제?
• F (Frequency): 얼마나 자주?
• M (Monetary): 얼마나 많이?

SQL로 RFM 계산

WITH customer_metrics AS (
  SELECT
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(order_amount) AS monetary
  FROM orders
  WHERE order_status = 'completed'
    AND order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    recency_days, frequency, monetary,
    NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
    NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
  FROM customer_metrics
)
SELECT
  customer_id,
  CONCAT(r_score, f_score, m_score) AS rfm_score,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
    WHEN f_score >= 4 AND m_score >= 3 THEN 'Loyal Customers'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
    WHEN r_score <= 2 AND f_score >= 4 THEN 'At Risk'
    WHEN r_score = 1 AND f_score <= 2 THEN 'Lost'
    ELSE 'Others'
  END AS segment
FROM rfm_scores;

NTILE의 함정

NTILE은 동점자를 임의로 분배합니다. 더 정확한 방식은 PERCENT_RANK 사용입니다.

6. 실전 ETL — 스케줄 쿼리로 마트 구축

ELT 패러다임

ETL(Extract-Transform-Load)에서 ELT(Extract-Load-Transform)로 바뀌었습니다. BigQuery, Snowflake에서는 원본을 먼저 적재하고, SQL로 변환합니다.

레이어드 아키텍처

1. Raw: 원본 그대로 저장
2. Clean: NULL 처리, JSON 파싱, UTM 추출
3. Aggregate: 일별/주별 집계
4. Mart: 비즈니스 도메인별 최종 테이블

전체 파이프라인 예시

-- Step 1: Raw -> Clean (매일 새벽 1시)
CREATE OR REPLACE TABLE analytics.clean_events AS
WITH deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingestion_timestamp DESC) AS rn
  FROM raw_events
  WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  event_id, user_id, event_name, event_timestamp,
  JSON_EXTRACT_SCALAR(properties, '$.page_url') AS page_url,
  REGEXP_EXTRACT(LOWER(JSON_EXTRACT_SCALAR(properties, '$.page_url')), r'utm_source=([^&]+)') AS utm_source,
  CURRENT_TIMESTAMP() AS processed_at
FROM deduped WHERE rn = 1;

7. 흔한 실수들

NULL 미처리

-- Good: COALESCE 또는 모니터링
SELECT
  SUM(COALESCE(revenue, 0)) AS total,
  COUNTIF(revenue IS NULL) AS null_count
FROM orders;

시간대 무시

-- Good: 비즈니스 시간대 적용
SELECT DATE(event_timestamp, 'Asia/Seoul') AS date FROM events;

마무리

"Python이 필요해요"라고 말하기 전에, SQL로 되는지 먼저 확인하세요.

잘 작성된 SQL 한 줄이 Python 스크립트 100줄보다:

• 유지보수하기 쉽고
• 성능도 좋으며
• 비용도 저렴합니다