14_SQL GROUP BY 실습 2

1. 인트로

GROUP BY의 심화 내용으로, 날짜/시간 데이터를 활용한 그룹화와 복잡한 GROUP BY 심화 패턴을 익힌다. 실제 서비스에서 "월별 매출", "시간대별 주문 수" 같은 통계는 이 패턴으로 만들어진다.

---

2. 실습 DB 준비

import sqlite3
import pandas as pd
from datetime import datetime, timedelta
import random

conn = sqlite3.connect("orders.db")
cur  = conn.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS sales (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        product  TEXT    NOT NULL,
        category TEXT    NOT NULL,
        amount   INTEGER NOT NULL,
        qty      INTEGER DEFAULT 1,
        sale_dt  TEXT    NOT NULL
    )
""")

# 6개월치 샘플 데이터 생성
products = [
    ("갤럭시S24", "스마트폰", 1250000),
    ("아이폰15",  "스마트폰", 1350000),
    ("맥북에어",  "노트북",   1500000),
    ("갤럭시북",  "노트북",   1200000),
    ("아이패드",  "태블릿",   900000),
    ("갤럭시탭",  "태블릿",   700000),
]

random.seed(42)
start = datetime(2024, 7, 1)
for _ in range(200):
    p    = random.choice(products)
    days = random.randint(0, 183)
    dt   = (start + timedelta(days=days)).strftime("%Y-%m-%d")
    cur.execute(
        "INSERT INTO sales (product,category,amount,qty,sale_dt) VALUES (?,?,?,?,?)",
        (p[0], p[1], p[2], random.randint(1, 3), dt)
    )

conn.commit()
print("200개 판매 데이터 생성 완료")

코드 설명:

• 3행: datetime, timedelta로 날짜 계산을 한다.
• 20~27행: 6가지 상품 정보를 리스트에 담는다.
• 29행: random.seed(42) — 동일한 랜덤 데이터를 재현 가능하게 고정한다.
• 31행: random.choice(products) — 상품 목록에서 무작위로 하나를 선택한다.
• 33~35행: timedelta(days=days) — 시작일로부터 랜덤 일수를 더하여 판매 날짜를 만든다.

---

3. 시간/날짜 데이터를 활용한 GROUP BY 실습

3.1. 월별 매출 집계

df = pd.read_sql("""
    SELECT SUBSTR(sale_dt, 1, 7)   AS 월,
           COUNT(id)               AS 주문수,
           SUM(amount * qty)       AS 총매출
    FROM sales
    GROUP BY SUBSTR(sale_dt, 1, 7)
    ORDER BY 월
""", conn)
print("=== 월별 매출 ===")
print(df)

코드 설명:

• 2행: SUBSTR(sale_dt, 1, 7) — 날짜 문자열에서 앞 7자리(YYYY-MM)만 추출한다.
  • '2024-08-15'에서 '2024-08'을 추출한다.
• 3행: COUNT(id) — 해당 월의 주문 수를 센다.
• 4행: SUM(amount * qty) — 금액 × 수량으로 총 매출을 계산한다.
• 6행: GROUP BY에도 같은 SUBSTR 표현식을 사용한다.

3.2. 카테고리별 월별 매출 (교차 분석)

df = pd.read_sql("""
    SELECT SUBSTR(sale_dt, 1, 7) AS 월,
           category              AS 카테고리,
           SUM(amount * qty)     AS 매출
    FROM sales
    GROUP BY SUBSTR(sale_dt, 1, 7), category
    ORDER BY 월, 카테고리
""", conn)
print("=== 카테고리별 월별 매출 ===")
print(df)

코드 설명:

• 6행: GROUP BY 월, category — 월과 카테고리 두 기준으로 동시에 그룹화한다.
• 결과는 각 월의 각 카테고리별 매출을 보여준다.

---

4. GROUP BY 심화 실습 1

4.1. 파이썬으로 결과 피벗(Pivot(피벗) 변환)

# 월별 × 카테고리별 교차표 만들기
df = pd.read_sql("""
    SELECT SUBSTR(sale_dt, 1, 7) AS 월,
           category              AS 카테고리,
           SUM(amount * qty)     AS 매출
    FROM sales
    GROUP BY SUBSTR(sale_dt, 1, 7), category
""", conn)

pivot = df.pivot(index="월", columns="카테고리", values="매출").fillna(0)
pivot = pivot.astype(int)
print("=== 피벗 테이블 ===")
print(pivot)

코드 설명:

• 9행: pivot() — 행(index)은 월, 열(columns)은 카테고리, 값은 매출로 교차표를 만든다.
• 9행: .fillna(0) — 데이터가 없는 셀은 0으로 채운다.
• 10행: .astype(int) — 소수점을 제거하고 정수로 변환한다.

---

5. GROUP BY 심화 실습 2

5.1. 상품별 누적 판매 순위

df = pd.read_sql("""
    SELECT product             AS 상품명,
           category            AS 카테고리,
           SUM(qty)            AS 총판매량,
           SUM(amount * qty)   AS 총매출,
           ROUND(AVG(qty), 1)  AS 평균주문수량
    FROM sales
    GROUP BY product, category
    ORDER BY 총매출 DESC
""", conn)
print("=== 상품별 판매 순위 ===")
print(df)

5.2. 전체 대비 카테고리 비중 계산

df = pd.read_sql("""
    SELECT category AS 카테고리,
           SUM(amount * qty) AS 매출,
           ROUND(
               SUM(amount * qty) * 100.0 /
               (SELECT SUM(amount * qty) FROM sales),
               1
           ) AS 비중
    FROM sales
    GROUP BY category
    ORDER BY 매출 DESC
""", conn)
print("=== 카테고리별 매출 비중 ===")
print(df)

코드 설명:

• 5~7행: 서브쿼리(SubQuery(서브쿼리))로 전체 총 매출을 구하고, 각 카테고리 매출을 나누어 비중을 계산한다.
• * 100.0 — 정수 나눗셈을 방지하기 위해 100.0을 곱한다.

---

6. 활용 Plus — 리액트 차트(Chart(차트)) 연동

리액트 대시보드에서 막대 차트(Bar Chart(바 차트)) 데이터를 API로 제공하는 예시이다.

def get_monthly_chart():
    df = pd.read_sql("""
        SELECT SUBSTR(sale_dt, 1, 7) AS month,
               SUM(amount * qty)     AS revenue
        FROM sales
        GROUP BY SUBSTR(sale_dt, 1, 7)
        ORDER BY month
    """, conn)
    return {
        "labels": df["month"].tolist(),
        "data"  : df["revenue"].tolist()
    }

result = get_monthly_chart()
print(result)

코드 설명:

• 9행: df["month"].tolist() — 월 목록을 리스트로 변환한다. 리액트 차트의 x축 라벨이 된다.
• 10행: df["revenue"].tolist() — 매출 목록을 리스트로 변환한다. 리액트 차트의 y축 데이터가 된다.
• 리액트에서 이 데이터를 받아 Chart.js(차트제이에스)나 Recharts(리차트)로 그래프를 그린다.

---

7. 문제풀기