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

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

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

✓2. 학습 목표

✓이 강의를 마치면

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

◇3. 핵심 개념

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

시작 전 상태와 산출물

확인 항목	시작 조건	완료 증거
대상 DB	final_game_ranking.db가 없는 새 경로	기존 실습 DB와 별도 파일
스키마	테이블 없음	.tables, .schema, FK 검사 통과
seed	저장 행 없음	players 5, games 4, results 8, stats 8
분석	실행 전 기대값을 손으로 계산	네 결과 표와 계산 일치
쓰기	game 3/player 3 결과 없음	결과·캐릭터 통계가 함께 1행씩 증가
인덱스	기간 인덱스 없음	전후 EXPLAIN QUERY PLAN 기록

실행 전에 Test-Path .\data\final_game_ranking.db로 같은 이름의 파일이 있는지 확인합니다. 이미 있다면 삭제하거나 덮어쓰지 말고 날짜가 포함된 새 학습 경로를 사용합니다.

구현 순서가 중요한 이유

1. 스키마: 허용할 데이터 모양과 제약을 먼저 만듭니다.
2. seed: 부모인 players·games를 만든 뒤 자식인 match_results·character_stats를 넣습니다.
3. 무결성 검사: 분석 전에 FK 위반이 없는지 확인합니다.
4. 분석: 쓰기 실험 전의 고정된 8개 결과로 손계산과 비교합니다.
5. 트랜잭션: 두 테이블에 함께 저장하고 일부 실패가 남지 않는지 확인합니다.
6. 인덱스: 같은 조회의 계획을 전후로 비교합니다. 쿼리를 바꾸면 인덱스 효과 비교가 아닙니다.

최종 관계

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

match_results는 플레이어와 경기의 다대다 관계를 참가 결과라는 행으로 풉니다. character_stats.result_id가 UNIQUE이므로 한 참가 결과에는 캐릭터 통계가 최대 한 행만 연결됩니다.

§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;

스키마의 핵심 줄 해설

• PRAGMA foreign_keys = ON은 연결마다 FK 검사를 켭니다. 새 sqlite3 명령을 실행할 때 다시 설정합니다.
• 각 PRIMARY KEY는 행을 식별하고, UNIQUE (game_id, player_id)는 한 플레이어의 같은 경기 결과 중복을 막습니다.
• CHECK는 지역·모드·승패처럼 허용할 값과 음수가 될 수 없는 수치를 저장 경계에서 검사합니다.
• ON DELETE RESTRICT는 결과가 참조하는 플레이어·경기를 실수로 지우지 못하게 합니다.
• ON DELETE CASCADE는 참가 결과를 의도적으로 삭제할 때 그 결과에만 속한 character_stats가 고아 행으로 남지 않게 합니다.

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;

seed 값 흐름 검산

부모 테이블을 먼저 넣었기 때문에 모든 result의 game_id와 player_id가 존재합니다. result_id 1~8도 character_stats에서 정확히 한 번씩 사용됩니다.

행 수와 FK 확인

SELECT 'players' AS table_name, COUNT(*) AS row_count FROM players
UNION ALL SELECT 'games', COUNT(*) FROM games
UNION ALL SELECT 'match_results', COUNT(*) FROM match_results
UNION ALL SELECT 'character_stats', COUNT(*) FROM character_stats;
PRAGMA foreign_key_check;

예상 행 수는 차례로 5, 4, 8, 8이며 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;

분석 쿼리의 핵심과 손계산

• 승률 분자는 win인 행 수, 분모는 해당 플레이어의 전체 result 수입니다. LEFT JOIN 때문에 경기 없는 rookie_05도 남고 NULLIF·COALESCE로 0이 됩니다.
• 캐릭터 평균은 character_stats에서 result_id로 실제 점수 행을 JOIN한 뒤 캐릭터별로 묶습니다.
• 7월 구간은 7월 1일 포함, 8월 1일 미포함이라 월말의 시·분·초 정밀도와 관계없이 안전합니다.
• 랭킹 정렬은 승률 → 평균 점수 → player_id 순서라 동률이어도 결과가 안정적입니다.

seed 8개 결과로 손계산한 핵심 값은 다음과 같습니다.

플레이어별 승률과 랭킹

knight_01  3전 2승  66.7%  평균 1123.3
mage_02    2전 1승  50.0%  평균 1155.0
archer_03  2전 1승  50.0%  평균 1125.0
healer_04  1전 0승   0.0%  평균 1000.0
rookie_05  0전 0승   0.0%  평균    0.0

캐릭터별 평균 점수

Mage    (910 + 1400) / 2 = 1155.0
Archer  (1100 + 1150) / 2 = 1125.0
Knight  (1250 + 800 + 1320) / 3 = 1123.3
Priest  1000 / 1 = 1000.0

7월 경기에는 game_id 2, 3, 4가 포함되므로 game_count는 3입니다.

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';

트랜잭션과 계획 결과 읽기

첫 INSERT가 새 match_result를 만들고 last_insert_rowid()가 같은 연결에서 방금 생성된 result_id를 두 번째 INSERT에 전달합니다. 둘 다 성공해야 COMMIT합니다. 정상 실행 후 result와 character_stats는 각각 9행이며 game 3/player 3의 score는 1180입니다.

실패를 안전하게 확인하려면 새 테스트 조합으로 일부 성공 뒤 명시적으로 ROLLBACK합니다.

final/05-rollback-lab.sql

PRAGMA foreign_keys = ON;
BEGIN;
INSERT INTO match_results (game_id, player_id, score, result, rank_position)
VALUES (2, 2, 900, 'loss', 3);

-- CHECK 위반을 의도적으로 재현합니다.
INSERT INTO character_stats (result_id, character_name, damage_dealt, healing_done)
VALUES (last_insert_rowid(), 'Mage', -1, 0);

ROLLBACK;
SELECT COUNT(*) AS should_be_zero
FROM match_results
WHERE game_id = 2 AND player_id = 2;

SQLite의 제약 실패는 상황에 따라 현재 문장만 중단할 수 있으므로 실패 뒤 자동 COMMIT을 기대하지 않습니다. 학습 스크립트에서 ROLLBACK을 실행하고 결과가 0인지 확인합니다.

작은 seed에서는 옵티마이저가 인덱스 생성 후에도 전체 스캔을 선택할 수 있습니다. 계획에서 인덱스 이름이 보이지 않는다고 즉시 실패로 단정하지 말고, 쿼리·통계·데이터 규모를 기록한 뒤 더 현실적인 데이터에서 다시 측정합니다.

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. 직접 실습

✎최종 완료 조건

실습 목표

빈 SQLite 파일에서 스키마·seed·분석·트랜잭션·인덱스를 순서대로 재현하고, 표의 숫자를 손계산으로 검증합니다.

시작 전 상태

기존 DB와 다른 새 파일 경로를 정하고 네 SQL 파일의 실행 순서를 README에 적습니다. 파일 존재 여부와 현재 작업 변경을 확인한 뒤 시작합니다.

1단계: 따라 하기

스키마와 seed를 실행합니다. 네 테이블 행 수 5·4·8·8, FK 검사 출력 없음, integrity_check ok를 확인한 뒤 분석 쿼리 결과를 제공된 손계산과 비교합니다.

2단계: 값과 실패를 바꿔 보기

복사한 seed에서 knight_01의 result_id 3을 loss에서 win으로 바꾸면 승률이 3전 3승으로 달라질 것을 먼저 예측한 뒤 별도 DB에서 확인합니다. 원본 seed는 유지합니다. 이어서 rollback 실험으로 game 2/player 2 결과가 0행인지 확인합니다.

3단계: 직접 확장

무승부를 0.5승으로 계산하는 별도 분석 쿼리와 모드별 승률 쿼리를 작성합니다. 기존 승률 정의를 몰래 바꾸지 말고 지표 이름과 분자 규칙을 README에 명시합니다.

4단계: 스스로 확인

• 새 DB에서 순서대로 다시 실행해 같은 결과가 나오나요?
• 미참여자와 분모 0 정책을 설명할 수 있나요?
• 두 INSERT 중 하나가 실패해도 고아 결과가 남지 않나요?
• 인덱스 전후에 같은 쿼리와 조건을 비교했나요?

막혔을 때

• table already exists: 기존 DB를 DROP하지 말고 새 파일 경로인지 확인합니다.
• FK 오류: PRAGMA foreign_keys, 부모 seed 실행 순서, 실제 ID를 확인합니다.
• 승률이 정수 0 또는 1만 나옴: 분자에 1.0 *을 사용해 실수 나눗셈을 유도합니다.
• last_insert_rowid가 잘못 연결됨: 두 INSERT를 같은 sqlite3 연결과 트랜잭션에서 실행합니다.
• 인덱스 계획이 SCAN: 작은 데이터에서는 합리적일 수 있으므로 데이터 규모와 ANALYZE 여부를 기록합니다.

?8. 이해 점검 질문 3개

?이해 점검

1. 네 테이블 각각이 한 곳에서만 저장하는 사실은 무엇인가요?
2. 7월 기간 조회를 >= 7월 1일 AND < 8월 1일로 쓰는 이유는 무엇인가요?
3. 경기 결과 INSERT는 성공했지만 캐릭터 통계의 음수 CHECK가 실패했을 때 어떤 행이 남아야 하며, 이를 어떻게 확인하나요?

■9. 핵심 요약

■핵심 요약

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

§다음 학습 연결

SQLite로 관계와 분석을 재현했습니다. 서버 연결·동시성·운영 복구까지 확장하려면 실제 문서인 데이터베이스 기초 1강. DBMS의 역할로 이어갑니다.