24강. 최종 프로젝트: 게임 기록·랭킹 데이터베이스 설계와 분석 쿼리

?1. 이번 강의에서 해결할 문제

지금까지의 스키마와 분석 쿼리를 새 데이터베이스에서 처음부터 재현 가능한 최종 결과물로 묶습니다. 데이터 무결성, 분석 요구, 안전한 쓰기와 성능 확인을 함께 충족합니다.

✓2. 학습 목표

✓이 강의를 마치면

• 네 테이블과 Primary Key·Foreign Key를 한 스키마로 만듭니다.
• 플레이어 승률, 캐릭터 평균 점수, 기간별 경기 수와 상위 랭킹을 조회합니다.
• 트랜잭션과 인덱스 계획을 검증합니다.

◇3. 핵심 개념

최종 프로젝트는 스키마 → seed → 무결성 확인 → 분석 SELECT → 트랜잭션 쓰기 → 계획 비교 순서로 실행합니다. 파괴적인 DROP으로 초기화하지 않고 새 final_game_ranking.db 파일을 만들어 기존 실습 DB를 보존합니다.

최종 관계

players 1 ── N match_results N ── 1 games
                         1
                         │
                         1
                  character_stats

§4. 단계별 SQL 예시

4-1. 스키마

대상: 새 DB의 players, games, match_results, character_stats
파일 경로: C:\dev\game-ranking-db\final\01-schema.sql

final/01-schema.sql

PRAGMA foreign_keys = ON;

CREATE TABLE players (
    player_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    region TEXT NOT NULL CHECK (region IN ('KR', 'JP', 'NA', 'EU')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

CREATE TABLE games (
    game_id INTEGER PRIMARY KEY,
    mode TEXT NOT NULL CHECK (mode IN ('ranked', 'normal')),
    started_at TEXT NOT NULL,
    duration_seconds INTEGER NOT NULL CHECK (duration_seconds > 0)
) STRICT;

CREATE TABLE match_results (
    result_id INTEGER PRIMARY KEY,
    game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE RESTRICT,
    player_id INTEGER NOT NULL REFERENCES players(player_id) ON DELETE RESTRICT,
    score INTEGER NOT NULL CHECK (score >= 0),
    result TEXT NOT NULL CHECK (result IN ('win', 'loss', 'draw')),
    rank_position INTEGER NOT NULL CHECK (rank_position > 0),
    UNIQUE (game_id, player_id)
) STRICT;

CREATE TABLE character_stats (
    stat_id INTEGER PRIMARY KEY,
    result_id INTEGER NOT NULL UNIQUE
        REFERENCES match_results(result_id) ON DELETE CASCADE,
    character_name TEXT NOT NULL,
    damage_dealt INTEGER NOT NULL CHECK (damage_dealt >= 0),
    healing_done INTEGER NOT NULL DEFAULT 0 CHECK (healing_done >= 0)
) STRICT;

4-2. 샘플 데이터

파일 경로: C:\dev\game-ranking-db\final\02-seed.sql

final/02-seed.sql

PRAGMA foreign_keys = ON;
BEGIN;

INSERT INTO players (player_id, username, region) VALUES
    (1, 'knight_01', 'KR'), (2, 'mage_02', 'JP'),
    (3, 'archer_03', 'NA'), (4, 'healer_04', 'EU'),
    (5, 'rookie_05', 'KR');

INSERT INTO games (game_id, mode, started_at, duration_seconds) VALUES
    (1, 'ranked', '2026-06-28T10:00:00Z', 420),
    (2, 'normal', '2026-07-02T12:30:00Z', 305),
    (3, 'ranked', '2026-07-03T09:15:00Z', 510),
    (4, 'ranked', '2026-07-12T15:45:00Z', 460);

INSERT INTO match_results
    (result_id, game_id, player_id, score, result, rank_position) VALUES
    (1, 1, 1, 1250, 'win', 1), (2, 1, 2, 910, 'loss', 2),
    (3, 2, 1, 800, 'loss', 2), (4, 2, 3, 1100, 'win', 1),
    (5, 3, 2, 1400, 'win', 1), (6, 3, 4, 1000, 'loss', 2),
    (7, 4, 1, 1320, 'win', 1), (8, 4, 3, 1150, 'loss', 2);

INSERT INTO character_stats
    (result_id, character_name, damage_dealt, healing_done) VALUES
    (1, 'Knight', 5200, 300), (2, 'Mage', 4800, 0),
    (3, 'Knight', 3900, 250), (4, 'Archer', 6100, 0),
    (5, 'Mage', 6800, 0), (6, 'Priest', 2200, 4900),
    (7, 'Knight', 6400, 400), (8, 'Archer', 5700, 0);

COMMIT;
PRAGMA foreign_key_check;

4-3. 필수 분석 쿼리

대상: 네 테이블 읽기 전용
파일 경로: C:\dev\game-ranking-db\final\03-analysis.sql

final/03-analysis.sql

-- 플레이어별 승률
SELECT
    p.username,
    COUNT(mr.result_id) AS battle_count,
    ROUND(
        COALESCE(
            1.0 * SUM(CASE WHEN mr.result = 'win' THEN 1 ELSE 0 END)
            / NULLIF(COUNT(mr.result_id), 0),
            0
        ),
        4
    ) AS win_rate
FROM players AS p
LEFT JOIN match_results AS mr ON mr.player_id = p.player_id
GROUP BY p.player_id, p.username
ORDER BY win_rate DESC, battle_count DESC, p.player_id;

-- 캐릭터별 평균 점수
SELECT
    cs.character_name,
    COUNT(*) AS battle_count,
    ROUND(AVG(mr.score), 1) AS average_score
FROM character_stats AS cs
JOIN match_results AS mr ON mr.result_id = cs.result_id
GROUP BY cs.character_name
ORDER BY average_score DESC, cs.character_name;

-- 기간별 경기 수: 반열린 구간으로 2026년 7월 조회
SELECT
    strftime('%Y-%m', started_at) AS period,
    COUNT(*) AS game_count
FROM games
WHERE started_at >= '2026-07-01T00:00:00Z'
  AND started_at < '2026-08-01T00:00:00Z'
GROUP BY strftime('%Y-%m', started_at);

-- 상위 랭킹: 승률, 평균 점수, 고유 키로 안정 정렬
WITH ranking AS (
    SELECT
        p.player_id,
        p.username,
        COUNT(mr.result_id) AS battle_count,
        COALESCE(
            1.0 * SUM(CASE WHEN mr.result = 'win' THEN 1 ELSE 0 END)
            / NULLIF(COUNT(mr.result_id), 0),
            0
        ) AS win_rate,
        COALESCE(AVG(mr.score), 0) AS average_score
    FROM players AS p
    LEFT JOIN match_results AS mr ON mr.player_id = p.player_id
    GROUP BY p.player_id, p.username
)
SELECT username, battle_count,
       ROUND(win_rate * 100, 1) AS win_rate_percent,
       ROUND(average_score, 1) AS average_score
FROM ranking
ORDER BY win_rate DESC, average_score DESC, player_id
LIMIT 10;

4-4. 원자적 경기 기록과 인덱스 계획

파일 경로: C:\dev\game-ranking-db\final\04-transaction-index.sql

final/04-transaction-index.sql

PRAGMA foreign_keys = ON;
BEGIN IMMEDIATE;
INSERT INTO match_results (game_id, player_id, score, result, rank_position)
VALUES (3, 3, 1180, 'win', 1);
INSERT INTO character_stats (result_id, character_name, damage_dealt, healing_done)
VALUES (last_insert_rowid(), 'Archer', 5900, 0);
COMMIT;

-- 인덱스 전 계획을 먼저 기록
EXPLAIN QUERY PLAN
SELECT game_id FROM games
WHERE started_at >= '2026-07-01T00:00:00Z'
  AND started_at < '2026-08-01T00:00:00Z';

CREATE INDEX idx_games_started_at ON games (started_at);
CREATE INDEX idx_match_results_player_game ON match_results (player_id, game_id);

-- 같은 쿼리에서 SCAN과 SEARCH·인덱스 이름 비교
EXPLAIN QUERY PLAN
SELECT game_id FROM games
WHERE started_at >= '2026-07-01T00:00:00Z'
  AND started_at < '2026-08-01T00:00:00Z';

PowerShell · 최종 실행 순서

Set-Location C:\dev\game-ranking-db
New-Item -ItemType Directory -Force .\final, .\data
sqlite3 .\data\final_game_ranking.db ".read .\final\01-schema.sql"
sqlite3 .\data\final_game_ranking.db ".read .\final\02-seed.sql"
sqlite3 -header -box .\data\final_game_ranking.db ".read .\final\03-analysis.sql"
sqlite3 -header -box .\data\final_game_ranking.db ".read .\final\04-transaction-index.sql"
sqlite3 .\data\final_game_ranking.db "PRAGMA foreign_key_check; PRAGMA integrity_check;"

→5. 쿼리가 동작하는 이유

네 테이블은 계정, 경기, 참여 결과, 캐릭터 지표를 한 곳씩 저장합니다. LEFT JOIN은 미참여자를 보존하고 NULLIF가 0 나눗셈을 막습니다. 반열린 날짜 범위는 월말 시각 정밀도 문제를 피합니다. 트랜잭션은 결과와 통계를 함께 저장하고 인덱스는 기간·플레이어 접근 경로를 제공합니다.

!6. 자주 하는 실수와 안전한 해결법

!자주 하는 실수

• 기존 DB를 DROP으로 초기화: 모든 데이터가 사라집니다. 새 파일에서 최종 스크립트를 실행해 기존 DB를 보존합니다.
• UPDATE·DELETE를 검증 없이 추가: 같은 WHERE의 SELECT와 COUNT를 먼저 실행하고 트랜잭션 안에서 결과를 확인합니다.
• 승률 분모 0 처리 누락: 미참여자의 NULL·0 정책을 명시하고 NULLIF·COALESCE를 사용합니다.
• 인덱스 효과를 실행 시간 한 번으로 판단: 전후 EXPLAIN 계획과 실제 규모 측정을 함께 비교합니다.

✎7. 직접 실습

✎최종 완료 조건

1. 새 DB에 네 테이블과 모든 PK·FK·제약을 생성합니다.
2. 샘플 데이터와 외래 키 검사를 통과시킵니다.
3. 플레이어 승률, 캐릭터 평균 점수, 기간별 경기 수, 상위 랭킹을 손계산과 비교합니다.
4. 결과·캐릭터 통계를 한 트랜잭션으로 저장하고 실패 시 ROLLBACK을 시험합니다.
5. 인덱스 적용 전후 계획의 SCAN·SEARCH 차이를 기록합니다.
6. FastAPI에서 사용할 읽기 쿼리와 쓰기 트랜잭션 경계를 README에 정리합니다.

?8. 이해 점검 질문 3개

?이해 점검

1. 네 테이블 각각이 한 곳에서만 저장하는 사실은 무엇인가요?
2. 7월 기간 조회를 >= 7월 1일 AND < 8월 1일로 쓰는 이유는 무엇인가요?
3. 경기 결과와 캐릭터 통계를 한 트랜잭션으로 넣어야 하는 이유는 무엇인가요?

■9. 핵심 요약

■핵심 요약

게임 기록·랭킹 데이터베이스를 관계형 스키마, 샘플 데이터, 필수 분석, 트랜잭션과 인덱스까지 재현 가능한 SQL 파일로 완성했습니다. 같은 설계는 자료형과 운영 차이를 조정해 FastAPI·PostgreSQL로 확장할 수 있습니다.