395 lines
16 KiB
MySQL
395 lines
16 KiB
MySQL
|
-- ============================================================================
|
||
|
|
-- L3 Schema: Player Features Data Mart (Version 2.0)
|
||
|
|
-- ============================================================================
|
||
|
|
-- Based on: L3_ARCHITECTURE_PLAN.md
|
||
|
|
-- Design: 5-Tier Feature Hierarchy (CORE → TACTICAL → INTELLIGENCE → META → COMPOSITE)
|
||
|
|
-- Granularity: One row per player (Aggregated Profile)
|
||
|
|
-- Total Columns: 207 features + 6 metadata = 213 columns
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- Main Table: dm_player_features
|
||
|
|
-- ============================================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS dm_player_features (
|
||
|
|
-- ========================================================================
|
||
|
|
-- Metadata (6 columns)
|
||
|
|
-- ========================================================================
|
||
|
|
steam_id_64 TEXT PRIMARY KEY,
|
||
|
|
total_matches INTEGER NOT NULL DEFAULT 0,
|
||
|
|
total_rounds INTEGER NOT NULL DEFAULT 0,
|
||
|
|
first_match_date INTEGER, -- Unix timestamp
|
||
|
|
last_match_date INTEGER, -- Unix timestamp
|
||
|
|
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
-- ========================================================================
|
||
|
|
-- TIER 1: CORE (41 columns)
|
||
|
|
-- Direct aggregations from fact_match_players
|
||
|
|
-- ========================================================================
|
||
|
|
|
||
|
|
-- Basic Performance (15 columns)
|
||
|
|
core_avg_rating REAL DEFAULT 0.0,
|
||
|
|
core_avg_rating2 REAL DEFAULT 0.0,
|
||
|
|
core_avg_kd REAL DEFAULT 0.0,
|
||
|
|
core_avg_adr REAL DEFAULT 0.0,
|
||
|
|
core_avg_kast REAL DEFAULT 0.0,
|
||
|
|
core_avg_rws REAL DEFAULT 0.0,
|
||
|
|
core_avg_hs_kills REAL DEFAULT 0.0,
|
||
|
|
core_hs_rate REAL DEFAULT 0.0, -- hs/total_kills
|
||
|
|
core_total_kills INTEGER DEFAULT 0,
|
||
|
|
core_total_deaths INTEGER DEFAULT 0,
|
||
|
|
core_total_assists INTEGER DEFAULT 0,
|
||
|
|
core_avg_assists REAL DEFAULT 0.0,
|
||
|
|
core_kpr REAL DEFAULT 0.0, -- kills per round
|
||
|
|
core_dpr REAL DEFAULT 0.0, -- deaths per round
|
||
|
|
core_survival_rate REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Match Stats (8 columns)
|
||
|
|
core_win_rate REAL DEFAULT 0.0,
|
||
|
|
core_wins INTEGER DEFAULT 0,
|
||
|
|
core_losses INTEGER DEFAULT 0,
|
||
|
|
core_avg_match_duration INTEGER DEFAULT 0, -- seconds
|
||
|
|
core_avg_mvps REAL DEFAULT 0.0,
|
||
|
|
core_mvp_rate REAL DEFAULT 0.0,
|
||
|
|
core_avg_elo_change REAL DEFAULT 0.0,
|
||
|
|
core_total_elo_gained REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Weapon Stats (12 columns)
|
||
|
|
core_avg_awp_kills REAL DEFAULT 0.0,
|
||
|
|
core_awp_usage_rate REAL DEFAULT 0.0,
|
||
|
|
core_avg_knife_kills REAL DEFAULT 0.0,
|
||
|
|
core_avg_zeus_kills REAL DEFAULT 0.0,
|
||
|
|
core_zeus_buy_rate REAL DEFAULT 0.0,
|
||
|
|
core_top_weapon TEXT,
|
||
|
|
core_top_weapon_kills INTEGER DEFAULT 0,
|
||
|
|
core_top_weapon_hs_rate REAL DEFAULT 0.0,
|
||
|
|
core_weapon_diversity REAL DEFAULT 0.0,
|
||
|
|
core_rifle_hs_rate REAL DEFAULT 0.0,
|
||
|
|
core_pistol_hs_rate REAL DEFAULT 0.0,
|
||
|
|
core_smg_kills_total INTEGER DEFAULT 0,
|
||
|
|
|
||
|
|
-- Objective Stats (6 columns)
|
||
|
|
core_avg_plants REAL DEFAULT 0.0,
|
||
|
|
core_avg_defuses REAL DEFAULT 0.0,
|
||
|
|
core_avg_flash_assists REAL DEFAULT 0.0,
|
||
|
|
core_plant_success_rate REAL DEFAULT 0.0,
|
||
|
|
core_defuse_success_rate REAL DEFAULT 0.0,
|
||
|
|
core_objective_impact REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- ========================================================================
|
||
|
|
-- TIER 2: TACTICAL (44 columns)
|
||
|
|
-- Multi-table JOINs, conditional aggregations
|
||
|
|
-- ========================================================================
|
||
|
|
|
||
|
|
-- Opening Impact (8 columns)
|
||
|
|
tac_avg_fk REAL DEFAULT 0.0,
|
||
|
|
tac_avg_fd REAL DEFAULT 0.0,
|
||
|
|
tac_fk_rate REAL DEFAULT 0.0,
|
||
|
|
tac_fd_rate REAL DEFAULT 0.0,
|
||
|
|
tac_fk_success_rate REAL DEFAULT 0.0,
|
||
|
|
tac_entry_kill_rate REAL DEFAULT 0.0,
|
||
|
|
tac_entry_death_rate REAL DEFAULT 0.0,
|
||
|
|
tac_opening_duel_winrate REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Multi-Kill (6 columns)
|
||
|
|
tac_avg_2k REAL DEFAULT 0.0,
|
||
|
|
tac_avg_3k REAL DEFAULT 0.0,
|
||
|
|
tac_avg_4k REAL DEFAULT 0.0,
|
||
|
|
tac_avg_5k REAL DEFAULT 0.0,
|
||
|
|
tac_multikill_rate REAL DEFAULT 0.0,
|
||
|
|
tac_ace_count INTEGER DEFAULT 0,
|
||
|
|
|
||
|
|
-- Clutch Performance (10 columns)
|
||
|
|
tac_clutch_1v1_attempts INTEGER DEFAULT 0,
|
||
|
|
tac_clutch_1v1_wins INTEGER DEFAULT 0,
|
||
|
|
tac_clutch_1v1_rate REAL DEFAULT 0.0,
|
||
|
|
tac_clutch_1v2_attempts INTEGER DEFAULT 0,
|
||
|
|
tac_clutch_1v2_wins INTEGER DEFAULT 0,
|
||
|
|
tac_clutch_1v2_rate REAL DEFAULT 0.0,
|
||
|
|
tac_clutch_1v3_plus_attempts INTEGER DEFAULT 0,
|
||
|
|
tac_clutch_1v3_plus_wins INTEGER DEFAULT 0,
|
||
|
|
tac_clutch_1v3_plus_rate REAL DEFAULT 0.0,
|
||
|
|
tac_clutch_impact_score REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Utility Mastery (13 columns)
|
||
|
|
tac_util_flash_per_round REAL DEFAULT 0.0,
|
||
|
|
tac_util_smoke_per_round REAL DEFAULT 0.0,
|
||
|
|
tac_util_molotov_per_round REAL DEFAULT 0.0,
|
||
|
|
tac_util_he_per_round REAL DEFAULT 0.0,
|
||
|
|
tac_util_usage_rate REAL DEFAULT 0.0,
|
||
|
|
tac_util_nade_dmg_per_round REAL DEFAULT 0.0,
|
||
|
|
tac_util_nade_dmg_per_nade REAL DEFAULT 0.0,
|
||
|
|
tac_util_flash_time_per_round REAL DEFAULT 0.0,
|
||
|
|
tac_util_flash_enemies_per_round REAL DEFAULT 0.0,
|
||
|
|
tac_util_flash_efficiency REAL DEFAULT 0.0,
|
||
|
|
tac_util_impact_score REAL DEFAULT 0.0,
|
||
|
|
tac_util_zeus_equipped_count INTEGER DEFAULT 0,
|
||
|
|
-- tac_util_zeus_kills REMOVED
|
||
|
|
|
||
|
|
-- Economy Efficiency (8 columns)
|
||
|
|
tac_eco_dmg_per_1k REAL DEFAULT 0.0,
|
||
|
|
tac_eco_kpr_eco_rounds REAL DEFAULT 0.0,
|
||
|
|
tac_eco_kd_eco_rounds REAL DEFAULT 0.0,
|
||
|
|
tac_eco_kpr_force_rounds REAL DEFAULT 0.0,
|
||
|
|
tac_eco_kpr_full_rounds REAL DEFAULT 0.0,
|
||
|
|
tac_eco_save_discipline REAL DEFAULT 0.0,
|
||
|
|
tac_eco_force_success_rate REAL DEFAULT 0.0,
|
||
|
|
tac_eco_efficiency_score REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- ========================================================================
|
||
|
|
-- TIER 3: INTELLIGENCE (53 columns)
|
||
|
|
-- Advanced analytics on fact_round_events
|
||
|
|
-- ========================================================================
|
||
|
|
|
||
|
|
-- High IQ Kills (9 columns)
|
||
|
|
int_wallbang_kills INTEGER DEFAULT 0,
|
||
|
|
int_wallbang_rate REAL DEFAULT 0.0,
|
||
|
|
int_smoke_kills INTEGER DEFAULT 0,
|
||
|
|
int_smoke_kill_rate REAL DEFAULT 0.0,
|
||
|
|
int_blind_kills INTEGER DEFAULT 0,
|
||
|
|
int_blind_kill_rate REAL DEFAULT 0.0,
|
||
|
|
int_noscope_kills INTEGER DEFAULT 0,
|
||
|
|
int_noscope_rate REAL DEFAULT 0.0,
|
||
|
|
int_high_iq_score REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Timing Analysis (12 columns)
|
||
|
|
int_timing_early_kills INTEGER DEFAULT 0,
|
||
|
|
int_timing_mid_kills INTEGER DEFAULT 0,
|
||
|
|
int_timing_late_kills INTEGER DEFAULT 0,
|
||
|
|
int_timing_early_kill_share REAL DEFAULT 0.0,
|
||
|
|
int_timing_mid_kill_share REAL DEFAULT 0.0,
|
||
|
|
int_timing_late_kill_share REAL DEFAULT 0.0,
|
||
|
|
int_timing_avg_kill_time REAL DEFAULT 0.0,
|
||
|
|
int_timing_early_deaths INTEGER DEFAULT 0,
|
||
|
|
int_timing_early_death_rate REAL DEFAULT 0.0,
|
||
|
|
int_timing_aggression_index REAL DEFAULT 0.0,
|
||
|
|
int_timing_patience_score REAL DEFAULT 0.0,
|
||
|
|
int_timing_first_contact_time REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Pressure Performance (9 columns)
|
||
|
|
int_pressure_comeback_kd REAL DEFAULT 0.0,
|
||
|
|
int_pressure_comeback_rating REAL DEFAULT 0.0,
|
||
|
|
int_pressure_losing_streak_kd REAL DEFAULT 0.0,
|
||
|
|
int_pressure_matchpoint_kpr REAL DEFAULT 0.0,
|
||
|
|
int_pressure_clutch_composure REAL DEFAULT 0.0,
|
||
|
|
int_pressure_entry_in_loss REAL DEFAULT 0.0,
|
||
|
|
int_pressure_performance_index REAL DEFAULT 0.0,
|
||
|
|
int_pressure_big_moment_score REAL DEFAULT 0.0,
|
||
|
|
int_pressure_tilt_resistance REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Position Mastery (14 columns)
|
||
|
|
int_pos_site_a_control_rate REAL DEFAULT 0.0,
|
||
|
|
int_pos_site_b_control_rate REAL DEFAULT 0.0,
|
||
|
|
int_pos_mid_control_rate REAL DEFAULT 0.0,
|
||
|
|
int_pos_favorite_position TEXT,
|
||
|
|
int_pos_position_diversity REAL DEFAULT 0.0,
|
||
|
|
int_pos_rotation_speed REAL DEFAULT 0.0,
|
||
|
|
int_pos_map_coverage REAL DEFAULT 0.0,
|
||
|
|
int_pos_lurk_tendency REAL DEFAULT 0.0,
|
||
|
|
int_pos_site_anchor_score REAL DEFAULT 0.0,
|
||
|
|
int_pos_entry_route_diversity REAL DEFAULT 0.0,
|
||
|
|
int_pos_retake_positioning REAL DEFAULT 0.0,
|
||
|
|
int_pos_postplant_positioning REAL DEFAULT 0.0,
|
||
|
|
int_pos_spatial_iq_score REAL DEFAULT 0.0,
|
||
|
|
int_pos_avg_distance_from_teammates REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Trade Network (8 columns)
|
||
|
|
int_trade_kill_count INTEGER DEFAULT 0,
|
||
|
|
int_trade_kill_rate REAL DEFAULT 0.0,
|
||
|
|
int_trade_response_time REAL DEFAULT 0.0,
|
||
|
|
int_trade_given_count INTEGER DEFAULT 0,
|
||
|
|
int_trade_given_rate REAL DEFAULT 0.0,
|
||
|
|
int_trade_balance REAL DEFAULT 0.0,
|
||
|
|
int_trade_efficiency REAL DEFAULT 0.0,
|
||
|
|
int_teamwork_score REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- ========================================================================
|
||
|
|
-- TIER 4: META (52 columns)
|
||
|
|
-- Long-term patterns and meta-features
|
||
|
|
-- ========================================================================
|
||
|
|
|
||
|
|
-- Stability (8 columns)
|
||
|
|
meta_rating_volatility REAL DEFAULT 0.0,
|
||
|
|
meta_recent_form_rating REAL DEFAULT 0.0,
|
||
|
|
meta_win_rating REAL DEFAULT 0.0,
|
||
|
|
meta_loss_rating REAL DEFAULT 0.0,
|
||
|
|
meta_rating_consistency REAL DEFAULT 0.0,
|
||
|
|
meta_time_rating_correlation REAL DEFAULT 0.0,
|
||
|
|
meta_map_stability REAL DEFAULT 0.0,
|
||
|
|
meta_elo_tier_stability REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Side Preference (14 columns)
|
||
|
|
meta_side_ct_rating REAL DEFAULT 0.0,
|
||
|
|
meta_side_t_rating REAL DEFAULT 0.0,
|
||
|
|
meta_side_ct_kd REAL DEFAULT 0.0,
|
||
|
|
meta_side_t_kd REAL DEFAULT 0.0,
|
||
|
|
meta_side_ct_win_rate REAL DEFAULT 0.0,
|
||
|
|
meta_side_t_win_rate REAL DEFAULT 0.0,
|
||
|
|
meta_side_ct_fk_rate REAL DEFAULT 0.0,
|
||
|
|
meta_side_t_fk_rate REAL DEFAULT 0.0,
|
||
|
|
meta_side_ct_kast REAL DEFAULT 0.0,
|
||
|
|
meta_side_t_kast REAL DEFAULT 0.0,
|
||
|
|
meta_side_rating_diff REAL DEFAULT 0.0,
|
||
|
|
meta_side_kd_diff REAL DEFAULT 0.0,
|
||
|
|
meta_side_preference TEXT,
|
||
|
|
meta_side_balance_score REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Opponent Adaptation (12 columns)
|
||
|
|
meta_opp_vs_lower_elo_rating REAL DEFAULT 0.0,
|
||
|
|
meta_opp_vs_similar_elo_rating REAL DEFAULT 0.0,
|
||
|
|
meta_opp_vs_higher_elo_rating REAL DEFAULT 0.0,
|
||
|
|
meta_opp_vs_lower_elo_kd REAL DEFAULT 0.0,
|
||
|
|
meta_opp_vs_similar_elo_kd REAL DEFAULT 0.0,
|
||
|
|
meta_opp_vs_higher_elo_kd REAL DEFAULT 0.0,
|
||
|
|
meta_opp_elo_adaptation REAL DEFAULT 0.0,
|
||
|
|
meta_opp_stomping_score REAL DEFAULT 0.0,
|
||
|
|
meta_opp_upset_score REAL DEFAULT 0.0,
|
||
|
|
meta_opp_consistency_across_elos REAL DEFAULT 0.0,
|
||
|
|
meta_opp_rank_resistance REAL DEFAULT 0.0,
|
||
|
|
meta_opp_smurf_detection REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Map Specialization (10 columns)
|
||
|
|
meta_map_best_map TEXT,
|
||
|
|
meta_map_best_rating REAL DEFAULT 0.0,
|
||
|
|
meta_map_worst_map TEXT,
|
||
|
|
meta_map_worst_rating REAL DEFAULT 0.0,
|
||
|
|
meta_map_diversity REAL DEFAULT 0.0,
|
||
|
|
meta_map_pool_size INTEGER DEFAULT 0,
|
||
|
|
meta_map_specialist_score REAL DEFAULT 0.0,
|
||
|
|
meta_map_versatility REAL DEFAULT 0.0,
|
||
|
|
meta_map_comfort_zone_rate REAL DEFAULT 0.0,
|
||
|
|
meta_map_adaptation REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Session Pattern (8 columns)
|
||
|
|
meta_session_avg_matches_per_day REAL DEFAULT 0.0,
|
||
|
|
meta_session_longest_streak INTEGER DEFAULT 0,
|
||
|
|
meta_session_weekend_rating REAL DEFAULT 0.0,
|
||
|
|
meta_session_weekday_rating REAL DEFAULT 0.0,
|
||
|
|
meta_session_morning_rating REAL DEFAULT 0.0,
|
||
|
|
meta_session_afternoon_rating REAL DEFAULT 0.0,
|
||
|
|
meta_session_evening_rating REAL DEFAULT 0.0,
|
||
|
|
meta_session_night_rating REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- ========================================================================
|
||
|
|
-- TIER 5: COMPOSITE (11 columns)
|
||
|
|
-- Weighted composite scores (0-100)
|
||
|
|
-- ========================================================================
|
||
|
|
score_aim REAL DEFAULT 0.0,
|
||
|
|
score_clutch REAL DEFAULT 0.0,
|
||
|
|
score_pistol REAL DEFAULT 0.0,
|
||
|
|
score_defense REAL DEFAULT 0.0,
|
||
|
|
score_utility REAL DEFAULT 0.0,
|
||
|
|
score_stability REAL DEFAULT 0.0,
|
||
|
|
score_economy REAL DEFAULT 0.0,
|
||
|
|
score_pace REAL DEFAULT 0.0,
|
||
|
|
score_overall REAL DEFAULT 0.0,
|
||
|
|
tier_classification TEXT,
|
||
|
|
tier_percentile REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
-- Foreign key constraint
|
||
|
|
FOREIGN KEY (steam_id_64) REFERENCES dim_players(steam_id_64)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes for query performance
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_dm_player_features_rating ON dm_player_features(core_avg_rating DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_dm_player_features_matches ON dm_player_features(total_matches DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_dm_player_features_tier ON dm_player_features(tier_classification);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_dm_player_features_updated ON dm_player_features(last_updated DESC);
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- Auxiliary Table: dm_player_match_history
|
||
|
|
-- ============================================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS dm_player_match_history (
|
||
|
|
steam_id_64 TEXT,
|
||
|
|
match_id TEXT,
|
||
|
|
match_date INTEGER, -- Unix timestamp
|
||
|
|
match_sequence INTEGER, -- Player's N-th match
|
||
|
|
|
||
|
|
-- Core performance snapshot
|
||
|
|
rating REAL,
|
||
|
|
kd_ratio REAL,
|
||
|
|
adr REAL,
|
||
|
|
kast REAL,
|
||
|
|
is_win BOOLEAN,
|
||
|
|
|
||
|
|
-- Match context
|
||
|
|
map_name TEXT,
|
||
|
|
opponent_avg_elo REAL,
|
||
|
|
teammate_avg_rating REAL,
|
||
|
|
|
||
|
|
-- Cumulative stats
|
||
|
|
cumulative_rating REAL,
|
||
|
|
rolling_10_rating REAL,
|
||
|
|
|
||
|
|
PRIMARY KEY (steam_id_64, match_id),
|
||
|
|
FOREIGN KEY (steam_id_64) REFERENCES dm_player_features(steam_id_64) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (match_id) REFERENCES fact_matches(match_id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_player_history_player_date ON dm_player_match_history(steam_id_64, match_date DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_player_history_match ON dm_player_match_history(match_id);
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- Auxiliary Table: dm_player_map_stats
|
||
|
|
-- ============================================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS dm_player_map_stats (
|
||
|
|
steam_id_64 TEXT,
|
||
|
|
map_name TEXT,
|
||
|
|
|
||
|
|
matches INTEGER DEFAULT 0,
|
||
|
|
wins INTEGER DEFAULT 0,
|
||
|
|
win_rate REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
avg_rating REAL DEFAULT 0.0,
|
||
|
|
avg_kd REAL DEFAULT 0.0,
|
||
|
|
avg_adr REAL DEFAULT 0.0,
|
||
|
|
avg_kast REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
best_rating REAL DEFAULT 0.0,
|
||
|
|
worst_rating REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
PRIMARY KEY (steam_id_64, map_name),
|
||
|
|
FOREIGN KEY (steam_id_64) REFERENCES dm_player_features(steam_id_64) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_player_map_stats_player ON dm_player_map_stats(steam_id_64);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_player_map_stats_map ON dm_player_map_stats(map_name);
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- Auxiliary Table: dm_player_weapon_stats
|
||
|
|
-- ============================================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS dm_player_weapon_stats (
|
||
|
|
steam_id_64 TEXT,
|
||
|
|
weapon_name TEXT,
|
||
|
|
|
||
|
|
total_kills INTEGER DEFAULT 0,
|
||
|
|
total_headshots INTEGER DEFAULT 0,
|
||
|
|
hs_rate REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
usage_rounds INTEGER DEFAULT 0,
|
||
|
|
usage_rate REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
avg_kills_per_round REAL DEFAULT 0.0,
|
||
|
|
effectiveness_score REAL DEFAULT 0.0,
|
||
|
|
|
||
|
|
PRIMARY KEY (steam_id_64, weapon_name),
|
||
|
|
FOREIGN KEY (steam_id_64) REFERENCES dm_player_features(steam_id_64) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_player_weapon_stats_player ON dm_player_weapon_stats(steam_id_64);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_player_weapon_stats_weapon ON dm_player_weapon_stats(weapon_name);
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- Schema Summary
|
||
|
|
-- ============================================================================
|
||
|
|
-- dm_player_features: 213 columns (6 metadata + 207 features)
|
||
|
|
-- - Tier 1 CORE: 41 columns
|
||
|
|
-- - Tier 2 TACTICAL: 44 columns
|
||
|
|
-- - Tier 3 INTELLIGENCE: 53 columns
|
||
|
|
-- - Tier 4 META: 52 columns
|
||
|
|
-- - Tier 5 COMPOSITE: 11 columns
|
||
|
|
--
|
||
|
|
-- dm_player_match_history: Per-match snapshots for trend analysis
|
||
|
|
-- dm_player_map_stats: Map-level aggregations
|
||
|
|
-- dm_player_weapon_stats: Weapon usage statistics
|
||
|
|
-- ============================================================================
|