뷰와 CTE

CREATE VIEW order_summary AS
SELECT o.order_id, u.username, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, u.username, o.order_date, o.status;

-- 테이블처럼 SELECT
SELECT * FROM order_summary WHERE status = 'DELIVERED';

-- 다른 쿼리에서 조인 대상으로도 사용 가능
SELECT os.username, os.total_amount
FROM order_summary os
WHERE os.total_amount > 100000
ORDER BY os.total_amount DESC;

-- 뷰 수정 (CREATE OR REPLACE)
CREATE OR REPLACE VIEW order_summary AS
SELECT o.order_id, u.username, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS total_amount,
       COUNT(oi.item_id) AS item_count
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, u.username, o.order_date, o.status;

-- 뷰 삭제
DROP VIEW order_summary;

-- 조건부 삭제
DROP VIEW IF EXISTS order_summary;  -- MySQL, PostgreSQL

✓ 단일 테이블 기반 (JOIN 없음)
✓ 집계 함수 없음 (SUM, COUNT, AVG 등)
✓ GROUP BY 없음
✓ DISTINCT 없음
✓ UNION 없음
✓ 서브쿼리 없음 (FROM 절)
✗ 위 조건 중 하나라도 위반하면 갱신 불가

CREATE VIEW active_users AS
SELECT user_id, username, email, grade
FROM users
WHERE is_active = 1
WITH CHECK OPTION;

-- 이제 is_active = 0인 행은 이 뷰를 통해 입력/수정 불가
-- CHECK OPTION이 없으면 조건을 벗어나는 데이터도 삽입될 수 있음

-- LOCAL vs CASCADED (뷰 위에 뷰가 있을 때)
-- LOCAL: 현재 뷰의 조건만 검사
-- CASCADED (기본값): 기반 뷰의 조건도 함께 검사

-- 직원 테이블에서 급여 정보를 숨긴 뷰
CREATE VIEW employee_public AS
SELECT emp_id, name, department, position, hire_date
FROM employees;
-- 급여(salary), 주민번호(ssn) 등 민감 정보 제외

-- 부서별 뷰 (자기 부서만 조회 가능)
CREATE VIEW dept_10_employees AS
SELECT emp_id, name, position, salary
FROM employees
WHERE dept_id = 10;

-- 권한 부여
GRANT SELECT ON employee_public TO junior_role;
GRANT SELECT, UPDATE ON dept_10_employees TO dept_10_manager;
-- 기본 테이블에 대한 직접 접근은 차단

-- 생성
CREATE MATERIALIZED VIEW mv_monthly_sales
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    SUM(total_amount) AS monthly_total,
    COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

-- 수동 갱신
BEGIN
    DBMS_MVIEW.REFRESH('mv_monthly_sales');
END;

-- 삭제
DROP MATERIALIZED VIEW mv_monthly_sales;

-- 생성
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS monthly_total,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- 수동 갱신
REFRESH MATERIALIZED VIEW mv_monthly_sales;

-- 동시 조회 가능한 갱신 (UNIQUE INDEX 필요)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

WITH high_value_orders AS (
    SELECT o.order_id, o.user_id,
           SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.order_id, o.user_id
    HAVING SUM(oi.quantity * oi.unit_price) > 100000
)
SELECT u.username, h.total
FROM high_value_orders h
JOIN users u ON h.user_id = u.user_id;

-- 서브쿼리 방식 (가독성 낮음)
SELECT u.username, sub.total
FROM users u
JOIN (
    SELECT o.user_id, SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.user_id
    HAVING SUM(oi.quantity * oi.unit_price) > 100000
) sub ON u.user_id = sub.user_id;

-- CTE 방식 (가독성 높음)
WITH high_spenders AS (
    SELECT o.user_id, SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.user_id
    HAVING SUM(oi.quantity * oi.unit_price) > 100000
)
SELECT u.username, hs.total
FROM users u
JOIN high_spenders hs ON u.user_id = hs.user_id;

WITH
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders GROUP BY user_id
),
user_spending AS (
    SELECT o.user_id, SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.user_id
),
user_stats AS (
    -- 앞의 두 CTE를 합쳐서 사용
    SELECT uo.user_id, uo.order_count, us.total_spent,
           us.total_spent / NULLIF(uo.order_count, 0) AS avg_per_order
    FROM user_orders uo
    JOIN user_spending us ON uo.user_id = us.user_id
)
SELECT u.username, s.order_count, s.total_spent, s.avg_per_order
FROM users u
JOIN user_stats s ON u.user_id = s.user_id
ORDER BY s.total_spent DESC;

-- MySQL / PostgreSQL
WITH RECURSIVE org_tree AS (
    -- 기저 조건: 최상위 (CEO)
    SELECT emp_id, name, manager_id, 1 AS level_depth,
           CAST(name AS CHAR(500)) AS path
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 재귀: 부하 직원
    SELECT e.emp_id, e.name, e.manager_id, t.level_depth + 1,
           CONCAT(t.path, ' > ', e.name)
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT 
    CONCAT(REPEAT('  ', level_depth - 1), name) AS org_chart,
    level_depth,
    path
FROM org_tree
ORDER BY path;

-- 카테고리 테이블
-- id | name      | parent_id
-- 1  | 전자제품  | NULL
-- 2  | 컴퓨터    | 1
-- 3  | 노트북    | 2
-- 4  | 데스크탑  | 2
-- 5  | 의류      | NULL

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 
           0 AS depth,
           CAST(id AS CHAR(200)) AS sort_path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id,
           ct.depth + 1,
           CONCAT(ct.sort_path, '-', LPAD(c.id, 5, '0'))
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT CONCAT(REPEAT('  ', depth), name) AS tree
FROM category_tree
ORDER BY sort_path;

SELECT 
    LPAD(' ', (LEVEL-1)*2) || name AS org_chart,
    LEVEL,
    SYS_CONNECT_BY_PATH(name, '/') AS path
FROM employees
START WITH manager_id IS NULL        -- 시작점 (Anchor)
CONNECT BY PRIOR emp_id = manager_id -- 재귀 조건
ORDER SIBLINGS BY name;              -- 같은 레벨 내 정렬

-- MySQL: 최대 재귀 깊이 설정 (기본 1000)
SET SESSION cte_max_recursion_depth = 100;

-- PostgreSQL: CYCLE 감지 (SQL:2016 표준)
WITH RECURSIVE org AS (
    SELECT emp_id, name, manager_id
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.name, e.manager_id
    FROM employees e JOIN org o ON e.manager_id = o.emp_id
) CYCLE emp_id SET is_cycle USING path_array
SELECT * FROM org WHERE NOT is_cycle;

-- Oracle: CONNECT BY NOCYCLE
SELECT * FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

-- 인라인 뷰
SELECT a.dept_name, b.avg_salary
FROM departments a
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) b ON a.dept_id = b.dept_id
WHERE b.avg_salary > 5000000;

-- 동일한 로직을 CTE로
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT a.dept_name, b.avg_salary
FROM departments a
JOIN dept_avg b ON a.dept_id = b.dept_id
WHERE b.avg_salary > 5000000;

CREATE VIEW customer_masked AS
SELECT 
    customer_id,
    CONCAT(LEFT(name, 1), '**') AS name,
    CONCAT(LEFT(phone, 3), '-****-', RIGHT(phone, 4)) AS phone,
    CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email,
    grade, join_date
FROM customers;

-- 일반 직원에게는 마스킹된 뷰만 접근 허용
GRANT SELECT ON customer_masked TO staff_role;

-- 월별 매출 뷰 (자주 사용되므로 VIEW)
CREATE VIEW v_monthly_sales AS
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status != 'CANCELLED'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- 전월 대비 성장률 분석 (일회성이므로 CTE)
WITH current_month AS (
    SELECT * FROM v_monthly_sales WHERE month = '2024-01'
),
previous_month AS (
    SELECT * FROM v_monthly_sales WHERE month = '2023-12'
)
SELECT 
    c.month,
    c.revenue AS current_revenue,
    p.revenue AS prev_revenue,
    ROUND((c.revenue - p.revenue) / p.revenue * 100, 1) AS growth_rate
FROM current_month c
CROSS JOIN previous_month p;