[이제와서 시작하는 Metabase 마스터하기 #7] SQL로 고급 분석 - 네이티브 쿼리 활용

학습 목표

이 포스트를 마치면 다음을 할 수 있습니다:

• GUI 빌더의 한계를 넘어 SQL로 복잡한 분석 수행
• Window Functions, CTEs, Subqueries 활용
• 데이터베이스별 고유 기능 사용
• SQL Snippets로 재사용 가능한 로직 구축
• 쿼리 성능 최적화 기법 적용
• SQL과 GUI 빌더를 혼합하여 활용

Native Query란?

Native Query는 Metabase의 GUI 빌더 대신 직접 SQL을 작성하는 방식입니다.

🚀 v0.56 개선사항: 네이티브 쿼리에서도 시간 기반 그룹핑 UI가 지원됩니다. DATE_TRUNC을 수동으로 작성하지 않아도 일/주/월/분기/년 단위로 쉽게 그룹핑할 수 있습니다.

GUI vs SQL 비교

graph LR
    A[Analysis Need] --> B{Complexity}
    B -->|Simple| C[GUI Builder]
    B -->|Complex| D[Native SQL]

    C --> C1[No SQL knowledge]
    C --> C2[Quick & Easy]
    C --> C3[Limited capabilities]

    D --> D1[SQL expertise]
    D --> D2[Full power]
    D --> D3[Any complexity]

SQL이 필요한 경우

1. Window Functions: 순위, 누적합, 이동평균
2. 복잡한 조인: 3개 이상 테이블, Self-join
3. 서브쿼리: 중간 계산 결과 활용
4. 집계의 집계: 평균의 평균, 최대값의 합계
5. 조건부 로직: CASE WHEN의 복잡한 조합
6. 성능 최적화: 인덱스 힌트, 특정 실행 계획

Native Query 시작하기

Step 1: SQL Editor 열기

New > Question > Native Query
또는
Existing Question > Convert to SQL

Step 2: 기본 쿼리 작성

SELECT
  id,
  created_at,
  total,
  user_id
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100

Step 3: 실행 및 시각화

1. Run 버튼 클릭 (또는 Cmd/Ctrl + Enter)
2. 결과 확인
3. Visualization 선택
4. Save

Window Functions 마스터하기

Window Functions는 행별 계산을 수행하면서 집계 컨텍스트를 유지합니다.

1. ROW_NUMBER - 순위 매기기

예제: 고객별 최근 주문

SELECT
  customer_id,
  order_id,
  created_at,
  total,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC
  ) as order_rank
FROM orders
QUALIFY order_rank = 1  -- Snowflake
-- 또는
WHERE order_rank = 1  -- 서브쿼리 사용

결과:

customer_id	order_id	created_at	total	order_rank
101	5001	2025-03-20	$150	1
102	5002	2025-03-19	$200	1

2. RANK vs DENSE_RANK

차이점:

SELECT
  product_name,
  revenue,
  RANK() OVER (ORDER BY revenue DESC) as rank,
  DENSE_RANK() OVER (ORDER BY revenue DESC) as dense_rank
FROM (
  SELECT
    product_name,
    SUM(total) as revenue
  FROM orders
  GROUP BY product_name
)

결과:

product_name	revenue	rank	dense_rank
Product A	$1000	1	1
Product B	$1000	1	1
Product C	$800	3	2	← 차이!
Product D	$700	4	3

RANK: 공동 순위 다음은 건너뜀 (1, 1, 3, 4) DENSE_RANK: 연속된 순위 (1, 1, 2, 3)

3. SUM OVER - 누적합

예제: 일별 누적 매출

SELECT
  DATE(created_at) as order_date,
  SUM(total) as daily_revenue,
  SUM(SUM(total)) OVER (
    ORDER BY DATE(created_at)
  ) as cumulative_revenue
FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY DATE(created_at)
ORDER BY order_date

결과:

order_date	daily_revenue	cumulative_revenue
2025-01-01	$1,200	$1,200
2025-01-02	$1,500	$2,700
2025-01-03	$1,100	$3,800

시각화: Line chart with 2 series

4. Moving Average - 이동평균

예제: 7일 이동평균

SELECT
  DATE(created_at) as order_date,
  SUM(total) as daily_revenue,
  AVG(SUM(total)) OVER (
    ORDER BY DATE(created_at)
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7d
FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY DATE(created_at)
ORDER BY order_date

활용: 일별 변동성 제거, 트렌드 파악

5. LAG/LEAD - 이전/다음 행 참조

예제: 전일 대비 증감

SELECT
  DATE(created_at) as order_date,
  SUM(total) as revenue,
  LAG(SUM(total)) OVER (ORDER BY DATE(created_at)) as prev_day_revenue,
  SUM(total) - LAG(SUM(total)) OVER (ORDER BY DATE(created_at)) as revenue_change,
  ((SUM(total) - LAG(SUM(total)) OVER (ORDER BY DATE(created_at)))
    / LAG(SUM(total)) OVER (ORDER BY DATE(created_at))) * 100 as pct_change
FROM orders
WHERE created_at >= '2025-03-01'
GROUP BY DATE(created_at)
ORDER BY order_date

결과:

order_date	revenue	prev_day_revenue	revenue_change	pct_change
2025-03-01	$1,200	NULL	NULL	NULL
2025-03-02	$1,500	$1,200	$300	25.0%
2025-03-03	$1,400	$1,500	-$100	-6.7%

CTEs (Common Table Expressions)

CTEs는 복잡한 쿼리를 모듈화하고 가독성을 높입니다.

1. 기본 CTE

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) as month,
    SUM(total) as revenue
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  revenue,
  AVG(revenue) OVER () as avg_monthly_revenue,
  revenue - AVG(revenue) OVER () as variance
FROM monthly_revenue
ORDER BY month

2. 다중 CTE

WITH
-- CTE 1: 고객별 총 주문액
customer_totals AS (
  SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total) as total_spent
  FROM orders
  GROUP BY customer_id
),
-- CTE 2: 고객 세그먼트 정의
customer_segments AS (
  SELECT
    customer_id,
    order_count,
    total_spent,
    CASE
      WHEN total_spent >= 1000 THEN 'VIP'
      WHEN total_spent >= 500 THEN 'Regular'
      ELSE 'Casual'
    END as segment
  FROM customer_totals
)
-- 최종 쿼리
SELECT
  segment,
  COUNT(*) as customer_count,
  AVG(total_spent) as avg_spent,
  SUM(total_spent) as total_revenue
FROM customer_segments
GROUP BY segment
ORDER BY total_revenue DESC

3. Recursive CTE

예제: 조직도 계층 구조

WITH RECURSIVE org_hierarchy AS (
  -- Base case: 최상위 관리자
  SELECT
    id,
    name,
    manager_id,
    1 as level,
    name as path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: 하위 직원들
  SELECT
    e.id,
    e.name,
    e.manager_id,
    oh.level + 1,
    oh.path || ' > ' || e.name
  FROM employees e
  JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT
  level,
  name,
  path
FROM org_hierarchy
ORDER BY path

Subqueries (서브쿼리)

1. Scalar Subquery

예제: 평균 대비 비율

SELECT
  product_name,
  price,
  (SELECT AVG(price) FROM products) as avg_price,
  price / (SELECT AVG(price) FROM products) as price_ratio
FROM products
ORDER BY price_ratio DESC

2. Correlated Subquery

예제: 카테고리 내 순위

SELECT
  p1.category,
  p1.product_name,
  p1.sales,
  (
    SELECT COUNT(*)
    FROM products p2
    WHERE p2.category = p1.category
      AND p2.sales > p1.sales
  ) + 1 as rank_in_category
FROM products p1
ORDER BY category, rank_in_category

3. Subquery in FROM

SELECT
  category,
  AVG(sales) as avg_sales,
  MAX(sales) as max_sales
FROM (
  SELECT
    category,
    product_name,
    SUM(quantity * price) as sales
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
  GROUP BY category, product_name
) as product_sales
GROUP BY category

고급 분석 패턴

1. Cohort Analysis (코호트 분석)

예제: 월별 신규 고객의 재구매율

WITH
-- 고객의 첫 주문 월
first_orders AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(created_at)) as cohort_month
  FROM orders
  GROUP BY customer_id
),
-- 모든 주문에 코호트 월 태깅
orders_with_cohort AS (
  SELECT
    o.*,
    fo.cohort_month,
    DATE_TRUNC('month', o.created_at) as order_month,
    DATEDIFF('month', fo.cohort_month, o.created_at) as months_since_first
  FROM orders o
  JOIN first_orders fo ON o.customer_id = fo.customer_id
)
-- 코호트별 월차별 활성 고객 수
SELECT
  cohort_month,
  months_since_first,
  COUNT(DISTINCT customer_id) as active_customers
FROM orders_with_cohort
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first

Pivot 시각화:

	Month 0	Month 1	Month 2	Month 3
2025-01	150	75	60	45
2025-02	180	90	70	-
2025-03	200	100	-	-

2. Funnel Analysis (퍼널 분석)

WITH funnel_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) as viewed,
    MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) as added_to_cart,
    MAX(CASE WHEN event_type = 'checkout' THEN 1 ELSE 0 END) as checked_out,
    MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) as purchased
  FROM events
  WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
  GROUP BY user_id
)
SELECT
  'Page View' as step,
  SUM(viewed) as users,
  100.0 as conversion_rate
FROM funnel_steps
UNION ALL
SELECT
  'Add to Cart',
  SUM(added_to_cart),
  (SUM(added_to_cart) * 100.0 / NULLIF(SUM(viewed), 0))
FROM funnel_steps
UNION ALL
SELECT
  'Checkout',
  SUM(checked_out),
  (SUM(checked_out) * 100.0 / NULLIF(SUM(viewed), 0))
FROM funnel_steps
UNION ALL
SELECT
  'Purchase',
  SUM(purchased),
  (SUM(purchased) * 100.0 / NULLIF(SUM(viewed), 0))
FROM funnel_steps
ORDER BY users DESC

시각화: Funnel chart

3. RFM Segmentation

Recency, Frequency, Monetary 세그멘테이션:

WITH customer_metrics AS (
  SELECT
    customer_id,
    MAX(created_at) as last_order_date,
    COUNT(*) as order_count,
    SUM(total) as total_spent
  FROM orders
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    DATEDIFF('day', last_order_date, CURRENT_DATE) as recency,
    order_count as frequency,
    total_spent as monetary,
    NTILE(5) OVER (ORDER BY DATEDIFF('day', last_order_date, CURRENT_DATE) DESC) as r_score,
    NTILE(5) OVER (ORDER BY order_count) as f_score,
    NTILE(5) OVER (ORDER BY total_spent) as m_score
  FROM customer_metrics
)
SELECT
  customer_id,
  recency,
  frequency,
  monetary,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 THEN 'Champions'
    WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'Promising'
    WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
    WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
    ELSE 'Others'
  END as segment
FROM rfm_scores

SQL Snippets (재사용 가능한 코드)

1. Snippet 생성

Admin > Settings > SQL Snippets > Create snippet

Name: date_ranges
Description: Common date range filters
Content:
CASE {{period}}
  WHEN 'today' THEN created_at >= CURRENT_DATE
  WHEN 'yesterday' THEN created_at >= CURRENT_DATE - 1
                    AND created_at < CURRENT_DATE
  WHEN 'last_7_days' THEN created_at >= CURRENT_DATE - 7
  WHEN 'last_30_days' THEN created_at >= CURRENT_DATE - 30
  WHEN 'this_month' THEN created_at >= DATE_TRUNC('month', CURRENT_DATE)
  WHEN 'last_month' THEN created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
                      AND created_at < DATE_TRUNC('month', CURRENT_DATE)
END

2. Snippet 사용

SELECT
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders
WHERE {{ snippet: date_ranges }}

3. 유용한 Snippet 예제

A. 고객 세그먼트 로직

-- Snippet: customer_segment
CASE
  WHEN total_spent >= 1000 THEN 'VIP'
  WHEN total_spent >= 500 THEN 'Regular'
  WHEN total_spent >= 100 THEN 'Casual'
  ELSE 'New'
END

B. 작업일 계산 (주말 제외)

-- Snippet: business_days_between
DATEDIFF('day', {{start_date}}, {{end_date}})
  - (DATEDIFF('week', {{start_date}}, {{end_date}}) * 2)
  - CASE WHEN DAYOFWEEK({{start_date}}) = 0 THEN 1 ELSE 0 END
  - CASE WHEN DAYOFWEEK({{end_date}}) = 6 THEN 1 ELSE 0 END

C. 통화 포맷

-- Snippet: format_currency
CONCAT('$', FORMAT({{amount}}, 2))

데이터베이스별 특수 기능

PostgreSQL

JSON 처리:

SELECT
  id,
  metadata->>'customer_name' as customer_name,
  metadata->'items'->0->>'product' as first_product,
  jsonb_array_length(metadata->'items') as item_count
FROM orders
WHERE metadata->>'status' = 'completed'

Array 연산:

SELECT
  category,
  ARRAY_AGG(product_name ORDER BY sales DESC) as top_products
FROM products
GROUP BY category

MySQL

Full-text Search:

SELECT
  product_name,
  description,
  MATCH(product_name, description) AGAINST ('gaming laptop' IN NATURAL LANGUAGE MODE) as relevance
FROM products
WHERE MATCH(product_name, description) AGAINST ('gaming laptop')
ORDER BY relevance DESC

Snowflake

QUALIFY Clause:

SELECT
  customer_id,
  order_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) as rank
FROM orders
QUALIFY rank <= 3  -- 각 고객의 상위 3개 주문만

Lateral Flatten (JSON):

SELECT
  order_id,
  f.value:product_id::VARCHAR as product_id,
  f.value:quantity::INT as quantity
FROM orders,
LATERAL FLATTEN(input => items_json) f

BigQuery

ARRAY and STRUCT:

SELECT
  order_id,
  (SELECT SUM(quantity) FROM UNNEST(items)) as total_items,
  ARRAY_AGG(STRUCT(product_id, quantity)) as items_array
FROM orders
GROUP BY order_id

쿼리 최적화

1. EXPLAIN PLAN 분석

EXPLAIN
SELECT
  c.name,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC

체크 포인트:

• Seq Scan (전체 스캔) → Index Scan으로 개선 필요
• Hash Join vs Nested Loop
• Estimated rows vs Actual rows

2. 인덱스 활용

-- 인덱스가 있는 컬럼 확인
SELECT
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE tablename = 'orders'

-- 인덱스 생성 권장
CREATE INDEX idx_orders_customer_created
  ON orders(customer_id, created_at)

3. WHERE vs HAVING

-- 나쁜 예 (HAVING으로 필터링)
SELECT
  customer_id,
  SUM(total) as total_spent
FROM orders
GROUP BY customer_id
HAVING MAX(created_at) >= '2025-01-01'

-- 좋은 예 (WHERE로 미리 필터링)
SELECT
  customer_id,
  SUM(total) as total_spent
FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY customer_id

4. Limit 활용

-- 개발/테스트 시
SELECT *
FROM large_table
LIMIT 1000  -- 전체 데이터 대신 샘플만

-- 프로덕션에서는 적절한 필터 사용

5. JOIN 순서

-- 작은 테이블을 먼저 필터링
WITH filtered_orders AS (
  SELECT *
  FROM orders
  WHERE created_at >= '2025-01-01'  -- 90% 제거
)
SELECT
  c.name,
  COUNT(o.id)
FROM customers c
JOIN filtered_orders o ON c.id = o.customer_id
GROUP BY c.name

SQL과 GUI 혼합 활용

1. SQL Question을 GUI에서 확장

1. SQL로 Base Question 생성:
   SELECT customer_id, SUM(total) as total_spent
   FROM orders
   GROUP BY customer_id

2. 저장: "Customer Totals"

3. 새 Question에서:
   Data: "Customer Totals" (저장된 Question)
   GUI Builder로 추가 집계:
     - Summarize: Average of total_spent
     - Group by: (없음)

→ SQL의 결과를 GUI로 재가공

2. Models로 SQL 추상화

-- Model: "Clean Orders"
SELECT
  o.id,
  o.created_at,
  o.total,
  c.name as customer_name,
  c.state as customer_state,
  p.name as product_name,
  p.category as product_category
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01'

-- GUI 사용자는 이 Model을 일반 테이블처럼 사용
-- 복잡한 조인 로직 숨김

실전 연습 문제

연습 1: Window Functions (초급)

과제: 각 카테고리의 상위 3개 상품 찾기

해답 보기

WITH product_sales AS (
  SELECT
    p.category,
    p.product_name,
    SUM(oi.quantity * oi.price) as total_sales
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
  GROUP BY p.category, p.product_name
),
ranked_products AS (
  SELECT
    category,
    product_name,
    total_sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY total_sales DESC
    ) as rank
  FROM product_sales
)
SELECT
  category,
  product_name,
  total_sales,
  rank
FROM ranked_products
WHERE rank <= 3
ORDER BY category, rank

연습 2: Cohort Analysis (중급)

과제: 2025년 1월 신규 고객의 월별 재구매율 계산

해답 보기

WITH jan_cohort AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE DATE_TRUNC('month', created_at) = '2025-01-01'
),
monthly_activity AS (
  SELECT
    o.customer_id,
    DATE_TRUNC('month', o.created_at) as activity_month,
    DATEDIFF('month', '2025-01-01', DATE_TRUNC('month', o.created_at)) as months_since
  FROM orders o
  JOIN jan_cohort jc ON o.customer_id = jc.customer_id
  WHERE o.created_at >= '2025-01-01'
  GROUP BY o.customer_id, DATE_TRUNC('month', o.created_at)
)
SELECT
  months_since,
  COUNT(DISTINCT customer_id) as active_customers,
  (COUNT(DISTINCT customer_id) * 100.0 /
    (SELECT COUNT(*) FROM jan_cohort)) as retention_rate
FROM monthly_activity
GROUP BY months_since
ORDER BY months_since

연습 3: Advanced Analytics (고급)

과제: ABC 분석 - 상위 20% 고객이 전체 매출의 몇 %를 차지하는가?

해답 보기

WITH customer_revenue AS (
  SELECT
    customer_id,
    SUM(total) as total_revenue
  FROM orders
  GROUP BY customer_id
),
customer_percentiles AS (
  SELECT
    customer_id,
    total_revenue,
    NTILE(5) OVER (ORDER BY total_revenue DESC) as quintile,
    SUM(total_revenue) OVER () as total_company_revenue
  FROM customer_revenue
)
SELECT
  quintile,
  COUNT(*) as customer_count,
  SUM(total_revenue) as quintile_revenue,
  (SUM(total_revenue) / MAX(total_company_revenue)) * 100 as revenue_percentage,
  SUM(SUM(total_revenue)) OVER (ORDER BY quintile) / MAX(total_company_revenue) * 100 as cumulative_percentage
FROM customer_percentiles
GROUP BY quintile
ORDER BY quintile

-- 일반적 결과:
-- Quintile 1 (상위 20%): 60-70% 매출
-- Quintile 2: 15-20% 매출
-- Quintile 3-5: 10-25% 매출

다음 단계

SQL 고급 분석을 마스터했습니다. 다음 포스트에서는:

• Data Modeling: Models, Metrics, Segments로 비즈니스 로직 재사용
• Semantic Layer: 일관된 메트릭 정의
• Query Performance: 대용량 데이터 최적화

요약

SQL 기능 매트릭스

기능	난이도	사용 빈도	성능 영향
Window Functions	⭐⭐⭐	높음	중간
CTEs	⭐⭐	높음	낮음
Subqueries	⭐⭐	중간	높음
Recursive CTEs	⭐⭐⭐⭐	낮음	높음
JSON 처리	⭐⭐⭐	중간	중간

SQL 작성 체크리스트

쿼리 작성:

• CTE로 복잡한 로직 분해
• Window Functions 활용 검토
• 재사용 가능한 부분은 Snippet으로

최적화:

• WHERE로 먼저 필터링 (HAVING 최소화)
• 인덱스된 컬럼 사용
• LIMIT으로 결과 제한
• EXPLAIN PLAN 확인

가독성:

• 일관된 들여쓰기
• 명확한 별칭 사용
• 주석으로 복잡한 로직 설명
• CTE 이름을 의미 있게

다음 포스트에서는 SQL을 재사용 가능한 비즈니스 로직으로 추상화하는 Data Modeling을 배웁니다!

📚 시리즈 전체 목차

🚀 기초편 (1-5화)

1. Metabase 소개와 핵심 개념
2. 설치와 초기 설정
3. 샘플 데이터 둘러보기
4. 첫 차트 만들기 - 실습 완전정복
5. 대시보드 만들기 - 한 화면에 모으기

💪 활용편 (6-10화)

1. 필터와 파라미터
2. [SQL 네이티브 쿼리] (현재 글)
3. 데이터 모델링
4. 자동화와 알림
5. 권한과 보안

🎯 고급편 (11-16화)

1. 임베딩과 공유
2. 성능 최적화
3. 멀티 데이터소스
4. 커스터마이징
5. 운영과 모니터링
6. 실전 프로젝트