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. 주요 집계 함수
| 함수 | 의미 |
|---|---|
| COUNT(컬럼) | 행의 수를 센다 |
| SUM(컬럼) | 합계를 구한다 |
| AVG(컬럼) | 평균을 구한다 |
| MAX(컬럼) | 최대값을 구한다 |
| MIN(컬럼) | 최소값을 구한다 |
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. 문제풀기
① GROUP BY에 명시한 컬럼
② 집계 함수 (COUNT, SUM, AVG, MAX, MIN)
이 두 가지 외의 컬럼은 SELECT에 직접 쓸 수 없다.
WHERE: GROUP BY 실행 전에 개별 행을 필터링한다. 집계 함수 사용 불가.
HAVING: GROUP BY 실행 후에 그룹을 필터링한다. 집계 함수 사용 가능.
SELECT genre, COUNT(*) AS cnt
FROM games
GROUP BY genre
HAVING COUNT(*) >= 3;
rating의 평균을 구한 후 소수점 첫째 자리까지 반올림한다.
예: 4.433… → 4.4