13_SQL GROUP BY 실습 1

1. 인트로

GROUP BY(그룹 바이)는 같은 값을 가진 행들을 묶어 집계 함수로 통계를 구하는 기능이다. "장르별 게임 수", "월별 매출", "카테고리별 평균 가격" 같은 통계 데이터를 만들 때 반드시 필요하다.

---

2. 실습 DB 준비 (7차시 game_store.db 이어서 사용)

import sqlite3
import pandas as pd

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

# 데이터 확인
df = pd.read_sql("SELECT * FROM games", conn)
print(df[["title","genre","platform","price","rating"]])

---

3. GROUP BY(그룹 바이) 명령어 실습

3.1. GROUP BY 기본 개념

GROUP BY는 특정 컬럼의 값이 같은 행들을 하나의 그룹으로 묶는다.

플랫폼이 'PC'인 행들  → PC 그룹
플랫폼이 'PS5'인 행들 → PS5 그룹
플랫폼이 'Switch'인 행들 → Switch 그룹

각 그룹에 COUNT(), AVG(), SUM(), MAX(), MIN() 같은 집계 함수를 적용한다.

3.2. 주요 집계 함수

3.3. 플랫폼별 게임 수 집계

df = pd.read_sql("""
    SELECT platform       AS 플랫폼,
           COUNT(id)      AS 게임수
    FROM games
    GROUP BY platform
    ORDER BY 게임수 DESC
""", conn)
print("=== 플랫폼별 게임 수 ===")
print(df)

코드 설명:

• 3행: COUNT(id) — 그룹 내 행의 수를 센다.
• 5행: GROUP BY platform — platform 값이 같은 행끼리 묶는다.
• SELECT에는 GROUP BY 기준 컬럼과 집계 함수만 사용할 수 있다.

3.4. 장르별 평균 평점

df = pd.read_sql("""
    SELECT genre          AS 장르,
           COUNT(id)      AS 게임수,
           ROUND(AVG(rating), 2) AS 평균평점,
           MAX(rating)    AS 최고평점
    FROM games
    GROUP BY genre
    ORDER BY 평균평점 DESC
""", conn)
print("=== 장르별 통계 ===")
print(df)

코드 설명:

• 4행: ROUND(AVG(rating), 2) — 평균 평점을 소수점 둘째 자리까지 반올림한다.
• ROUND(값, 자릿수) — 지정한 자릿수로 반올림한다.

---

4. HAVING(해빙) 명령어 실습

4.1. HAVING이란?

WHERE는 개별 행을 필터링한다. HAVING은 GROUP BY로 묶인 그룹을 필터링한다.

WHERE  → 그룹화 전에 행을 필터링
HAVING → 그룹화 후에 그룹을 필터링

4.2. 게임이 2개 이상인 플랫폼만 조회

df = pd.read_sql("""
    SELECT platform  AS 플랫폼,
           COUNT(id) AS 게임수
    FROM games
    GROUP BY platform
    HAVING COUNT(id) >= 2
    ORDER BY 게임수 DESC
""", conn)
print("=== 게임 2개 이상 플랫폼 ===")
print(df)

코드 설명:

• 6행: HAVING COUNT(id) >= 2 — 그룹화 후 게임 수가 2개 이상인 그룹만 남긴다.
• WHERE에는 집계 함수를 쓸 수 없으나 HAVING에는 쓸 수 있다.

4.3. HAVING 명령어 실습 — 평균 평점 4.5 이상인 장르

df = pd.read_sql("""
    SELECT genre              AS 장르,
           COUNT(id)          AS 게임수,
           ROUND(AVG(rating), 1) AS 평균평점
    FROM games
    GROUP BY genre
    HAVING AVG(rating) >= 4.5
    ORDER BY 평균평점 DESC
""", conn)
print("=== 평균 평점 4.5+ 장르 ===")
print(df)

---

5. GROUP BY & JOIN 동시 적용 실습

# JOIN + GROUP BY: 플랫폼별 유료 게임의 평균 가격과 최고 평점
df = pd.read_sql("""
    SELECT platform          AS 플랫폼,
           COUNT(id)         AS 유료게임수,
           ROUND(AVG(price), 0) AS 평균가격,
           MAX(rating)       AS 최고평점
    FROM games
    WHERE price > 0
    GROUP BY platform
    HAVING COUNT(id) >= 1
    ORDER BY 평균가격 DESC
""", conn)
print("=== 플랫폼별 유료게임 통계 ===")
print(df)

코드 설명:

• 8행: WHERE price > 0 — 그룹화 전에 무료 게임을 제외한다.
• 10행: HAVING COUNT(id) >= 1 — 유료 게임이 1개 이상인 플랫폼만 표시한다.
• WHERE와 HAVING이 함께 쓰일 때 실행 순서: WHERE 먼저 → GROUP BY → HAVING

---

6. 활용 Plus — 리액트 대시보드 연동

리액트 게임 스토어 앱의 "통계 대시보드" 페이지에서 파이썬 API가 반환하는 데이터 예시이다.

def get_stats():
    stats = {}

    # 플랫폼별 게임 수
    df1 = pd.read_sql(
        "SELECT platform, COUNT(*) AS cnt FROM games GROUP BY platform",
        conn
    )
    stats["by_platform"] = df1.to_dict("records")

    # 장르별 평균 평점
    df2 = pd.read_sql(
        "SELECT genre, ROUND(AVG(rating),2) AS avg_r FROM games GROUP BY genre",
        conn
    )
    stats["by_genre"] = df2.to_dict("records")

    return stats

import json
print(json.dumps(get_stats(), ensure_ascii=False, indent=2))

코드 설명:

• 2행: 여러 통계를 딕셔너리에 담아 한 번에 반환한다.
• 21행: json.dumps()로 JSON 문자열로 변환하여 출력한다. 리액트 API 응답 형태이다.

---

7. 문제풀기