서브쿼리

-- 서브쿼리 없이: 2번의 쿼리 필요
-- 1단계: 평균 가격 구하기
SELECT AVG(price) FROM products;  -- 결과: 50000

-- 2단계: 평균보다 비싼 상품 조회
SELECT * FROM products WHERE price > 50000;

-- 서브쿼리 사용: 1번의 쿼리로 해결
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

* 비상관 서브쿼리 (Non-correlated)
  외부 쿼리와 독립적. 서브쿼리가 먼저 한 번 실행되고 결과가 외부에 전달됨
  예: WHERE price > (SELECT AVG(price) FROM products)

* 상관 서브쿼리 (Correlated)
  외부 쿼리의 값을 참조. 외부의 각 행마다 서브쿼리가 반복 실행됨
  예: WHERE price = (SELECT MAX(price) FROM products p2 WHERE p2.category = p.category)

SELECT o.order_id,
       o.order_date,
       o.total_amount,
       (SELECT u.username
        FROM users u
        WHERE u.user_id = o.user_id) AS customer_name
FROM orders o;

-- 전체 평균과 각 상품의 가격 차이
SELECT p.name,
       p.price,
       (SELECT AVG(price) FROM products) AS avg_price,
       p.price - (SELECT AVG(price) FROM products) AS diff
FROM products p;

-- 각 부서의 사원 수
SELECT d.dept_name,
       (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.dept_id) AS emp_count
FROM departments d;

-- 주문이 없는 고객의 경우 last_order_date는 NULL
SELECT u.username,
       (SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.user_id = u.user_id) AS last_order_date
FROM users u;

-- COALESCE로 NULL 대체
SELECT u.username,
       COALESCE(
           (SELECT MAX(o.order_date)
            FROM orders o
            WHERE o.user_id = u.user_id),
           '주문 없음'
       ) AS last_order_date
FROM users u;

-- 방법 1: 스칼라 서브쿼리
SELECT o.order_id,
       (SELECT u.username FROM users u WHERE u.user_id = o.user_id) AS name
FROM orders o;

-- 방법 2: LEFT JOIN (일반적으로 더 빠름)
SELECT o.order_id, u.username AS name
FROM orders o
LEFT JOIN users u ON u.user_id = o.user_id;

SELECT summary.category,
       summary.total_value,
       summary.product_count
FROM (
    SELECT category,
           SUM(price * stock) AS total_value,
           COUNT(*) AS product_count
    FROM products
    GROUP BY category
) summary
WHERE summary.total_value > 1000000
ORDER BY summary.total_value DESC;

-- 카테고리별 매출 상위 3개 카테고리
SELECT *
FROM (
    SELECT category,
           SUM(price * stock) AS total_value,
           ROW_NUMBER() OVER (ORDER BY SUM(price * stock) DESC) AS rn
    FROM products
    GROUP BY category
) ranked
WHERE ranked.rn <= 3;

SELECT *
FROM (
    SELECT category, avg_price
    FROM (
        SELECT category, AVG(price) AS avg_price
        FROM products
        GROUP BY category
    ) inner_view
    WHERE avg_price > 50000
) outer_view
ORDER BY avg_price DESC;

-- CTE로 개선 — 같은 결과, 더 읽기 쉬움
WITH category_avg AS (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
)
SELECT category, avg_price
FROM category_avg
WHERE avg_price > 50000
ORDER BY avg_price DESC;

-- 주문한 적이 있는 고객
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

-- 주문한 적이 없는 고객
SELECT * FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders);

-- 주문한 적이 있는 고객 (EXISTS)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

-- 동일한 결과 (IN)
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

-- ALL: 모든 도서 가격보다 비싼 상품 (= 도서 중 최고가보다 비쌈)
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = '도서');

-- 위와 동일 (MAX 사용)
SELECT * FROM products
WHERE price > (SELECT MAX(price) FROM products WHERE category = '도서');

-- ANY: 도서 중 하나라도보다 비싼 상품 (= 도서 중 최저가보다 비쌈)
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = '도서');

-- 위와 동일 (MIN 사용)
SELECT * FROM products
WHERE price > (SELECT MIN(price) FROM products WHERE category = '도서');

-- 전체 평균 주문 수보다 많이 주문한 고객
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > (
    SELECT AVG(cnt) FROM (
        SELECT COUNT(*) AS cnt FROM orders GROUP BY user_id
    ) t
);

SELECT p.name, p.price, p.category
FROM products p
WHERE p.price = (
    SELECT MAX(p2.price) FROM products p2
    WHERE p2.category = p.category   -- 외부의 p.category 참조
);

-- 원본: 상관 서브쿼리 (느릴 수 있음)
SELECT p.name, p.price, p.category
FROM products p
WHERE p.price = (
    SELECT MAX(p2.price) FROM products p2
    WHERE p2.category = p.category
);

-- 변환 1: JOIN 사용
SELECT p.name, p.price, p.category
FROM products p
INNER JOIN (
    SELECT category, MAX(price) AS max_price
    FROM products
    GROUP BY category
) m ON p.category = m.category AND p.price = m.max_price;

-- 변환 2: 윈도우 함수 사용 (가장 효율적)
SELECT name, price, category
FROM (
    SELECT name, price, category,
           RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rk
    FROM products
) ranked
WHERE rk = 1;

-- 각 카테고리에서 최고가인 상품 (다중 열 비교)
SELECT * FROM products
WHERE (category, price) IN (
    SELECT category, MAX(price) FROM products GROUP BY category
);

-- Oracle에서 다중 열 비교
SELECT * FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary) FROM employees GROUP BY department_id
);

SELECT p.name, p.price
FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

SELECT category,
       COUNT(*) AS cnt,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products), 1) AS pct
FROM products
GROUP BY category
ORDER BY pct DESC;

SELECT o.order_id,
       o.order_date,
       o.order_date - (
           SELECT MAX(o2.order_date)
           FROM orders o2
           WHERE o2.user_id = o.user_id
             AND o2.order_date < o.order_date
       ) AS days_since_last
FROM orders o
ORDER BY o.user_id, o.order_date;

-- 위 쿼리는 LAG 윈도우 함수로 더 효율적으로 작성 가능
SELECT order_id,
       order_date,
       order_date - LAG(order_date) OVER (
           PARTITION BY user_id ORDER BY order_date
       ) AS days_since_last
FROM orders
ORDER BY user_id, order_date;

-- 3개월간 주문이 없는 고객을 휴면 처리
UPDATE users
SET status = '휴면'
WHERE user_id NOT IN (
    SELECT DISTINCT user_id FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '3 months'
);

-- DELETE에서도 사용 가능
DELETE FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id FROM order_items
);

-- 월별 매출 요약을 집계 테이블에 삽입
INSERT INTO monthly_sales (month, total_amount, order_count)
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total_amount),
       COUNT(*)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date);

-- 방법 1: 서브쿼리 (IN)
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders WHERE total_amount > 100000);

-- 방법 2: 조인
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.total_amount > 100000;

-- 방법 3: EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.user_id AND o.total_amount > 100000
);

-- 오류! 서브쿼리가 여러 행을 반환
SELECT (SELECT username FROM users) FROM orders;
-- ERROR: more than one row returned by a subquery

-- 수정: 조건을 추가하여 1행만 반환
SELECT (SELECT username FROM users WHERE user_id = o.user_id)
FROM orders o;

-- 오류! IN 서브쿼리는 단일 열만 반환해야 함
SELECT * FROM users
WHERE user_id IN (SELECT user_id, username FROM orders);

-- 수정: 비교할 열만 반환
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

-- 불필요: 서브쿼리 안에 서브쿼리
SELECT * FROM products
WHERE category IN (
    SELECT category FROM (
        SELECT DISTINCT category FROM products
    ) t
);

-- 간결하게: 서브쿼리 없이
SELECT DISTINCT category FROM products;