정규화 이해

-- 현재 설계: orders는 customer_id만 저장 (정규화됨)
SELECT o.id, o.customer_id, c.name, c.email
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
LIMIT 5;

-- 한 고객의 주문이 몇 건인지 확인
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 5;

-- 현재 설계: 1NF 준수 - order_items로 분리
SELECT o.order_number, p.name, oi.quantity, oi.unit_price
FROM order_items AS oi
INNER JOIN orders AS o ON oi.order_id = o.id
INNER JOIN products AS p ON oi.product_id = p.id
WHERE o.id = 1;

-- 정규화된 설계에서 특정 상품 검색은 간단
SELECT COUNT(DISTINCT order_id) AS orders_with_product
FROM order_items
WHERE product_id = 1;

-- 현재 설계: order_items는 product_id만 저장 (2NF 준수)
SELECT oi.order_id, oi.product_id, p.name, p.brand
FROM order_items AS oi
INNER JOIN products AS p ON oi.product_id = p.id
LIMIT 5;

-- 한 상품이 몇 개의 주문에 포함되었는지 확인
SELECT product_id, COUNT(*) AS appearances
FROM order_items
GROUP BY product_id
ORDER BY appearances DESC
LIMIT 5;

-- 현재 설계: orders -> customers -> grade (3NF 준수)
SELECT o.order_number, c.name, c.grade, c.point_balance
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
LIMIT 5;

-- 한 고객의 주문 수 (등급 변경 시 이 모든 행이 영향)
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 10
LIMIT 5;

-- 상품의 현재 가격과 과거 주문 가격이 다를 수 있음
SELECT
    p.name,
    p.price AS current_price,
    oi.unit_price AS order_price,
    o.ordered_at
FROM order_items AS oi
INNER JOIN products AS p ON oi.product_id = p.id
INNER JOIN orders AS o ON oi.order_id = o.id
WHERE p.price <> oi.unit_price
LIMIT 10;

-- wishlists = 연결 테이블 (junction table)
SELECT
    c.name AS customer,
    p.name AS product,
    w.created_at
FROM wishlists AS w
INNER JOIN customers AS c ON w.customer_id = c.id
INNER JOIN products AS p ON w.product_id = p.id
LIMIT 10;

-- 연결 테이블 구조 확인
SELECT sql FROM sqlite_master WHERE name = 'wishlists';

-- parent_id로 자기 자신을 참조하는 계층 구조
SELECT
    child.name AS category,
    parent.name AS parent_category,
    child.depth
FROM categories AS child
LEFT JOIN categories AS parent ON child.parent_id = parent.id
ORDER BY child.depth, child.sort_order;

-- 재귀 CTE로 전체 경로 구성
WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, name AS path, 0 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id,
           tree.path || ' > ' || c.name, tree.depth + 1
    FROM categories AS c
    INNER JOIN tree ON c.parent_id = tree.id
)
SELECT path, depth FROM tree
ORDER BY path;

-- B가 정답 - 1NF 원칙 (반복 그룹 제거)
-- customer_addresses: 고객당 여러 배송지 (1:N)
SELECT customer_id, COUNT(*) AS address_count
FROM customer_addresses
GROUP BY customer_id
ORDER BY address_count DESC
LIMIT 5;