SQL

-- CREATE: 데이터 삽입
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

-- READ: 데이터 조회
SELECT id, name, email
FROM users
WHERE created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 10;

-- UPDATE: 데이터 수정
UPDATE users
SET email = 'alice.new@example.com', updated_at = NOW()
WHERE id = 1;

-- DELETE: 데이터 삭제
DELETE FROM users
WHERE id = 1;

-- 주문과 사용자 정보 조인
SELECT
    u.name AS user_name,
    o.order_id,
    o.total_amount,
    o.created_at AS order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;

-- 집계 함수와 GROUP BY
SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) >= 5
ORDER BY total_spent DESC;

-- 서브쿼리: 평균보다 많이 구매한 사용자
SELECT name, email
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    GROUP BY user_id
    HAVING SUM(total_amount) > (
        SELECT AVG(total_amount) FROM orders
    )
);

-- CTE (Common Table Expression): 가독성 향상
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_sales
)
SELECT
    month,
    revenue,
    ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_rate
FROM growth
WHERE prev_revenue IS NOT NULL;

-- 순위 함수
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

-- 누적 합계와 이동 평균
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7days
FROM daily_sales;

-- 인덱스 생성
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 복합 인덱스 (왼쪽 컬럼부터 사용됨)
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- EXPLAIN으로 실행 계획 확인
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;