윈도우 함수와 뷰

-- ROW_NUMBER: 연속 순번 (중복 없음)
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- RANK: 동순위 허용, 다음 순위 건너뜀 (1, 2, 2, 4)
SELECT
    name, department, salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;

-- DENSE_RANK: 동순위 허용, 다음 순위 연속 (1, 2, 2, 3)
SELECT
    name, department, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

-- PARTITION BY: 부서별 순위
SELECT
    name, department, salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_rank
FROM employees;

-- 부서별 TOP 3 추출
WITH ranked AS (
    SELECT
        name, department, salary,
        ROW_NUMBER() OVER (
            PARTITION BY department ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

-- LAG: 이전 행 값 참조 (전월 대비)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS diff,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / LAG(revenue, 1) OVER (ORDER BY month) * 100, 1
    ) AS growth_pct
FROM monthly_sales;

-- LEAD: 다음 행 값 참조
SELECT
    order_date,
    customer_id,
    LEAD(order_date, 1) OVER (
        PARTITION BY customer_id ORDER BY order_date
    ) AS next_order_date,
    DATEDIFF(
        LEAD(order_date, 1) OVER (
            PARTITION BY customer_id ORDER BY order_date
        ),
        order_date
    ) AS days_until_next
FROM orders;

-- SUM() OVER: 누적 합계
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM daily_sales;

-- COUNT, AVG OVER with PARTITION
SELECT
    name, department, salary,
    COUNT(*) OVER (PARTITION BY department) AS dept_size,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

-- 뷰 생성: 고객 주문 요약
CREATE VIEW customer_order_summary AS
SELECT
    u.id AS customer_id,
    u.name,
    u.email,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.ordered_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- 뷰 사용 (일반 테이블처럼)
SELECT * FROM customer_order_summary
WHERE total_spent > 100000
ORDER BY total_spent DESC;

-- 뷰 수정
CREATE OR REPLACE VIEW customer_order_summary AS
SELECT
    u.id AS customer_id,
    u.name,
    u.email,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.ordered_at) AS last_order_date,
    DATEDIFF(CURDATE(), MAX(o.ordered_at)) AS days_since_last
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- 갱신 가능한 단순 뷰
CREATE VIEW active_products AS
SELECT id, name, price, stock
FROM products
WHERE is_active = TRUE;

-- 뷰를 통한 UPDATE (단순 뷰는 가능)
UPDATE active_products
SET price = price * 0.9
WHERE stock > 100;

-- 뷰 삭제
DROP VIEW IF EXISTS customer_order_summary;