[DB-012][실무] 주문표를 만들고 상품별 매출 계산하기 > IT 기술 공유

• 목록

[이번 수업]

주문·상품·주문항목 표를 만들고 상품별 판매 수량과 매출을 계산합니다. 모델링, 제약조건, JOIN, 집계를 한 번에 연결하는 작은 프로젝트입니다.

[선수지식]

DB-002의 SQL, DB-003의 JOIN, DB-004의 정규화, DB-006의 트랜잭션을 알면 좋습니다. Python의 sqlite3 표준 모듈을 사용합니다.

[학습목표]

1. 주문과 상품의 다대다 관계를 주문항목으로 나눈다.
2. 기본키·외래키·CHECK로 잘못된 데이터를 막는다.
3. 결제 완료 주문만 모아 상품별 매출을 계산한다.

[핵심개념]

한 주문에는 여러 상품이 있고 한 상품도 여러 주문에 들어가므로 중간 표 `order_items`가 필요합니다. 이 표에는 수량뿐 아니라 주문 당시 단가를 저장합니다. 상품의 현재 가격이 바뀌어도 과거 매출을 그대로 계산하기 위해서입니다.

기본키는 행을 구분하고 외래키는 연결 대상이 실제로 있는지 확인합니다. CHECK는 수량이 양수인지, 상태가 허용한 값인지 검사합니다. 분석 쿼리는 주문→주문항목→상품을 JOIN한 뒤 `GROUP BY`와 `SUM`으로 묶습니다. 취소 주문은 WHERE에서 제외합니다.

[따라하기]

orders.py를 만들고 붙여 넣으세요. 메모리 데이터베이스라 실행할 때마다 깨끗하게 시작합니다.
```python
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  ordered_at TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('PAID', 'CANCELLED'))
);
CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price INTEGER NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO products VALUES (1, '노트'), (2, '펜');
INSERT INTO orders VALUES
  (101, '2026-09-01', 'PAID'),
  (102, '2026-09-02', 'PAID'),
  (103, '2026-09-03', 'CANCELLED');
INSERT INTO order_items VALUES
  (101, 1, 2, 3000),
  (101, 2, 1, 5000),
  (102, 2, 3, 4500),
  (103, 1, 100, 1);
""")

query = """
SELECT p.name,
      SUM(i.quantity) AS units,
      SUM(i.quantity * i.unit_price) AS sales
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
JOIN products AS p ON p.id = i.product_id
WHERE o.status = 'PAID'
GROUP BY p.id, p.name
ORDER BY sales DESC, p.name;
"""

for name, units, sales in db.execute(query):
    print(f"{name} | {units} | {sales}")
db.close()
```

macOS·Linux는 `python3 orders.py`, Windows는 `py orders.py`로 실행합니다. 예상 결과는 첫 줄 `펜 | 4 | 18500`, 둘째 줄 `노트 | 2 | 6000`입니다. 취소 주문의 노트 100개가 빠졌는지 확인하세요.

[흔한 실수]

상품 표의 현재 가격으로 과거 매출을 다시 계산하지 마세요. 주문 한 행에 상품 목록을 쉼표로 넣거나, JOIN 조건을 빠뜨려 행이 불어나는 실수도 흔합니다. 집계 전 원본 행 수를 확인하면 원인을 찾기 쉽습니다.

[보안 주의]

외부 입력을 SQL 문자열에 이어 붙이지 말고 `?` 자리표시자와 매개변수를 사용하세요. 실습은 가짜 데이터와 본인 소유 로컬 환경에서만 진행합니다. 실제 금액은 통화와 최소 단위를 정하고 부동소수점 오차를 피해야 합니다.

[직접 해볼 과제]

2026-09-02 이후 주문만 계산하는 날짜 조건을 추가하세요. PAID 주문 한 건도 더 넣어 수량과 매출이 예상대로 바뀌는지 확인합니다.

[확인문제]

1. 주문항목에 주문 당시 단가를 저장하는 이유는 무엇인가요?
2. 외래키와 CHECK는 각각 어떤 오류를 막나요?
3. 취소 주문을 집계에서 제외하려면 어느 단계에서 조건을 넣나요?

[다음 학습]

OS-012에서 이 데이터 실습을 안전하게 실행할 Linux 서버 환경을 만듭니다.

[공식 참고 자료]

https://www.sqlite.org/foreignkeys.html
https://www.sqlite.org/lang_aggfunc.html
https://www.sqlite.org/lang_createtable.html
https://docs.python.org/3/library/sqlite3.html
https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html