13 SQL GROUP BY 실습 1

1. 인트로

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

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

1
import sqlite3
2
import pandas as pd
3

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

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

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

3.1. GROUP BY 기본 개념

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

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

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

3.2. 주요 집계 함수

함수	의미
COUNT(컬럼)	행의 수를 센다
SUM(컬럼)	합계를 구한다
AVG(컬럼)	평균을 구한다
MAX(컬럼)	최대값을 구한다
MIN(컬럼)	최소값을 구한다

3.3. 플랫폼별 게임 수 집계

1
df = pd.read_sql("""
2
    SELECT platform       AS 플랫폼,
3
           COUNT(id)      AS 게임수
4
    FROM games
5
    GROUP BY platform
6
    ORDER BY 게임수 DESC
7
""", conn)
8
print("=== 플랫폼별 게임 수 ===")
9
print(df)

코드 설명:

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

3.4. 장르별 평균 평점

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

코드 설명:

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

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

4.1. HAVING이란?

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

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

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

1
df = pd.read_sql("""
2
    SELECT platform  AS 플랫폼,
3
           COUNT(id) AS 게임수
4
    FROM games
5
    GROUP BY platform
6
    HAVING COUNT(id) >= 2
7
    ORDER BY 게임수 DESC
8
""", conn)
9
print("=== 게임 2개 이상 플랫폼 ===")
10
print(df)

코드 설명:

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

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

1
df = pd.read_sql("""
2
    SELECT genre              AS 장르,
3
           COUNT(id)          AS 게임수,
4
           ROUND(AVG(rating), 1) AS 평균평점
5
    FROM games
6
    GROUP BY genre
7
    HAVING AVG(rating) >= 4.5
8
    ORDER BY 평균평점 DESC
9
""", conn)
10
print("=== 평균 평점 4.5+ 장르 ===")
11
print(df)

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

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

코드 설명:

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

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

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

1
def get_stats():
2
    stats = {}
3

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

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

18
    return stats
19

20
import json
21
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