Apache Druid

-- Apache Druid OLAP 쿼리 예제

-- ==========================================
-- 1. 기본 집계 쿼리 (시간 기반)
-- ==========================================

-- 일별 매출 집계
SELECT
  TIME_FLOOR(__time, 'P1D') AS day,
  COUNT(*) AS transaction_count,
  SUM(amount) AS total_revenue,
  COUNT(DISTINCT user_id) AS unique_users,
  AVG(amount) AS avg_transaction
FROM sales_events
WHERE __time >= TIMESTAMP '2024-01-01'
  AND __time < TIMESTAMP '2024-02-01'
GROUP BY TIME_FLOOR(__time, 'P1D')
ORDER BY day;

-- ==========================================
-- 2. 다차원 분석 (GROUPING SETS)
-- ==========================================

-- 카테고리, 지역별 복합 집계
SELECT
  COALESCE(category, 'ALL') AS category,
  COALESCE(region, 'ALL') AS region,
  COUNT(*) AS events,
  SUM(revenue) AS total_revenue
FROM page_views
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
GROUP BY GROUPING SETS (
  (category, region),
  (category),
  (region),
  ()
)
ORDER BY category, region;

-- ==========================================
-- 3. 퍼널 분석 쿼리
-- ==========================================

-- 사용자 전환 퍼널
SELECT
  step,
  COUNT(DISTINCT user_id) AS users,
  COUNT(DISTINCT user_id) * 100.0 /
    FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (ORDER BY step) AS conversion_rate
FROM (
  SELECT
    user_id,
    MIN(CASE WHEN event_type = 'page_view' THEN 1 END) AS step1,
    MIN(CASE WHEN event_type = 'add_to_cart' THEN 2 END) AS step2,
    MIN(CASE WHEN event_type = 'checkout' THEN 3 END) AS step3,
    MIN(CASE WHEN event_type = 'purchase' THEN 4 END) AS step4
  FROM user_events
  WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  GROUP BY user_id
)
UNPIVOT (step FOR step_name IN (step1, step2, step3, step4))
WHERE step IS NOT NULL
GROUP BY step;

-- ==========================================
-- 4. 실시간 대시보드 쿼리
-- ==========================================

-- 최근 5분 실시간 메트릭
SELECT
  TIME_FLOOR(__time, 'PT1M') AS minute,
  datasource,
  COUNT(*) AS requests_per_min,
  APPROX_QUANTILE_DS(response_time, 0.95) AS p95_latency,
  APPROX_QUANTILE_DS(response_time, 0.99) AS p99_latency
FROM request_logs
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '5' MINUTE
GROUP BY 1, 2
ORDER BY 1 DESC;

-- ==========================================
-- 5. 데이터 수집 스펙 (Kafka Ingestion)
-- ==========================================

-- JSON 형식의 Kafka Ingestion Spec
/*
{
  "type": "kafka",
  "spec": {
    "dataSchema": {
      "dataSource": "sales_events",
      "timestampSpec": {
        "column": "event_time",
        "format": "iso"
      },
      "dimensionsSpec": {
        "dimensions": [
          "user_id",
          "product_id",
          "category",
          {"name": "price", "type": "double"},
          "region"
        ]
      },
      "metricsSpec": [
        {"type": "count", "name": "count"},
        {"type": "longSum", "name": "quantity", "fieldName": "quantity"},
        {"type": "doubleSum", "name": "revenue", "fieldName": "revenue"},
        {"type": "HLLSketchBuild", "name": "unique_users", "fieldName": "user_id"}
      ],
      "granularitySpec": {
        "type": "uniform",
        "segmentGranularity": "HOUR",
        "queryGranularity": "MINUTE",
        "rollup": true
      }
    },
    "ioConfig": {
      "topic": "sales-events",
      "consumerProperties": {
        "bootstrap.servers": "kafka:9092"
      },
      "taskCount": 3,
      "replicas": 2
    },
    "tuningConfig": {
      "type": "kafka",
      "maxRowsInMemory": 100000,
      "maxRowsPerSegment": 5000000
    }
  }
}
*/