Metric Views 심화: SQL 문법과 조직 전략

CREATE OR REPLACE METRIC VIEW catalog.schema.ecommerce_metrics
COMMENT '이커머스 핵심 비즈니스 메트릭 (복합 메트릭 포함)'
AS SELECT
  -- 차원 (Dimensions)
  order_date AS time                  DIMENSION COMMENT '주문 날짜',
  region                              DIMENSION COMMENT '판매 지역',
  product_category                    DIMENSION COMMENT '제품 카테고리',
  channel                             DIMENSION COMMENT '판매 채널 (온라인/오프라인/앱)',

  -- 기본 측정값 (Base Measures)
  SUM(order_amount)                   MEASURE total_revenue
                                      COMMENT '총 매출 (환불 제외)',
  COUNT(DISTINCT order_id)            MEASURE order_count
                                      COMMENT '총 주문 건수',
  COUNT(DISTINCT customer_id)         MEASURE unique_customers
                                      COMMENT '고유 고객 수',
  SUM(CASE WHEN is_returned = true THEN 1 ELSE 0 END)
                                      MEASURE return_count
                                      COMMENT '반품 건수',
  SUM(cost_amount)                    MEASURE total_cost
                                      COMMENT '총 비용',

  -- 복합 측정값 (Derived Measures)
  SUM(order_amount) / COUNT(DISTINCT order_id)
                                      MEASURE avg_order_value
                                      COMMENT '평균 주문 금액 (AOV)',
  SUM(order_amount) / COUNT(DISTINCT customer_id)
                                      MEASURE revenue_per_customer
                                      COMMENT '고객당 매출 (ARPU)',
  (SUM(order_amount) - SUM(cost_amount)) / NULLIF(SUM(order_amount), 0) * 100
                                      MEASURE gross_margin_pct
                                      COMMENT '매출 총이익률 (%)',
  SUM(CASE WHEN is_returned = true THEN 1 ELSE 0 END) * 100.0
    / NULLIF(COUNT(DISTINCT order_id), 0)
                                      MEASURE return_rate_pct
                                      COMMENT '반품률 (%)'

FROM catalog.schema.gold_orders
WHERE order_status = 'completed';

-- 월별 매출과 전월 대비 증감을 보는 예시
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', time) AS month,
    total_revenue
  FROM catalog.schema.ecommerce_metrics
)
SELECT
  month,
  total_revenue,
  LAG(total_revenue) OVER (ORDER BY month) AS prev_month_revenue,
  (total_revenue - LAG(total_revenue) OVER (ORDER BY month))
    / NULLIF(LAG(total_revenue) OVER (ORDER BY month), 0) * 100 AS mom_growth_pct
FROM monthly
ORDER BY month DESC;

CREATE OR REPLACE METRIC VIEW catalog.schema.channel_metrics
COMMENT '채널별 세분화된 매출 메트릭'
AS SELECT
  order_date AS time             DIMENSION,
  region                         DIMENSION,

  -- 전체 매출
  SUM(order_amount)              MEASURE total_revenue
                                 COMMENT '전체 매출',
  -- 온라인 매출만
  SUM(CASE WHEN channel = 'online' THEN order_amount ELSE 0 END)
                                 MEASURE online_revenue
                                 COMMENT '온라인 채널 매출',
  -- 앱 매출만
  SUM(CASE WHEN channel = 'app' THEN order_amount ELSE 0 END)
                                 MEASURE app_revenue
                                 COMMENT '앱 채널 매출',
  -- 온라인 비중
  SUM(CASE WHEN channel = 'online' THEN order_amount ELSE 0 END) * 100.0
    / NULLIF(SUM(order_amount), 0)
                                 MEASURE online_revenue_share_pct
                                 COMMENT '온라인 매출 비중 (%)'

FROM catalog.schema.gold_orders
WHERE order_status = 'completed';

사용자 쿼리:
  SELECT region, total_revenue FROM catalog.schema.revenue_metrics

↓ 쿼리 리라이트 엔진 (Query Planner)

실제 실행되는 SQL:
  SELECT
    region,
    SUM(order_amount) AS total_revenue
  FROM catalog.schema.gold_orders
  WHERE order_status = 'completed'
  GROUP BY region

사용자: "이번 달 지역별 매출을 보여줘"

1. Genie NLU → 의도 파악: 시간 필터(이번 달) + 차원(region) + 측정값(total_revenue)
2. Metric View 매칭 → revenue_metrics의 total_revenue MEASURE 선택
3. SQL 생성:
   SELECT region, total_revenue
   FROM catalog.schema.revenue_metrics
   WHERE time >= DATE_TRUNC('month', CURRENT_DATE())
4. 자동 집계 엔진 → 실제 SQL로 변환 후 실행
5. 결과를 자연어로 포맷팅하여 반환