성능 최적화 고급

-- 병렬 처리를 최대화하는 쿼리 패턴
WITH optimized_scan AS (
  -- 컬럼 프루닝: 필요한 컬럼만 선택
  SELECT 
    customer_id,
    order_date,
    order_amount,
    product_category
  FROM `project.sales.orders`
  WHERE 
    -- 파티션 프루닝: 파티션 키 필터 먼저
    DATE(order_date) BETWEEN '2024-01-01' AND '2024-03-31'
    -- 선택성 높은 필터 우선
    AND product_category IN ('Electronics', 'Clothing')
    AND order_amount > 100
),
pre_aggregated AS (
  -- 조기 집계로 데이터 크기 감소
  SELECT 
    customer_id,
    product_category,
    COUNT(*) as order_count,
    SUM(order_amount) as total_amount,
    AVG(order_amount) as avg_amount
  FROM optimized_scan
  GROUP BY customer_id, product_category
)
-- 최종 결과
SELECT 
  customer_id,
  SUM(total_amount) as customer_total,
  AVG(avg_amount) as customer_avg_order
FROM pre_aggregated
GROUP BY customer_id
ORDER BY customer_total DESC
LIMIT 1000;  -- 결과 크기 제한

-- 슬롯 효율성을 위한 쿼리 구조화
-- ❌ 비효율적: 순차적 처리
SELECT *
FROM (
  SELECT customer_id, SUM(amount) as total 
  FROM orders 
  GROUP BY customer_id
) a
JOIN (
  SELECT customer_id, COUNT(*) as order_count 
  FROM orders 
  GROUP BY customer_id  
) b USING (customer_id);

-- ✅ 효율적: 병렬 처리 최적화
WITH customer_metrics AS (
  SELECT 
    customer_id,
    SUM(amount) as total,
    COUNT(*) as order_count
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total, order_count
FROM customer_metrics;

-- 쿼리 성능 분석을 위한 메타데이터 활용
WITH query_analysis AS (
  SELECT 
    job_id,
    query,
    total_bytes_processed,
    total_slot_ms,
    
    -- 효율성 지표
    total_bytes_processed / NULLIF(total_slot_ms, 0) * 1000 as bytes_per_slot_ms,
    total_slot_ms / 1000 / GREATEST(TIMESTAMP_DIFF(end_time, start_time, SECOND), 1) as avg_slots_used,
    
    -- 복잡도 분석
    LENGTH(query) as query_length,
    (SELECT COUNT(*) FROM UNNEST(SPLIT(UPPER(query), 'JOIN'))) - 1 as join_count,
    (SELECT COUNT(*) FROM UNNEST(SPLIT(UPPER(query), 'SELECT'))) - 1 as subquery_count,
    
    -- 성능 등급
    CASE 
      WHEN total_bytes_processed / NULLIF(total_slot_ms, 0) * 1000 > 2000 THEN 'Excellent'
      WHEN total_bytes_processed / NULLIF(total_slot_ms, 0) * 1000 > 1000 THEN 'Good'
      WHEN total_bytes_processed / NULLIF(total_slot_ms, 0) * 1000 > 500 THEN 'Fair'
      ELSE 'Poor'
    END as performance_grade
    
  FROM `project.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  WHERE DATE(creation_time) = CURRENT_DATE()
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND total_slot_ms > 0
)
SELECT 
  performance_grade,
  COUNT(*) as query_count,
  ROUND(AVG(bytes_per_slot_ms), 2) as avg_efficiency,
  ROUND(AVG(avg_slots_used), 0) as avg_slots,
  ROUND(AVG(join_count), 1) as avg_joins
FROM query_analysis
GROUP BY performance_grade
ORDER BY 
  CASE performance_grade
    WHEN 'Excellent' THEN 1
    WHEN 'Good' THEN 2  
    WHEN 'Fair' THEN 3
    ELSE 4
  END;

-- 스테이지별 성능 분석 (Job Timeline 활용)
CREATE OR REPLACE FUNCTION `project.optimization.analyze_query_stages`(job_id_param STRING)
RETURNS ARRAY<STRUCT<stage_id INT64, stage_name STRING, duration_ms INT64, records_read INT64, records_written INT64>>
LANGUAGE SQL AS (
  SELECT ARRAY_AGG(
    STRUCT(
      stage_id,
      stage_name,
      duration_ms,
      records_read,
      records_written
    ) 
    ORDER BY duration_ms DESC
  )
  FROM `project.region-us.INFORMATION_SCHEMA.JOB_TIMELINE_BY_PROJECT`
  WHERE job_id = job_id_param
    AND stage_name IS NOT NULL
);

-- 사용 예시
SELECT 
  job_id,
  `project.optimization.analyze_query_stages`(job_id) as stage_analysis
FROM `project.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND total_slot_ms > 300000  -- 5분 이상 슬롯 사용
LIMIT 10;

-- 작은 테이블 브로드캐스트 조인 최적화
WITH small_lookup_table AS (
  -- 작은 참조 테이블 (< 1GB)
  SELECT 
    product_id,
    category,
    brand,
    unit_cost
  FROM `project.master.products`
  WHERE is_active = true
),
large_fact_table AS (
  -- 큰 팩트 테이블
  SELECT 
    order_id,
    product_id,
    quantity,
    sale_price,
    order_date
  FROM `project.sales.order_items`
  WHERE DATE(order_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
-- BigQuery가 자동으로 브로드캐스트 조인 선택
SELECT 
  l.category,
  l.brand,
  SUM(f.quantity * f.sale_price) as revenue,
  SUM(f.quantity * l.unit_cost) as cost,
  SUM(f.quantity * (f.sale_price - l.unit_cost)) as profit
FROM large_fact_table f
JOIN small_lookup_table l ON f.product_id = l.product_id  -- 브로드캐스트 조인
GROUP BY l.category, l.brand
ORDER BY revenue DESC;

-- 파티션 키 기반 조인 최적화
WITH orders_partitioned AS (
  SELECT 
    order_id,
    customer_id,
    order_amount,
    DATE(order_timestamp) as order_date
  FROM `project.sales.orders_partitioned`
  WHERE DATE(order_timestamp) = '2024-01-15'  -- 파티션 프루닝
),
customers_partitioned AS (
  SELECT 
    customer_id,
    customer_segment,
    registration_date
  FROM `project.customers.customers_partitioned`  
  WHERE DATE(created_at) <= '2024-01-15'  -- 파티션 프루닝
)
-- 동일 파티션 키로 조인하여 셔플 최소화
SELECT 
  c.customer_segment,
  COUNT(*) as order_count,
  SUM(o.order_amount) as total_revenue,
  AVG(o.order_amount) as avg_order_value
FROM orders_partitioned o
JOIN customers_partitioned c ON o.customer_id = c.customer_id
GROUP BY c.customer_segment;

-- 조인 순서 최적화를 통한 중간 결과 최소화
WITH filtered_orders AS (
  -- 가장 선택적인 필터 먼저 적용
  SELECT order_id, customer_id, product_id, order_amount
  FROM `project.sales.orders`
  WHERE order_amount > 500  -- 고가 주문만 (선택률 5%)
    AND DATE(order_date) >= '2024-01-01'
),
filtered_customers AS (
  -- 두 번째로 선택적인 필터
  SELECT customer_id, customer_tier
  FROM `project.customers.customer_master`
  WHERE customer_tier = 'PREMIUM'  -- 프리미엄 고객만 (선택률 10%)
),
filtered_products AS (
  -- 마지막 필터 (선택률이 높음)
  SELECT product_id, category, brand
  FROM `project.catalog.products`
  WHERE category IN ('Electronics', 'Jewelry')  -- 선택률 30%
)
-- 크기가 작은 테이블부터 조인 (orders -> customers -> products)
SELECT 
  fp.category,
  fp.brand,
  fc.customer_tier,
  COUNT(*) as order_count,
  SUM(fo.order_amount) as total_revenue
FROM filtered_orders fo  -- 가장 작은 중간 결과
JOIN filtered_customers fc ON fo.customer_id = fc.customer_id  -- 두 번째 작은 결과
JOIN filtered_products fp ON fo.product_id = fp.product_id    -- 가장 큰 결과
GROUP BY fp.category, fp.brand, fc.customer_tier;

-- 시간 + 지역 복합 파티셔닝
CREATE OR REPLACE TABLE `project.optimized.global_sales_partitioned` (
  order_id STRING,
  customer_id STRING, 
  region STRING,
  order_amount NUMERIC,
  order_timestamp TIMESTAMP,
  order_date DATE GENERATED ALWAYS AS (DATE(order_timestamp)) STORED
)
PARTITION BY order_date  -- 시간 기반 파티셔닝
CLUSTER BY region, customer_id  -- 지역별 클러스터링
OPTIONS (
  partition_expiration_days = 365,
  require_partition_filter = true
);

-- 최적화된 쿼리 패턴
SELECT 
  region,
  COUNT(*) as order_count,
  SUM(order_amount) as total_revenue
FROM `project.optimized.global_sales_partitioned`
WHERE order_date = '2024-01-15'  -- 파티션 필터 (필수)
  AND region = 'APAC'             -- 클러스터 필터 (선택적)
GROUP BY region;

-- 파티션 생성 자동화
CREATE OR REPLACE PROCEDURE `project.optimization.create_future_partitions`(
  table_name STRING,
  days_ahead INT64
)
BEGIN
  DECLARE partition_date DATE;
  DECLARE end_date DATE;
  
  SET partition_date = CURRENT_DATE();
  SET end_date = DATE_ADD(CURRENT_DATE(), INTERVAL days_ahead DAY);
  
  WHILE partition_date <= end_date DO
    -- 파티션 생성 (데이터 없는 빈 파티션)
    EXECUTE IMMEDIATE FORMAT("""
      CREATE TABLE IF NOT EXISTS `%s$%s`
      LIKE `%s`
    """, table_name, FORMAT_DATE('%Y%m%d', partition_date), table_name);
    
    SET partition_date = DATE_ADD(partition_date, INTERVAL 1 DAY);
  END WHILE;
END;

-- 오래된 파티션 자동 정리
CREATE OR REPLACE PROCEDURE `project.optimization.cleanup_old_partitions`(
  table_name STRING,
  retention_days INT64
)
BEGIN
  DECLARE cutoff_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL retention_days DAY);
  
  FOR partition IN (
    SELECT partition_id
    FROM `project.INFORMATION_SCHEMA.PARTITIONS`
    WHERE table_name = SPLIT(table_name, '.')[OFFSET(2)]
      AND table_schema = SPLIT(table_name, '.')[OFFSET(1)]
      AND partition_id != '__NULL__'
      AND SAFE_CAST(partition_id AS DATE) < cutoff_date
  ) DO
    EXECUTE IMMEDIATE FORMAT(
      "DROP TABLE `%s$%s`", 
      table_name, 
      partition.partition_id
    );
  END FOR;
END;

-- 컬럼 순서 최적화 (높은 카디널리티 -> 낮은 카디널리티)
CREATE OR REPLACE TABLE `project.optimized.orders_column_optimized` AS
SELECT 
  -- 고유값이 많은 컬럼을 먼저 (압축률 향상)
  order_id,           -- 매우 높은 카디널리티
  customer_id,        -- 높은 카디널리티  
  product_id,         -- 높은 카디널리티
  order_timestamp,    -- 높은 카디널리티
  
  -- 수치 데이터
  order_amount,       -- 중간 카디널리티
  quantity,           -- 낮은 카디널리티
  
  -- 범주형 데이터 (낮은 카디널리티)
  order_status,       -- 매우 낮은 카디널리티 (5-10개 값)
  region,             -- 낮은 카디널리티 (10-50개 값)
  channel             -- 매우 낮은 카디널리티 (3-5개 값)
FROM `project.raw.orders`;

-- 중첩 구조 최적화
CREATE OR REPLACE TABLE `project.optimized.denormalized_orders` AS
WITH order_details AS (
  SELECT 
    order_id,
    customer_id,
    order_timestamp,
    
    -- 주문 항목을 중첩 구조로 최적화
    ARRAY_AGG(
      STRUCT(
        product_id,
        product_name,
        quantity,
        unit_price,
        quantity * unit_price as line_total
      ) 
      ORDER BY line_total DESC  -- 큰 금액부터 정렬
    ) as order_items,
    
    -- 고객 정보 임베딩
    ANY_VALUE(STRUCT(
      customer_name,
      customer_tier,
      customer_region
    )) as customer_info,
    
    -- 집계된 주문 메트릭
    SUM(quantity * unit_price) as order_total,
    COUNT(*) as item_count,
    AVG(unit_price) as avg_item_price
    
  FROM `project.raw.order_items` oi
  JOIN `project.raw.customers` c ON oi.customer_id = c.customer_id
  GROUP BY order_id, customer_id, order_timestamp, customer_name, customer_tier, customer_region
)
SELECT *
FROM order_details;

-- 최적화된 쿼리
SELECT 
  customer_info.customer_tier,
  COUNT(*) as order_count,
  AVG(order_total) as avg_order_value,
  
  -- 중첩 데이터 집계
  AVG(ARRAY_LENGTH(order_items)) as avg_items_per_order,
  
  -- 상위 제품 분석
  APPROX_TOP_COUNT(
    (SELECT item.product_name 
     FROM UNNEST(order_items) as item 
     ORDER BY item.line_total DESC 
     LIMIT 1), 10
  ) as top_products
  
FROM `project.optimized.denormalized_orders`
WHERE DATE(order_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY customer_info.customer_tier;

-- 압축 효율을 위한 데이터 타입 최적화
CREATE OR REPLACE TABLE `project.optimized.type_optimized_table` (
  -- 정수형 최적화
  small_int INT64,      -- TINYINT, SMALLINT 대신 INT64 사용 (BigQuery 권장)
  
  -- 문자열 최적화  
  status_code STRING,   -- 짧은 코드는 STRING
  description STRING,   -- 긴 텍스트도 STRING (TEXT 타입 없음)
  
  -- 날짜/시간 최적화
  event_date DATE,      -- 날짜만 필요한 경우 DATE
  event_timestamp TIMESTAMP,  -- 정확한 시간이 필요한 경우 TIMESTAMP
  
  -- 수치 최적화
  price NUMERIC(10,2),  -- 정확한 소수점이 필요한 경우
  ratio FLOAT64,        -- 근사치가 허용되는 경우
  
  -- JSON 최적화
  metadata JSON,        -- 구조화되지 않은 데이터
  
  -- 배열 최적화
  tags ARRAY<STRING>,   -- 가변 길이 목록
  
  -- 구조체 최적화
  location STRUCT<
    latitude FLOAT64,
    longitude FLOAT64,
    address STRING
  >                     -- 관련 필드 그룹화
)
PARTITION BY event_date
CLUSTER BY status_code;

-- 사례: 10TB 규모의 이벤트 로그 일별 집계 최적화

-- ❌ 비최적화 버전 (3시간, 2000 슬롯)
/*
SELECT 
  DATE(event_timestamp) as event_date,
  user_id,
  event_type,
  COUNT(*) as event_count
FROM `project.logs.raw_events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY DATE(event_timestamp), user_id, event_type
ORDER BY event_date DESC, event_count DESC;
*/

-- ✅ 최적화 버전 (30분, 500 슬롯)
WITH optimized_events AS (
  SELECT 
    -- 파티션 키를 먼저 추출하여 프루닝 최적화
    _PARTITIONDATE as event_date,
    user_id,
    event_type,
    
    -- 미리 계산된 파티션 날짜 활용
    EXTRACT(HOUR FROM event_timestamp) as event_hour
    
  FROM `project.logs.partitioned_events`
  WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    -- 높은 선택성 필터 먼저
    AND user_id IS NOT NULL
    AND event_type IN ('page_view', 'click', 'purchase')  -- 주요 이벤트만
),
hourly_pre_aggregation AS (
  -- 시간별 사전 집계로 중간 결과 크기 감소
  SELECT 
    event_date,
    event_hour,
    user_id,
    event_type,
    COUNT(*) as hourly_count
  FROM optimized_events
  GROUP BY event_date, event_hour, user_id, event_type
)
-- 최종 일별 집계
SELECT 
  event_date,
  user_id,
  event_type,
  SUM(hourly_count) as daily_count
FROM hourly_pre_aggregation
GROUP BY event_date, user_id, event_type
ORDER BY event_date DESC, daily_count DESC;

-- 사례: 6개 테이블 조인 쿼리 최적화

-- ❌ 비최적화 버전
/*
SELECT o.*, c.*, p.*, cat.*, b.*, r.*
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id  
JOIN categories cat ON p.category_id = cat.category_id
JOIN brands b ON p.brand_id = b.brand_id
JOIN regions r ON c.region_id = r.region_id
WHERE o.order_date >= '2024-01-01';
*/

-- ✅ 최적화 버전
WITH filtered_orders AS (
  -- 1단계: 가장 선택적인 테이블부터 필터링
  SELECT 
    order_id, customer_id, product_id, order_amount, order_date
  FROM `project.sales.orders`
  WHERE order_date >= '2024-01-01'  -- 시간 필터
    AND order_amount > 50           -- 최소 주문 금액
),
enriched_with_customers AS (
  -- 2단계: 고객 정보 조인 (작은 테이블)
  SELECT 
    fo.*,
    c.customer_name,
    c.customer_tier,
    c.region_id
  FROM filtered_orders fo
  JOIN `project.customers.customer_master` c 
    ON fo.customer_id = c.customer_id
  WHERE c.is_active = true  -- 활성 고객만
),
enriched_with_products AS (
  -- 3단계: 제품 정보 조인
  SELECT 
    ewc.*,
    p.product_name,
    p.category_id,
    p.brand_id
  FROM enriched_with_customers ewc
  JOIN `project.catalog.products` p 
    ON ewc.product_id = p.product_id
  WHERE p.is_active = true  -- 활성 제품만
),
final_enrichment AS (
  -- 4단계: 나머지 참조 테이블 조인 (한번에)
  SELECT 
    ewp.*,
    cat.category_name,
    b.brand_name,
    r.region_name
  FROM enriched_with_products ewp
  LEFT JOIN `project.catalog.categories` cat ON ewp.category_id = cat.category_id
  LEFT JOIN `project.catalog.brands` b ON ewp.brand_id = b.brand_id  
  LEFT JOIN `project.geo.regions` r ON ewp.region_id = r.region_id
)
-- 5단계: 필요한 컬럼만 최종 선택
SELECT 
  order_id,
  customer_name,
  customer_tier,
  product_name,
  category_name,
  brand_name,
  region_name,
  order_amount,
  order_date
FROM final_enrichment
ORDER BY order_date DESC, order_amount DESC;

-- 사례: 실시간 대시보드용 집계 테이블 최적화

-- 사전 계산된 집계 테이블 생성 (매시간 업데이트)
CREATE OR REPLACE TABLE `project.analytics.hourly_kpi_summary`
PARTITION BY DATE(hour_timestamp)
CLUSTER BY region, product_category
AS
WITH hourly_base AS (
  SELECT 
    TIMESTAMP_TRUNC(order_timestamp, HOUR) as hour_timestamp,
    region,
    product_category,
    
    -- 핵심 KPI만 사전 계산
    COUNT(*) as order_count,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(order_amount) as total_revenue,
    AVG(order_amount) as avg_order_value,
    
    -- 백분위수 근사치 (정확도 vs 성능 트레이드오프)
    APPROX_QUANTILES(order_amount, 100)[OFFSET(50)] as median_order_value,
    APPROX_QUANTILES(order_amount, 100)[OFFSET(95)] as p95_order_value,
    
    -- 고객 세그먼트별 집계
    COUNTIF(customer_tier = 'VIP') as vip_orders,
    COUNTIF(customer_tier = 'Premium') as premium_orders,
    COUNTIF(customer_tier = 'Regular') as regular_orders
    
  FROM `project.sales.enriched_orders_view`
  WHERE order_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  GROUP BY 
    TIMESTAMP_TRUNC(order_timestamp, HOUR),
    region,
    product_category
)
SELECT 
  *,
  -- 추가 계산 메트릭
  total_revenue / NULLIF(order_count, 0) as revenue_per_order,
  unique_customers / NULLIF(order_count, 0) as customer_ratio,
  vip_orders / NULLIF(order_count, 0) * 100 as vip_percentage,
  
  -- 트렌드 계산 (이전 시간 대비)
  total_revenue - LAG(total_revenue) OVER (
    PARTITION BY region, product_category 
    ORDER BY hour_timestamp
  ) as revenue_change_hourly
  
FROM hourly_base;

-- 실시간 대시보드 쿼리 (초고속)
SELECT 
  region,
  SUM(total_revenue) as daily_revenue,
  SUM(order_count) as daily_orders,
  AVG(avg_order_value) as avg_order_size
FROM `project.analytics.hourly_kpi_summary`
WHERE DATE(hour_timestamp) = CURRENT_DATE()
GROUP BY region
ORDER BY daily_revenue DESC;