[DATA-004][기초] SQL로 묶고 계산해 매출 요약표 만들기 > IT 기술 공유

• 목록

[이번 수업]
행이 수백 개여도 한 줄씩 세지 않고 SQL로 그룹별 개수·합계·평균을 구해 봅니다. SQL은 데이터베이스에 ‘어떤 결과가 필요한지’ 요청하는 언어입니다.

[선수지식]
DATA-001~003의 표·행·열, SELECT, WHERE 개념과 Python 파일 실행 방법을 알면 좋습니다.

[학습목표]
1. 집계 함수 COUNT, SUM, AVG의 결과를 설명한다.
2. GROUP BY로 같은 범주의 행을 한 그룹으로 묶는다.
3. WHERE와 HAVING의 적용 시점을 구분한다.

[핵심개념]
집계는 여러 행을 하나의 요약값으로 줄이는 작업입니다. COUNT(*)는 그룹의 행 수, SUM(식)은 합계, AVG(식)은 평균을 계산합니다. GROUP BY category는 같은 category를 가진 행들을 묶고 각 그룹마다 결과 한 행을 만듭니다.

WHERE는 그룹을 만들기 전에 원본 행을 거릅니다. HAVING은 그룹별 계산이 끝난 뒤 합계 같은 집계 결과를 거릅니다. 따라서 ‘수량이 양수인 주문만’은 WHERE, ‘매출 3만 원 이상인 범주만’은 HAVING에 둡니다. ORDER BY revenue DESC는 큰 매출부터 정렬합니다.

COUNT(*)는 주문 행 수이지 판매 수량이 아닙니다. 판매 수량은 SUM(quantity)로 구합니다. AVG(price * quantity)는 주문 한 행당 평균 금액입니다.

[따라하기]
아래 내용을 sales_summary.py로 저장하세요.

```python
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("""
CREATE TABLE sales (
    category TEXT NOT NULL,
    price INTEGER NOT NULL CHECK (price >= 0),
    quantity INTEGER NOT NULL CHECK (quantity > 0)
)
""")
con.executemany(
    "INSERT INTO sales VALUES (?, ?, ?)",
    [("책", 12000, 2), ("책", 15000, 1),
    ("강의", 30000, 1), ("강의", 20000, 2),
    ("도구", 5000, 3)],
)

minimum_revenue = 30000
sql = """
SELECT category,
      COUNT(*) AS order_count,
      SUM(price * quantity) AS revenue,
      ROUND(AVG(price * quantity), 1) AS avg_order
FROM sales
WHERE quantity > 0
GROUP BY category
HAVING SUM(price * quantity) >= ?
ORDER BY revenue DESC, category ASC
"""
for row in con.execute(sql, (minimum_revenue,)):
    print(row)
con.close()
```

Windows는 PowerShell에서 `py sales_summary.py`, macOS·Linux는 터미널에서 `python3 sales_summary.py`를 실행합니다. 예상 결과는 다음과 같습니다.

```text
('강의', 2, 70000, 35000.0)
('책', 2, 39000, 19500.0)
```

‘도구’ 매출은 15,000원이므로 HAVING 조건에서 빠집니다. minimum_revenue를 10000으로 바꾸면 세 범주가 모두 표시됩니다.

[흔한 실수]
SELECT에 그룹 기준이 아닌 열을 그대로 넣으면 DB에 따라 오류나 불분명한 값이 생길 수 있습니다. 그런 열은 GROUP BY에 넣거나 집계 함수로 계산하세요. WHERE에 SUM(...)을 쓰지 말고 평균이 ‘상품 단가’인지 ‘주문 금액’인지도 먼저 정하세요.

[보안 주의]
사용자 입력을 문자열 연결이나 f-string으로 SQL에 붙이지 마세요. 예제의 `?`는 자리표시자이며 실제 값은 두 번째 인수의 튜플로 전달됩니다. 값에는 바인딩을 쓰되, 테이블명·열명처럼 SQL 구조를 입력받아야 한다면 허용 목록으로 제한하세요. 실습은 본인 소유의 로컬 데이터에서만 하고, 실제 개인정보나 운영 DB를 복사하지 마세요.

[직접 해볼 과제]
1. SELECT에 SUM(quantity) AS units를 추가해 범주별 판매 수량을 표시하세요.
2. ‘도구’ 행 하나를 더 넣고 minimum_revenue를 바꾸어 HAVING 결과를 비교하세요.
3. category 대신 날짜 열을 추가하고 월별 매출을 어떻게 묶을지 설계해 보세요.

[확인문제]
1. WHERE와 HAVING은 각각 어느 시점의 데이터를 거르나요?
2. COUNT(*)와 SUM(quantity)는 무엇을 다르게 세나요?
3. 조건값을 SQL 문자열에 직접 붙이지 않고 `?`로 전달하는 이유는 무엇인가요?

[다음 학습]
DATA-005에서는 누락값과 이상값을 찾고 정리하는 방법을 배웁니다. 그 전에 NULL이 포함된 행을 추가하여 COUNT(*), COUNT(category), AVG의 결과가 어떻게 달라지는지 확인해 보세요.

[공식 참고 자료]
SQLite SELECT와 GROUP BY·HAVING: https://www.sqlite.org/lang_select.html
SQLite 내장 집계 함수: https://www.sqlite.org/lang_aggfunc.html
Python sqlite3 공식 문서: https://docs.python.org/3/library/sqlite3.html