14 SQL GROUP BY 실습 2

1. 인트로

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

2. 실습 DB 준비

1
import sqlite3
2
import pandas as pd
3
from datetime import datetime, timedelta
4
import random
5

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

9
cur.execute("""
10
    CREATE TABLE IF NOT EXISTS sales (
11
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
12
        product  TEXT    NOT NULL,
13
        category TEXT    NOT NULL,
14
        amount   INTEGER NOT NULL,
15
        qty      INTEGER DEFAULT 1,
16
        sale_dt  TEXT    NOT NULL
17
    )
18
""")
19

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

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

41
conn.commit()
42
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. 월별 매출 집계

1
df = pd.read_sql("""
2
    SELECT SUBSTR(sale_dt, 1, 7)   AS 월,
3
           COUNT(id)               AS 주문수,
4
           SUM(amount * qty)       AS 총매출
5
    FROM sales
6
    GROUP BY SUBSTR(sale_dt, 1, 7)
7
    ORDER BY 월
8
""", conn)
9
print("=== 월별 매출 ===")
10
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. 카테고리별 월별 매출 (교차 분석)

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

코드 설명:

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

4. GROUP BY 심화 실습 1

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

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

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

코드 설명:

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

5. GROUP BY 심화 실습 2

5.1. 상품별 누적 판매 순위

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

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

1
df = pd.read_sql("""
2
    SELECT category AS 카테고리,
3
           SUM(amount * qty) AS 매출,
4
           ROUND(
5
               SUM(amount * qty) * 100.0 /
6
               (SELECT SUM(amount * qty) FROM sales),
7
               1
8
           ) AS 비중
9
    FROM sales
10
    GROUP BY category
11
    ORDER BY 매출 DESC
12
""", conn)
13
print("=== 카테고리별 매출 비중 ===")
14
print(df)

코드 설명:

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

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

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

1
def get_monthly_chart():
2
    df = pd.read_sql("""
3
        SELECT SUBSTR(sale_dt, 1, 7) AS month,
4
               SUM(amount * qty)     AS revenue
5
        FROM sales
6
        GROUP BY SUBSTR(sale_dt, 1, 7)
7
        ORDER BY month
8
    """, conn)
9
    return {
10
        "labels": df["month"].tolist(),
11
        "data"  : df["revenue"].tolist()
12
    }
13

14
result = get_monthly_chart()
15
print(result)

코드 설명:

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

7. 문제풀기

sale_dt 문자열의 1번째 위치부터 7글자를 추출한다. ‘2024-08-15’ → ‘2024-08’ 월별 집계에 사용한다.

두 컬럼의 조합이 같은 행들끼리 묶인다. GROUP BY 월, category는 같은 월이면서 같은 카테고리인 행들을 하나의 그룹으로 처리한다.

단가(amount)에 수량(qty)을 곱해야 실제 매출금액이 되기 때문이다. qty가 없으면 단가의 합계만 구해진다.

SQL GROUP BY 결과를 행과 열이 교차하는 표 형태로 변환할 때 사용한다. 예: 월(행) × 카테고리(열) × 매출(값) 구조의 교차표를 만들 때 사용한다.