SQL 실전 종합 프로젝트 완벽 가이드

전자상거래 시스템을 직접 구축하면서 배우는 SQL 실전 프로젝트입니다. DB 설계부터 성능 최적화까지, 실무에서 필요한 모든 SQL 기술을 단계별로 마스터할 수 있습니다. 초급 개발자도 따라하기 쉬운 친절한 가이드로 구성되어 있습니다.

---

목차

1. 전자상거래_DB_설계
2. 회원_상품_주문_테이블_구축
3. 베스트셀러_상품_분석_쿼리
4. 고객별_구매_패턴_분석
5. 재고_관리_쿼리_작성
6. 성능_최적화_및_인덱스_설계

---

1. 전자상거래_DB_설계

시작하며

여러분이 쇼핑몰 웹사이트를 만든다고 상상해보세요. 회원 정보는 어디에 저장하죠?

상품 정보는요? 주문 내역은 어떻게 관리할까요?

막상 시작하려니 어디서부터 손을 대야 할지 막막하셨던 적 있으실 거예요. 이런 문제는 실제 개발 현장에서 가장 먼저 부딪히는 벽입니다.

데이터베이스 설계가 잘못되면 나중에 수정하기가 정말 어렵고, 성능 문제도 생기고, 데이터 중복이나 불일치 같은 골치 아픈 문제들이 계속 발생합니다. 바로 이럴 때 필요한 것이 체계적인 DB 설계입니다.

마치 집을 짓기 전에 설계도를 그리듯이, 데이터베이스도 미리 잘 설계해두면 나중에 편하게 개발할 수 있습니다.

개요

간단히 말해서, 전자상거래 DB 설계는 온라인 쇼핑몰에서 필요한 모든 데이터를 체계적으로 정리하고 저장하는 청사진을 만드는 것입니다. 왜 이게 필요한지 실무 관점에서 보면, 고객이 주문을 할 때 "이 주문은 누가 했지?", "어떤 상품을 샀지?", "배송지는 어디지?" 같은 질문에 빠르고 정확하게 답할 수 있어야 합니다.

잘 설계된 데이터베이스는 이런 모든 질문에 즉시 답할 수 있게 해줍니다. 기존에는 엑셀이나 메모장에 데이터를 저장했다면, 이제는 관계형 데이터베이스를 사용하여 데이터 간의 관계를 명확히 정의하고 효율적으로 관리할 수 있습니다.

핵심은 엔티티(테이블) 식별, 관계 정의, 정규화 이 세 가지입니다. 엔티티는 '회원', '상품', '주문' 같은 저장할 대상을 말하고, 관계는 이들이 어떻게 연결되는지를 정의하며, 정규화는 데이터 중복을 없애는 과정입니다.

이 세 가지를 잘 조합하면 깔끔하고 효율적인 데이터베이스를 만들 수 있습니다.

코드 예제

-- 전자상거래 데이터베이스 생성 및 기본 구조 설계
CREATE DATABASE ecommerce_db;
USE ecommerce_db;

-- 데이터베이스 설계의 핵심: ERD(Entity Relationship Diagram)를 코드로 표현
-- 주요 엔티티: 회원(users), 상품(products), 카테고리(categories), 주문(orders)

-- 카테고리 테이블: 상품을 분류하는 기준
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL,
    parent_category_id INT NULL,  -- 대분류/중분류/소분류 구조를 위한 자기참조
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

설명

이것이 하는 일: 전자상거래 시스템의 기반이 되는 데이터베이스를 생성하고, 가장 기본이 되는 카테고리 테이블 구조를 정의합니다. 첫 번째로, CREATE DATABASE ecommerce_db는 우리가 사용할 데이터베이스 전체 공간을 만듭니다.

마치 새 집을 짓기 위해 땅을 확보하는 것과 같아요. USE ecommerce_db는 이제부터 이 데이터베이스 안에서 작업하겠다고 선언하는 것입니다.

그 다음으로, 카테고리 테이블을 만드는 부분이 실행됩니다. category_id는 각 카테고리를 구분하는 고유번호이고, AUTO_INCREMENT를 사용하면 자동으로 1, 2, 3...

이렇게 번호가 증가합니다. category_name은 '전자제품', '의류' 같은 카테고리 이름을 저장하죠.

가장 중요한 건 parent_category_id인데, 이건 '대분류 > 중분류 > 소분류' 같은 계층 구조를 만들 수 있게 해줍니다. 마지막으로, FOREIGN KEY 제약조건이 데이터의 무결성을 보장합니다.

예를 들어, 존재하지 않는 상위 카테고리를 참조하려고 하면 데이터베이스가 자동으로 막아줍니다. 이렇게 하면 데이터가 꼬이는 걸 방지할 수 있어요.

여러분이 이 코드를 사용하면 체계적인 상품 분류 시스템을 구축할 수 있고, 나중에 '전자제품' 카테고리 안에 '스마트폰', '노트북' 같은 하위 카테고리를 쉽게 추가할 수 있습니다. 또한 카테고리별 상품 조회, 통계 분석 등도 간편하게 할 수 있게 됩니다.

실전 팁

💡 ERD 도구(MySQL Workbench, dbdiagram.io 등)를 사용하면 시각적으로 테이블 관계를 설계할 수 있어서 훨씬 이해하기 쉽습니다. 코드로 바로 작성하기 전에 ERD를 먼저 그려보세요.

💡 테이블 이름은 복수형(users, products)보다 단수형(user, product)을 사용하는 것이 일반적이지만, 팀 컨벤션에 따라 통일하는 게 가장 중요합니다. 일관성이 핵심입니다.

💡 초기 설계 단계에서는 정규화를 3NF(Third Normal Form)까지만 적용하세요. 과도한 정규화는 오히려 조인이 많아져서 성능이 떨어질 수 있습니다.

💡 VARCHAR 길이는 넉넉하게 설정하되, TEXT 타입은 인덱싱이 어려우므로 꼭 필요한 경우에만 사용하세요. 상품 설명 같은 긴 텍스트에만 TEXT를 쓰는 게 좋습니다.

💡 설계 단계에서 샘플 데이터를 넣어보고 실제로 쿼리를 돌려보세요. 설계만 하고 넘어가면 나중에 문제를 발견하기 어렵습니다.

---

2. 회원_상품_주문_테이블_구축

시작하며

여러분이 쇼핑몰을 운영한다고 생각해보세요. 고객이 회원가입을 하고, 상품을 둘러보고, 장바구니에 담고, 결제를 하는 모든 과정에서 데이터가 생성됩니다.

이 데이터들을 어떻게 저장하고 관리해야 할까요? 이런 핵심 테이블들이 제대로 구축되지 않으면 주문 정보가 누락되거나, 회원 정보와 주문이 연결되지 않거나, 상품 재고가 맞지 않는 심각한 문제가 발생합니다.

실제로 많은 초기 스타트업들이 이 부분을 대충 만들었다가 나중에 전체를 다시 만드는 경우가 많습니다. 바로 이럴 때 필요한 것이 회원, 상품, 주문 테이블의 체계적인 구축입니다.

이 세 테이블은 전자상거래의 핵심 삼각형이라고 할 수 있어요. 이것만 잘 만들어도 기본적인 쇼핑몰은 충분히 운영할 수 있습니다.

개요

간단히 말해서, 회원/상품/주문 테이블은 전자상거래의 핵심 데이터를 저장하는 세 개의 주요 저장소입니다. 왜 이게 필요한지 보면, 회원 테이블은 '누가' 주문했는지, 상품 테이블은 '무엇을' 샀는지, 주문 테이블은 '언제, 얼마에' 샀는지를 기록합니다.

예를 들어, 고객센터에서 "홍길동님이 어제 주문한 노트북 배송 어떻게 되나요?"라는 질문에 답하려면 이 세 테이블이 모두 필요합니다. 기존에는 각 정보를 따로따로 관리했다면, 이제는 외래키(Foreign Key)로 연결하여 하나의 통합된 시스템으로 관리할 수 있습니다.

한 테이블만 조회해도 관련된 모든 정보를 가져올 수 있죠. 핵심 특징은 첫째, 데이터 무결성 보장(잘못된 데이터 입력 차단), 둘째, 효율적인 데이터 조회(JOIN 사용), 셋째, 확장 가능한 구조(나중에 배송, 리뷰 테이블 추가 용이)입니다.

이러한 특징들이 안정적이고 확장 가능한 서비스를 만드는 데 필수적입니다.

코드 예제

-- 회원 테이블: 고객 정보 관리
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,  -- 이메일은 중복 불가
    password_hash VARCHAR(255) NOT NULL,  -- 비밀번호는 해시화하여 저장
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 상품 테이블: 판매 상품 정보
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,  -- 10자리 숫자, 소수점 2자리
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    INDEX idx_category (category_id),  -- 카테고리별 조회 성능 향상
    INDEX idx_price (price)  -- 가격 정렬 성능 향상
);

-- 주문 테이블: 주문 정보 관리
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user_date (user_id, order_date)  -- 회원별 주문 내역 조회용
);

-- 주문 상세 테이블: 주문한 상품 목록 (다대다 관계 해소)
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,  -- 주문 당시 가격 저장 (가격 변동 대비)
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_order (order_id)
);

설명

이것이 하는 일: 전자상거래 시스템의 핵심이 되는 네 개의 테이블(회원, 상품, 주문, 주문상세)을 생성하고, 이들을 외래키로 연결하여 통합 시스템을 구축합니다. 첫 번째로, users 테이블은 회원 정보를 저장합니다.

email에 UNIQUE 제약조건을 걸어서 중복 가입을 방지하고, password_hash는 평문이 아닌 암호화된 비밀번호를 저장합니다(보안 필수!). created_at과 updated_at은 자동으로 시간을 기록해서 언제 가입했고 언제 정보를 수정했는지 추적할 수 있어요.

그 다음으로, products 테이블이 상품 정보를 관리합니다. DECIMAL(10, 2)는 가격을 저장하는데, 소수점 연산 오류를 방지하기 위해 FLOAT 대신 DECIMAL을 사용하는 게 중요합니다.

돈 계산에서 0.1원이라도 틀리면 큰 문제가 될 수 있거든요. INDEX를 category_id와 price에 걸어서 "의류 카테고리 상품을 가격순으로 보여줘" 같은 쿼리가 빠르게 실행됩니다.

세 번째로, orders 테이블은 주문 정보를 저장하는데, ENUM 타입으로 주문 상태를 관리합니다. 이렇게 하면 'paid'나 'shipped' 같은 정해진 값만 입력할 수 있어서 오타나 잘못된 상태값을 방지할 수 있어요.

FOREIGN KEY (user_id)로 users 테이블과 연결하여 "이 주문은 누가 했는가?"를 명확히 합니다. 마지막으로, order_items 테이블이 실제로 어떤 상품을 몇 개 주문했는지 저장합니다.

이건 주문과 상품의 다대다 관계를 풀어주는 중간 테이블이에요. 한 주문에 여러 상품이 들어갈 수 있고, 한 상품도 여러 주문에 포함될 수 있으니까요.

unit_price를 별도로 저장하는 이유는 나중에 상품 가격이 바뀌어도 주문 당시 가격을 알 수 있도록 하기 위함입니다. 여러분이 이 테이블들을 사용하면 회원별 구매 내역 조회, 베스트셀러 상품 분석, 매출 통계 같은 다양한 비즈니스 로직을 쉽게 구현할 수 있습니다.

또한 나중에 배송 테이블, 리뷰 테이블을 추가할 때도 이 구조를 기반으로 쉽게 확장할 수 있어요.

실전 팁

💡 비밀번호는 절대 평문으로 저장하지 마세요. bcrypt, scrypt 같은 해시 함수를 사용하고, 애플리케이션 레벨에서 처리하는 게 안전합니다.

💡 ENUM 타입은 편리하지만, 값을 추가/변경할 때 테이블 구조를 변경해야 합니다. 상태값이 자주 바뀐다면 별도의 상태 테이블을 만드는 것도 고려해보세요.

💡 order_items 테이블에 unit_price를 저장하는 건 매우 중요합니다. products 테이블의 price만 참조하면 과거 주문의 정확한 금액을 알 수 없게 됩니다.

💡 외래키 제약조건은 데이터 무결성을 보장하지만, 삭제 작업이 복잡해질 수 있습니다. ON DELETE CASCADE나 ON DELETE SET NULL 옵션을 상황에 맞게 설정하세요.

💡 created_at, updated_at 같은 타임스탬프 컬럼은 모든 테이블에 기본으로 추가하세요. 디버깅할 때나 데이터 분석할 때 정말 유용합니다.

---

3. 베스트셀러_상품_분석_쿼리

시작하며

여러분이 쇼핑몰 운영자라고 생각해보세요. "우리 쇼핑몰에서 가장 많이 팔린 상품이 뭐지?", "이번 달 매출 1위 상품은?", "어떤 상품을 더 많이 홍보해야 할까?" 이런 질문들에 답하려면 어떻게 해야 할까요?

이런 분석을 수작업으로 하면 시간도 오래 걸리고 정확하지도 않습니다. 특히 상품이 수천, 수만 개가 되면 엑셀로는 감당이 안 됩니다.

데이터 기반 의사결정이 중요한 요즘, SQL로 빠르고 정확하게 분석할 수 있는 능력이 필수입니다. 바로 이럴 때 필요한 것이 베스트셀러 상품 분석 쿼리입니다.

복잡해 보이는 데이터도 몇 줄의 SQL로 의미 있는 정보로 바꿀 수 있습니다.

개요

간단히 말해서, 베스트셀러 분석 쿼리는 주문 데이터를 집계하여 가장 많이 팔린 상품을 찾아내는 SQL 명령문입니다. 왜 이게 필요한지 보면, 마케팅팀은 어떤 상품을 메인 페이지에 노출할지 결정해야 하고, 재고팀은 어떤 상품을 더 많이 확보해야 할지 알아야 하며, 경영진은 매출 현황을 실시간으로 파악해야 합니다.

예를 들어, "이번 달 TOP 10 상품을 찾아서 할인 이벤트를 기획하자" 같은 실무 시나리오에서 매우 유용합니다. 기존에는 엑셀로 주문 데이터를 다운받아서 피벗 테이블로 분석했다면, 이제는 SQL 쿼리 하나로 실시간 데이터를 즉시 분석할 수 있습니다.

심지어 자동화도 가능하죠. 핵심은 JOIN(테이블 결합), GROUP BY(그룹화), COUNT/SUM(집계 함수), ORDER BY(정렬) 이 네 가지입니다.

이들을 조합하면 주문 데이터에서 상품별 판매량, 매출액을 계산하고 순위를 매길 수 있어요. 이런 분석이 비즈니스 인사이트를 만들어냅니다.

코드 예제

-- 베스트셀러 TOP 10 상품 조회 (판매량 기준)
SELECT
    p.product_id,
    p.product_name,
    c.category_name,
    COUNT(oi.order_item_id) AS total_orders,  -- 총 주문 건수
    SUM(oi.quantity) AS total_quantity,  -- 총 판매 수량
    SUM(oi.quantity * oi.unit_price) AS total_revenue,  -- 총 매출액
    AVG(oi.unit_price) AS avg_price  -- 평균 판매가
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN categories c ON p.category_id = c.category_id
WHERE o.status IN ('paid', 'shipped', 'delivered')  -- 결제 완료된 주문만
    AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)  -- 최근 30일
GROUP BY p.product_id, p.product_name, c.category_name
HAVING total_quantity > 0  -- 판매량이 있는 상품만
ORDER BY total_quantity DESC  -- 판매량 많은 순
LIMIT 10;  -- 상위 10개만

설명

이것이 하는 일: 최근 30일간 결제 완료된 주문 데이터를 분석하여 판매량이 가장 많은 상위 10개 상품을 찾고, 각 상품의 주문 건수, 판매 수량, 총 매출액, 평균 가격을 함께 보여줍니다. 첫 번째로, SELECT 절에서 우리가 보고 싶은 정보들을 정의합니다.

COUNT(oi.order_item_id)는 이 상품이 몇 번 주문되었는지 세고, SUM(oi.quantity)는 총 몇 개가 팔렸는지 합산하며, SUM(oi.quantity * oi.unit_price)는 판매 수량 곱하기 가격으로 총 매출을 계산합니다. AS로 별칭을 붙여서 결과를 읽기 쉽게 만드는 것도 중요해요.

그 다음으로, FROM과 JOIN 절에서 네 개의 테이블을 연결합니다. products 테이블에서 시작해서 order_items로 "어떤 상품이 주문되었나"를 연결하고, orders로 "주문 상태와 날짜" 정보를 가져오며, categories로 "카테고리 이름"을 추가합니다.

INNER JOIN을 사용하면 모든 테이블에 매칭되는 데이터만 가져옵니다. 세 번째로, WHERE 절에서 데이터를 필터링합니다.

o.status IN ('paid', 'shipped', 'delivered')는 결제가 완료된 주문만 포함시키고(pending이나 cancelled는 제외), DATE_SUB(CURDATE(), INTERVAL 30 DAY)는 오늘부터 30일 전까지의 데이터만 가져옵니다. 이렇게 하면 최신 트렌드를 반영한 베스트셀러를 찾을 수 있어요.

마지막으로, GROUP BY로 상품별로 묶고, ORDER BY로 판매량 많은 순으로 정렬한 뒤, LIMIT 10으로 상위 10개만 추출합니다. HAVING 절은 GROUP BY 이후에 적용되는 필터인데, 판매량이 0인 상품을 제외하는 역할을 합니다.

여러분이 이 쿼리를 사용하면 실시간으로 베스트셀러 현황을 파악할 수 있고, 기간을 바꾸면(7일, 60일 등) 다양한 기간의 분석도 가능합니다. 또한 매출액 기준으로 정렬을 바꾸거나, 특정 카테고리만 필터링하는 등 응용이 무궁무진합니다.

대시보드나 리포트 자동화에도 바로 활용할 수 있죠.

실전 팁

💡 INNER JOIN 대신 LEFT JOIN을 사용하면 주문이 없는 상품도 포함됩니다. 상황에 따라 적절히 선택하세요. 베스트셀러 분석에는 INNER JOIN이 맞습니다.

💡 날짜 필터링 시 인덱스를 활용하려면 order_date >= '2025-01-01' 형태로 쓰는 게 좋습니다. 함수를 order_date에 직접 사용하면 인덱스를 못 타서 느려질 수 있어요.

💡 HAVING과 WHERE의 차이를 명확히 이해하세요. WHERE는 그룹화 전 필터, HAVING은 그룹화 후 필터입니다. 성능을 위해 가능하면 WHERE를 사용하세요.

💡 큰 데이터셋에서는 이 쿼리가 느릴 수 있습니다. 실시간 대시보드라면 결과를 별도 테이블에 저장하고 주기적으로 업데이트하는 방법도 고려해보세요.

💡 매출액 계산 시 할인, 쿠폰, 세금 등을 고려해야 한다면 order_items에 discount_amount 같은 컬럼을 추가하고 계산식을 조정하세요.

---

4. 고객별_구매_패턴_분석

시작하며

여러분이 마케팅 담당자라고 생각해보세요. "이 고객은 주로 언제 쇼핑하지?", "평균 구매 금액은 얼마나 되지?", "VIP 고객은 누구지?", "최근에 구매가 없는 고객한테 이벤트 문자를 보낼까?" 이런 질문들에 답할 수 있다면 얼마나 좋을까요?

이런 분석 없이 모든 고객에게 똑같은 마케팅을 하면 효율이 떨어집니다. 실제로 많은 기업들이 고객 세그먼트별 맞춤 마케팅으로 매출을 2배, 3배 올리고 있어요.

데이터를 활용하지 않으면 경쟁에서 뒤처질 수밖에 없습니다. 바로 이럴 때 필요한 것이 고객별 구매 패턴 분석입니다.

고객 한 명 한 명의 특성을 파악하면 개인화된 마케팅이 가능해지고, 고객 만족도와 매출이 동시에 상승합니다.

개요

간단히 말해서, 고객별 구매 패턴 분석은 각 회원의 구매 이력을 종합하여 구매 횟수, 총 구매액, 평균 구매액, 최근 구매일 등을 계산하는 분석 쿼리입니다. 왜 이게 필요한지 보면, CRM(고객관계관리) 시스템의 핵심이 바로 이 데이터입니다.

"3개월간 구매 없는 고객에게 쿠폰 발송", "누적 구매 100만원 이상 VIP 등급 부여", "첫 구매 고객에게 감사 메일 발송" 같은 마케팅 자동화가 모두 이 분석에서 시작됩니다. 예를 들어, 최근 30일간 구매가 없는 고객을 찾아서 재방문 유도 캠페인을 진행하면 이탈 방지에 효과적입니다.

기존에는 고객을 한 명씩 검색해서 주문 내역을 확인했다면, 이제는 한 번의 쿼리로 모든 고객의 구매 패턴을 한눈에 볼 수 있습니다. 심지어 엑셀로 내보내서 추가 분석도 가능하죠.

핵심은 고객별 집계(GROUP BY user), 시간 기반 분석(최근 구매일), 금액 통계(총액, 평균, 최대), 고객 세그먼테이션(VIP, 일반, 휴면 등)입니다. 이런 분석이 고객 생애 가치(LTV)를 높이는 데 결정적입니다.

코드 예제

-- 고객별 구매 패턴 종합 분석
SELECT
    u.user_id,
    u.name,
    u.email,
    COUNT(DISTINCT o.order_id) AS total_orders,  -- 총 주문 횟수
    SUM(o.total_amount) AS total_spent,  -- 총 구매 금액
    AVG(o.total_amount) AS avg_order_value,  -- 평균 주문 금액
    MAX(o.order_date) AS last_order_date,  -- 마지막 구매일
    MIN(o.order_date) AS first_order_date,  -- 첫 구매일
    DATEDIFF(CURDATE(), MAX(o.order_date)) AS days_since_last_order,  -- 마지막 구매 후 경과일
    -- 고객 등급 분류 (VIP, Gold, Silver, Bronze)
    CASE
        WHEN SUM(o.total_amount) >= 1000000 THEN 'VIP'
        WHEN SUM(o.total_amount) >= 500000 THEN 'Gold'
        WHEN SUM(o.total_amount) >= 100000 THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_grade,
    -- 고객 활성도 분류
    CASE
        WHEN DATEDIFF(CURDATE(), MAX(o.order_date)) <= 30 THEN 'Active'
        WHEN DATEDIFF(CURDATE(), MAX(o.order_date)) <= 90 THEN 'At Risk'
        ELSE 'Inactive'
    END AS customer_status
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
    AND o.status IN ('paid', 'shipped', 'delivered')  -- 완료된 주문만
GROUP BY u.user_id, u.name, u.email
ORDER BY total_spent DESC;  -- 총 구매액 높은 순

설명

이것이 하는 일: 모든 회원의 구매 이력을 분석하여 각 고객의 총 주문 횟수, 총/평균 구매액, 최근 구매일을 계산하고, 이를 바탕으로 고객 등급과 활성도를 자동으로 분류합니다. 첫 번째로, SELECT 절에서 다양한 집계 함수를 사용합니다.

COUNT(DISTINCT o.order_id)는 중복을 제거한 주문 횟수를 세고, SUM(o.total_amount)는 이 고객이 지금까지 쓴 총액을 합산하며, AVG(o.total_amount)는 한 번 주문할 때 평균 얼마를 쓰는지 계산합니다. MAX(o.order_date)와 MIN(o.order_date)로 첫 구매와 마지막 구매 날짜를 알 수 있어요.

그 다음으로, DATEDIFF(CURDATE(), MAX(o.order_date))는 오늘 날짜에서 마지막 구매일을 빼서 며칠이 지났는지 계산합니다. 이게 30일 이하면 활성 고객, 90일 이상이면 휴면 고객으로 판단할 수 있죠.

이런 지표를 RFM(Recency, Frequency, Monetary) 분석이라고 합니다. 세 번째로, CASE 문을 사용하여 고객을 자동으로 분류합니다.

총 구매액이 100만원 이상이면 VIP, 50만원 이상이면 Gold 같은 식으로 등급을 매기고, 마지막 구매 후 경과일에 따라 Active, At Risk, Inactive로 상태를 분류합니다. 이렇게 하면 SQL 쿼리 결과만 봐도 어떤 고객에게 어떤 마케팅을 해야 할지 바로 알 수 있어요.

마지막으로, LEFT JOIN을 사용한 이유는 주문이 없는 신규 회원도 결과에 포함시키기 위함입니다. INNER JOIN을 쓰면 한 번도 구매하지 않은 회원은 결과에서 빠져버리거든요.

GROUP BY로 고객별로 묶고, ORDER BY로 총 구매액이 높은 VIP부터 보여줍니다. 여러분이 이 쿼리를 사용하면 고객 세그먼테이션을 자동화할 수 있고, At Risk 고객에게 타겟 마케팅을 하거나, VIP 고객에게 특별 혜택을 제공하는 등 데이터 기반 의사결정이 가능합니다.

또한 WHERE 절에 조건을 추가하면 "Inactive 고객만 추출", "Gold 등급 이상만 추출" 같은 세밀한 분석도 가능해요.

실전 팁

💡 LEFT JOIN을 사용할 때 주의할 점: 주문이 없는 회원은 집계 함수 결과가 NULL이 될 수 있습니다. COALESCE(COUNT(o.order_id), 0) 같은 함수로 NULL을 0으로 바꾸면 더 안전합니다.

💡 고객 등급 기준(100만원, 50만원 등)은 업종과 비즈니스 모델에 따라 조정하세요. 정답은 없고, A/B 테스트를 통해 최적값을 찾는 게 좋습니다.

💡 이 쿼리는 회원 수가 많으면 느릴 수 있습니다. 결과를 customer_analytics 같은 별도 테이블에 저장하고 매일 새벽에 배치로 갱신하는 방법도 고려하세요.

💡 RFM 분석을 더 정교하게 하려면 Recency, Frequency, Monetary 각각에 점수를 매기고 조합하는 방식도 있습니다. 관심 있다면 RFM 스코어링을 검색해보세요.

💡 개인정보 보호를 위해 이메일이나 이름 같은 민감 정보는 실제 운영 환경에서 SELECT 하지 않거나, 마스킹 처리하는 게 좋습니다.

---

5. 재고_관리_쿼리_작성

시작하며

여러분이 상품 관리자라고 상상해보세요. "이 상품 재고가 얼마나 남았지?", "어떤 상품이 품절 위기지?", "재고가 부족한 상품은 빨리 발주해야 하는데..." 이런 고민을 매일 하게 됩니다.

재고 관리가 제대로 안 되면 큰 문제가 생깁니다. 재고가 부족하면 주문을 받아도 배송을 못 하고, 재고가 너무 많으면 창고 비용이 증가하고 유통기한이 있는 상품은 폐기 손실이 발생합니다.

실제로 많은 쇼핑몰들이 재고 관리 실패로 고객 불만과 손실을 겪고 있어요. 바로 이럴 때 필요한 것이 재고 관리 쿼리입니다.

현재 재고 현황, 판매 추이, 예상 소진 시점을 자동으로 계산하면 효율적인 재고 관리가 가능합니다.

개요

간단히 말해서, 재고 관리 쿼리는 상품별 현재 재고량과 최근 판매량을 비교하여 재고 상태를 파악하고, 품절 위험이 있는 상품을 미리 찾아내는 분석 쿼리입니다. 왜 이게 필요한지 보면, 재고 담당자는 매일 "어떤 상품을 발주해야 하나"를 결정해야 합니다.

인기 상품은 빨리 소진되므로 여유 있게 확보해야 하고, 느리게 팔리는 상품은 최소 재고만 유지해야 창고 비용을 절감할 수 있습니다. 예를 들어, 최근 7일간 하루 평균 10개씩 팔리는 상품의 현재 재고가 20개라면, 2일 후 품절될 위험이 있으니 긴급 발주가 필요하다는 걸 알 수 있죠.

기존에는 재고 장부를 수작업으로 관리하거나 엑셀로 계산했다면, 이제는 SQL 쿼리로 실시간 재고 현황과 예측을 자동으로 할 수 있습니다. 심지어 임계값 이하 재고는 자동으로 알림을 보내는 시스템도 만들 수 있어요.

핵심은 현재 재고 조회, 판매 속도 계산(일평균 판매량), 재고 소진 예상일 계산, 안전 재고 수준 설정입니다. 이런 지표들이 재고 회전율을 높이고 품절/과재고를 방지하는 데 필수적입니다.

코드 예제

-- 재고 현황 및 품절 위험 분석
SELECT
    p.product_id,
    p.product_name,
    c.category_name,
    p.stock_quantity AS current_stock,  -- 현재 재고
    p.price,
    -- 최근 30일 판매량
    COALESCE(SUM(oi.quantity), 0) AS sales_last_30days,
    -- 일평균 판매량
    COALESCE(SUM(oi.quantity) / 30.0, 0) AS avg_daily_sales,
    -- 재고 소진 예상일 (현재재고 / 일평균판매량)
    CASE
        WHEN COALESCE(SUM(oi.quantity) / 30.0, 0) > 0
        THEN ROUND(p.stock_quantity / (SUM(oi.quantity) / 30.0), 1)
        ELSE NULL
    END AS days_until_stockout,
    -- 재고 상태 분류
    CASE
        WHEN p.stock_quantity = 0 THEN 'Out of Stock'
        WHEN p.stock_quantity > 0 AND p.stock_quantity / (COALESCE(SUM(oi.quantity) / 30.0, 1)) < 7 THEN 'Critical'
        WHEN p.stock_quantity / (COALESCE(SUM(oi.quantity) / 30.0, 1)) < 14 THEN 'Low'
        ELSE 'Sufficient'
    END AS stock_status,
    -- 권장 발주 수량 (14일치 재고 유지 기준)
    GREATEST(0, ROUND((SUM(oi.quantity) / 30.0 * 14) - p.stock_quantity)) AS recommended_order_qty
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
    AND o.status IN ('paid', 'shipped', 'delivered')
    AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY p.product_id, p.product_name, c.category_name, p.stock_quantity, p.price
HAVING stock_status IN ('Out of Stock', 'Critical', 'Low')  -- 재고 부족 상품만
ORDER BY
    CASE stock_status
        WHEN 'Out of Stock' THEN 1
        WHEN 'Critical' THEN 2
        WHEN 'Low' THEN 3
    END,
    days_until_stockout ASC;

설명

이것이 하는 일: 모든 상품의 현재 재고량을 조회하고, 최근 30일간 판매 데이터를 분석하여 일평균 판매량을 계산한 뒤, 현재 재고가 며칠 후에 소진될지 예측하고, 재고 상태를 자동으로 분류하여 긴급 발주가 필요한 상품을 우선순위별로 보여줍니다. 첫 번째로, SELECT 절에서 현재 재고(p.stock_quantity)와 최근 30일 판매량을 가져옵니다.

COALESCE(SUM(oi.quantity), 0)는 판매 내역이 없는 상품의 경우 NULL 대신 0을 반환하도록 합니다. 판매가 없으면 SUM 결과가 NULL이 되거든요.

SUM(oi.quantity) / 30.0으로 일평균 판매량을 계산하는데, 30.0으로 나누는 이유는 소수점 계산을 위해 실수형으로 변환하기 위함입니다. 그 다음으로, 재고 소진 예상일을 계산합니다.

p.stock_quantity / (SUM(oi.quantity) / 30.0)는 현재 재고를 일평균 판매량으로 나눈 것으로, "현재 재고로 며칠 버틸 수 있나"를 의미합니다. 예를 들어 재고 100개, 일평균 판매 10개면 10일 후 품절 예상입니다.

CASE 문으로 판매량이 0인 경우를 체크하여 0으로 나누는 오류를 방지합니다. 세 번째로, 재고 상태를 4단계로 분류합니다.

재고가 0이면 'Out of Stock', 7일치 미만이면 'Critical', 14일치 미만이면 'Low', 그 이상이면 'Sufficient'로 분류합니다. 이 기준은 업종에 따라 조정할 수 있어요.

식품처럼 유통기한이 짧으면 기준을 더 짧게 설정하고, 내구재는 길게 설정하면 됩니다. 마지막으로, 권장 발주 수량을 계산합니다.

(일평균 판매량 * 14일) - 현재 재고로 14일치 재고를 유지하는 데 필요한 발주량을 제시합니다. GREATEST(0, ...)는 음수가 나오지 않도록 최소값을 0으로 제한합니다.

HAVING 절로 재고가 부족한 상품만 필터링하고, ORDER BY로 품절 상품을 최우선으로 정렬합니다. 여러분이 이 쿼리를 사용하면 매일 아침 재고 현황 리포트를 자동으로 받아볼 수 있고, Critical 상태 상품을 바로 발주 처리할 수 있습니다.

또한 권장 발주 수량까지 제시되므로 발주 의사결정이 빠르고 정확해집니다. 엑셀로 내보내서 발주서를 만들거나, 이메일/SMS 알림 시스템과 연동하면 완전 자동화도 가능해요.

실전 팁

💡 안전 재고 기준(7일, 14일)은 상품 특성에 따라 다르게 설정하세요. 발주 후 입고까지 시간이 오래 걸리는 수입 제품은 30일치 이상 확보하는 게 안전합니다.

💡 시즌 상품이나 이벤트 상품은 과거 30일 데이터만으로는 예측이 부정확합니다. 작년 동기 데이터나 프로모션 효과를 추가로 고려해야 합니다.

💡 COALESCE 함수는 NULL 처리의 핵심입니다. 판매 내역이 없는 신상품이나 단종 예정 상품도 쿼리 결과에 포함시키려면 꼭 사용하세요.

💡 재고 테이블과 판매 테이블의 데이터 일관성이 중요합니다. 주문 시 재고 차감을 트랜잭션으로 처리하고, 재고 음수 방지 로직을 반드시 구현하세요.

💡 이 쿼리를 뷰(VIEW)로 만들어두면 매번 복잡한 쿼리를 작성할 필요 없이 SELECT * FROM stock_alert_view 형태로 간단히 조회할 수 있습니다.

---

6. 성능_최적화_및_인덱스_설계

시작하며

여러분이 만든 쇼핑몰이 인기를 끌어서 회원이 10만 명, 상품이 1만 개, 주문이 100만 건이 되었다고 상상해보세요. 그런데 갑자기 베스트셀러 조회가 10초씩 걸리고, 고객이 검색할 때마다 "로딩 중..."이 계속 뜨면 어떻게 될까요?

이런 성능 문제는 실제로 많은 서비스들이 겪는 성장통입니다. 데이터가 적을 때는 아무 문제 없던 쿼리가 데이터가 많아지면 느려지고, 결국 고객들이 "느려서 못 쓰겠다"며 이탈하게 됩니다.

아마존은 페이지 로딩이 0.1초 느려지면 매출이 1% 감소한다는 연구 결과도 있어요. 바로 이럴 때 필요한 것이 성능 최적화와 인덱스 설계입니다.

올바른 인덱스 하나로 쿼리 속도를 100배, 1000배 빠르게 만들 수 있습니다.

개요

간단히 말해서, 인덱스는 데이터베이스의 '목차'입니다. 책에서 특정 내용을 찾을 때 처음부터 끝까지 읽지 않고 목차를 보듯이, 인덱스가 있으면 데이터베이스가 필요한 데이터를 빠르게 찾을 수 있습니다.

왜 이게 필요한지 보면, 인덱스가 없으면 데이터베이스는 Full Table Scan, 즉 테이블의 모든 행을 처음부터 끝까지 검사합니다. 100만 건의 주문에서 특정 회원의 주문을 찾으려면 100만 건을 다 뒤져야 하는 거죠.

하지만 user_id에 인덱스가 있으면 몇 밀리초 만에 찾을 수 있습니다. 예를 들어, "홍길동 회원의 최근 주문 내역 조회" 같은 쿼리가 10초에서 0.01초로 단축될 수 있어요.

기존에는 느린 쿼리를 발견하면 서버를 업그레이드하거나 캐싱을 추가했다면, 이제는 적절한 인덱스를 설계하여 하드웨어 투자 없이도 성능을 극적으로 개선할 수 있습니다. 돈보다 지식이 중요한 영역입니다.

핵심은 WHERE 절에 사용되는 컬럼, JOIN에 사용되는 컬럼, ORDER BY에 사용되는 컬럼에 인덱스를 거는 것입니다. 단, 인덱스가 많으면 INSERT/UPDATE가 느려지므로 균형을 맞춰야 합니다.

또한 복합 인덱스의 순서도 중요한데, 카디널리티(고유값 개수)가 높은 컬럼을 앞에 배치하는 게 효율적입니다.

코드 예제

-- 1. 기본 인덱스 설계 원칙

-- 회원 테이블: 이메일 검색 최적화 (로그인 시 사용)
CREATE INDEX idx_users_email ON users(email);

-- 상품 테이블: 카테고리별 조회 최적화
CREATE INDEX idx_products_category ON products(category_id);

-- 상품 테이블: 가격 범위 검색 최적화 (필터링 시 사용)
CREATE INDEX idx_products_price ON products(price);

-- 2. 복합 인덱스 (Composite Index) - 여러 컬럼 조합

-- 주문 테이블: 회원별 + 날짜별 조회 최적화
-- "홍길동 회원의 2025년 1월 주문 내역" 같은 쿼리에 효과적
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 주문 테이블: 상태별 + 날짜별 조회 최적화
-- "오늘 배송 완료된 주문 조회" 같은 쿼리에 효과적
CREATE INDEX idx_orders_status_date ON orders(status, order_date);

-- 3. 커버링 인덱스 (Covering Index) - 쿼리에 필요한 모든 컬럼 포함

-- 주문 아이템: 주문별 상품 조회 시 테이블 접근 없이 인덱스만으로 처리
CREATE INDEX idx_order_items_covering ON order_items(order_id, product_id, quantity, unit_price);

-- 4. 인덱스 성능 확인

-- 쿼리 실행 계획 확인 (인덱스 사용 여부 체크)
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND order_date >= '2025-01-01';

-- 느린 쿼리 찾기 (5초 이상 걸리는 쿼리 기록)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 5;

-- 인덱스 사용 통계 확인 (사용되지 않는 인덱스 찾기)
SELECT * FROM sys.schema_unused_indexes;

설명

이것이 하는 일: 자주 조회되는 컬럼과 조건에 인덱스를 생성하여 데이터베이스가 빠르게 데이터를 찾을 수 있도록 하고, 쿼리 실행 계획을 분석하여 인덱스가 제대로 사용되는지 확인합니다. 첫 번째로, 단일 컬럼 인덱스를 만듭니다.

CREATE INDEX idx_users_email ON users(email)은 이메일 컬럼에 인덱스를 생성하는데, 로그인할 때 "WHERE email = 'user@example.com'" 같은 조건으로 회원을 찾을 때 엄청나게 빨라집니다. 인덱스 이름은 idx_테이블명_컬럼명 규칙으로 지으면 나중에 관리하기 편해요.

상품 테이블의 category_id와 price에도 인덱스를 걸면 "의류 카테고리에서 3만원 이하 상품" 같은 필터 검색이 빨라집니다. 그 다음으로, 복합 인덱스를 만듭니다.

idx_orders_user_date ON orders(user_id, order_date)는 user_id와 order_date 두 컬럼을 조합한 인덱스인데, "특정 회원의 특정 기간 주문"을 조회할 때 매우 효과적입니다. 여기서 중요한 건 컬럼 순서입니다.

user_id를 먼저 두면 "user_id만 사용하는 쿼리"나 "user_id와 order_date를 함께 사용하는 쿼리" 둘 다 이 인덱스를 활용할 수 있어요. 하지만 반대로 하면 order_date만 사용하는 쿼리는 이 인덱스를 못 씁니다.

세 번째로, 커버링 인덱스를 만듭니다. idx_order_items_covering은 order_id, product_id, quantity, unit_price를 모두 포함하는데, 이렇게 하면 "주문별 상품 목록" 쿼리가 인덱스만 읽고 실제 테이블에 접근하지 않아서 더 빠릅니다.

마치 책의 요약본만 읽고 본문을 안 읽는 것과 같아요. 다만 인덱스 크기가 커지므로 꼭 필요한 경우에만 사용하세요.

마지막으로, EXPLAIN 명령어로 쿼리 실행 계획을 확인합니다. type이 'ALL'이면 Full Table Scan(나쁨), 'ref'나 'range'면 인덱스 사용(좋음), 'const'면 PRIMARY KEY 사용(매우 좋음)입니다.

key 컬럼에 인덱스 이름이 표시되면 그 인덱스가 사용되는 거예요. rows는 스캔할 예상 행 수인데, 이게 적을수록 빠릅니다.

slow_query_log를 켜두면 느린 쿼리가 자동으로 기록되어 나중에 최적화할 수 있습니다. 여러분이 이 인덱스들을 적용하면 대부분의 조회 쿼리가 100배 이상 빨라집니다.

특히 데이터가 많을수록 효과가 극적이에요. 또한 EXPLAIN으로 쿼리를 분석하는 습관을 들이면 성능 문제를 미리 예방할 수 있고, 느린 쿼리가 발생했을 때 빠르게 원인을 찾아 해결할 수 있습니다.

실전 팁

💡 인덱스는 만능이 아닙니다. 테이블 크기의 30% 이상을 조회하는 쿼리는 인덱스보다 Full Table Scan이 더 빠를 수 있어요. MySQL 옵티마이저가 자동으로 판단하지만, 간혹 잘못 판단할 때도 있습니다.

💡 복합 인덱스의 컬럼 순서는 매우 중요합니다. 일반적으로 카디널리티가 높은(고유값이 많은) 컬럼을 앞에 두세요. 성별(2가지) < 연령대(10가지) < 도시(100가지) < 이메일(수만 가지) 순입니다.

💡 인덱스가 많으면 INSERT, UPDATE, DELETE 성능이 떨어집니다. 데이터를 수정할 때마다 인덱스도 함께 업데이트해야 하거든요. 읽기가 90% 이상인 테이블에만 적극적으로 인덱스를 거세요.

💡 VARCHAR나 TEXT 컬럼에 인덱스를 걸 때는 Prefix Index를 고려하세요. CREATE INDEX idx_name ON users(name(10))처럼 앞 10자만 인덱싱하면 크기를 줄일 수 있습니다.

💡 인덱스는 주기적으로 재구성(OPTIMIZE TABLE)해야 합니다. 데이터가 계속 수정되면 인덱스가 단편화되어 성능이 떨어질 수 있어요. 하지만 재구성 중에는 테이블이 잠기므로 새벽 시간대에 하세요.

---