빅쿼리 뷰

-- 복잡한 기본 쿼리
SELECT 
  c.customer_id,
  c.customer_name,
  c.region,
  COUNT(o.order_id) as total_orders,
  SUM(o.total_amount) as total_spent,
  AVG(o.total_amount) as avg_order_value,
  MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
  AND o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY c.customer_id, c.customer_name, c.region;

-- 뷰로 단순화
CREATE VIEW customer_summary AS
SELECT 
  c.customer_id,
  c.customer_name,
  c.region,
  COUNT(o.order_id) as total_orders,
  SUM(o.total_amount) as total_spent,
  AVG(o.total_amount) as avg_order_value,
  MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
  AND o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY c.customer_id, c.customer_name, c.region;

-- 간단한 사용
SELECT * FROM customer_summary WHERE region = 'Asia';

-- 기본 뷰 생성
CREATE VIEW sales_view AS
SELECT 
  product_id,
  product_name,
  category,
  SUM(quantity) as total_quantity,
  SUM(revenue) as total_revenue
FROM sales_table
WHERE sale_date >= '2023-01-01'
GROUP BY product_id, product_name, category;

-- 승인된 뷰 생성 (특별한 권한 부여)
CREATE VIEW secure_customer_data AS
SELECT 
  customer_id,
  SUBSTR(customer_name, 1, 1) || '***' as masked_name,
  region,
  total_orders
FROM customer_summary
WHERE region IN ('Korea', 'Japan');

-- 파티션된 테이블 기반 뷰
CREATE VIEW recent_events AS
SELECT 
  event_id,
  event_type,
  user_id,
  event_timestamp
FROM events_partitioned
WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);

-- 기본 뷰 생성 구문
CREATE VIEW [project_id.]dataset_id.view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 실제 예시
CREATE VIEW analytics.monthly_sales AS
SELECT 
  EXTRACT(YEAR FROM order_date) as year,
  EXTRACT(MONTH FROM order_date) as month,
  COUNT(*) as order_count,
  SUM(total_amount) as total_revenue,
  AVG(total_amount) as avg_order_value
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

-- 뷰 수정 (OR REPLACE 사용)
CREATE OR REPLACE VIEW analytics.monthly_sales AS
SELECT 
  EXTRACT(YEAR FROM order_date) as year,
  EXTRACT(MONTH FROM order_date) as month,
  region,  -- 새로운 컬럼 추가
  COUNT(*) as order_count,
  SUM(total_amount) as total_revenue,
  AVG(total_amount) as avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id  -- JOIN 추가
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

-- 뷰 삭제
DROP VIEW analytics.monthly_sales;

-- 존재하는 경우에만 삭제
DROP VIEW IF EXISTS analytics.monthly_sales;

-- 데이터셋 내 모든 뷰 조회
SELECT 
  table_name,
  table_type,
  creation_time,
  last_modified_time
FROM analytics.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'VIEW';

-- 뷰 정의 조회
SELECT view_definition
FROM analytics.INFORMATION_SCHEMA.VIEWS
WHERE table_name = 'monthly_sales';

-- 개인정보 마스킹 뷰
CREATE VIEW secure_user_info AS
SELECT 
  user_id,
  CONCAT(
    SUBSTR(email, 1, 2),
    '***@',
    SUBSTR(email, STRPOS(email, '@') + 1, LENGTH(email))
  ) as masked_email,
  SUBSTR(phone, 1, 3) || '-****-' || SUBSTR(phone, -4) as masked_phone,
  registration_date
FROM users;

-- 지역별 데이터 접근 제한
CREATE VIEW regional_sales AS
SELECT 
  order_id,
  customer_id,
  product_id,
  total_amount,
  order_date
FROM orders
WHERE region = 
  CASE 
    WHEN SESSION_USER() LIKE '%asia%' THEN 'Asia'
    WHEN SESSION_USER() LIKE '%europe%' THEN 'Europe'
    ELSE 'Americas'
  END;

-- 1. 기본 테이블에 대한 뷰 생성
CREATE VIEW finance_summary AS
SELECT 
  department,
  SUM(budget) as total_budget,
  AVG(expense_ratio) as avg_expense_ratio
FROM confidential_finance_data
GROUP BY department;

-- 2. 특정 사용자/그룹에게만 뷰 접근 권한 부여
GRANT SELECT ON finance_summary TO 'user:analyst@company.com';

-- 3. 기본 테이블 접근은 제한하고 뷰 접근만 허용
-- (Google Cloud Console 또는 bq 명령어로 설정)

-- ❌ 비효율적인 뷰 (매번 전체 테이블 스캔)
CREATE VIEW inefficient_view AS
SELECT *
FROM large_table
WHERE some_condition = 'value';

-- ✅ 효율적인 뷰 (필요한 컬럼만 선택, 파티션 활용)
CREATE VIEW efficient_view AS
SELECT 
  id,
  name,
  created_date,
  status
FROM large_table
WHERE DATE(created_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND status IN ('active', 'pending');

-- 파티션된 테이블의 뷰에서 파티션 필터 포함
CREATE VIEW recent_logs AS
SELECT 
  log_id,
  user_id,
  action_type,
  log_timestamp
FROM partitioned_logs
WHERE DATE(log_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);

-- 클러스터링된 테이블의 뷰 (클러스터링 컬럼 활용)
CREATE VIEW user_activities AS
SELECT 
  user_id,  -- 클러스터링 컬럼
  activity_type,
  activity_timestamp,
  details
FROM clustered_activity_table
WHERE user_id IS NOT NULL;

-- 일반 뷰: 실시간 데이터가 중요한 경우
CREATE VIEW real_time_inventory AS
SELECT 
  product_id,
  available_quantity,
  reserved_quantity,
  available_quantity - reserved_quantity as sellable_quantity
FROM inventory
WHERE available_quantity > 0;

-- 머터리얼라이즈드 뷰: 복잡한 집계, 성능이 중요한 경우
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
  DATE(order_timestamp) as order_date,
  product_category,
  COUNT(*) as order_count,
  SUM(total_amount) as revenue,
  COUNT(DISTINCT customer_id) as unique_customers
FROM orders
WHERE DATE(order_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY 1, 2;

-- KPI 대시보드용 뷰
CREATE VIEW kpi_dashboard AS
SELECT 
  'Daily Active Users' as metric_name,
  COUNT(DISTINCT user_id) as metric_value,
  CURRENT_DATE() as metric_date
FROM user_activities
WHERE DATE(activity_timestamp) = CURRENT_DATE()

UNION ALL

SELECT 
  'Daily Revenue' as metric_name,
  COALESCE(SUM(total_amount), 0) as metric_value,
  CURRENT_DATE() as metric_date
FROM orders
WHERE DATE(order_timestamp) = CURRENT_DATE()

UNION ALL

SELECT 
  'New Registrations' as metric_name,
  COUNT(*) as metric_value,
  CURRENT_DATE() as metric_date
FROM users
WHERE DATE(created_timestamp) = CURRENT_DATE();

-- 데이터 품질 체크 뷰
CREATE VIEW data_quality_checks AS
SELECT 
  'orders' as table_name,
  'null_customer_id' as check_name,
  COUNT(*) as failed_records,
  CURRENT_TIMESTAMP() as check_timestamp
FROM orders
WHERE customer_id IS NULL

UNION ALL

SELECT 
  'orders' as table_name,
  'negative_amount' as check_name,
  COUNT(*) as failed_records,
  CURRENT_TIMESTAMP() as check_timestamp
FROM orders
WHERE total_amount < 0

UNION ALL

SELECT 
  'users' as table_name,
  'invalid_email' as check_name,
  COUNT(*) as failed_records,
  CURRENT_TIMESTAMP() as check_timestamp
FROM users
WHERE email NOT LIKE '%@%.%';

-- 프로덕션과 스테이징 환경 통합 뷰
CREATE VIEW unified_user_metrics AS
SELECT 
  'production' as environment,
  COUNT(*) as total_users,
  COUNT(CASE WHEN status = 'active' THEN 1 END) as active_users,
  AVG(session_duration) as avg_session_duration
FROM prod_dataset.users

UNION ALL

SELECT 
  'staging' as environment,
  COUNT(*) as total_users,
  COUNT(CASE WHEN status = 'active' THEN 1 END) as active_users,
  AVG(session_duration) as avg_session_duration
FROM staging_dataset.users;

-- ✅ 명확한 명명 규칙 사용
CREATE VIEW vw_monthly_sales_summary AS ...;     -- vw_ 접두사
CREATE VIEW sales_summary_monthly AS ...;        -- 의미 있는 이름
CREATE VIEW dim_customer_active AS ...;          -- 차원 테이블 표시

-- ✅ 뷰 목적과 사용법 주석 포함
CREATE VIEW customer_lifetime_value AS
/*
목적: 고객별 생애 가치(LTV) 계산
사용: 마케팅 타겟팅 및 세그먼테이션
업데이트: 매일 자동 새로고침
작성자: data-team@company.com
*/
SELECT 
  customer_id,
  SUM(order_total) as total_spent,
  COUNT(DISTINCT order_id) as total_orders,
  DATE_DIFF(MAX(order_date), MIN(order_date), DAY) as customer_lifetime_days
FROM orders
GROUP BY customer_id;

-- ✅ 필요한 컬럼만 선택
CREATE VIEW efficient_sales_view AS
SELECT 
  order_id,        -- 필요한 컬럼만
  customer_id,     -- 선택
  total_amount,
  order_date
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)  -- 적절한 필터링
  AND status = 'completed';

-- ❌ 피해야 할 패턴 - 복잡한 중첩 뷰
CREATE VIEW complex_nested_view AS
SELECT *
FROM (
  SELECT * FROM view1
  JOIN (
    SELECT * FROM view2
    JOIN view3 ON view2.id = view3.id
  ) ON view1.id = view2.id
);

-- ✅ 대안 - 단순한 구조 유지
CREATE VIEW simple_joined_view AS
SELECT 
  t1.id,
  t1.name,
  t2.category,
  t3.status
FROM base_table1 t1
JOIN base_table2 t2 ON t1.id = t2.id
JOIN base_table3 t3 ON t1.id = t3.id;

-- ✅ 적절한 권한 부여
-- 뷰에 대한 SELECT 권한만 부여
GRANT SELECT ON dataset.customer_summary_view TO 'group:analysts@company.com';

-- 기본 테이블에 대한 직접 접근은 제한
-- REVOKE ALL ON dataset.raw_customer_data FROM 'group:analysts@company.com';

-- 뷰 사용량 모니터링 쿼리
SELECT 
  view_name,
  query_count,
  avg_execution_time_ms,
  total_bytes_processed
FROM (
  SELECT 
    referenced_table.table_id as view_name,
    COUNT(*) as query_count,
    AVG(total_slot_ms) as avg_execution_time_ms,
    SUM(total_bytes_processed) as total_bytes_processed
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    AND referenced_table.table_id IN (
      SELECT table_name 
      FROM your_dataset.INFORMATION_SCHEMA.VIEWS
    )
  GROUP BY referenced_table.table_id
)
ORDER BY query_count DESC;