63 lines
2.5 KiB
SQL
63 lines
2.5 KiB
SQL
-- Create season batting stats table for production
|
|
-- This table stores aggregated seasonal batting statistics for fast querying
|
|
-- Updated with proper columns and constraints based on dev testing
|
|
|
|
CREATE TABLE seasonbattingstats (
|
|
-- Primary identifiers (composite primary key)
|
|
player_id INTEGER NOT NULL,
|
|
season INTEGER NOT NULL,
|
|
|
|
-- Additional identifiers and metadata
|
|
name VARCHAR(255),
|
|
sbaplayer_id INTEGER,
|
|
team_id INTEGER NOT NULL,
|
|
player_team_id INTEGER,
|
|
player_team_abbrev VARCHAR(10),
|
|
|
|
-- Counting statistics (summed from StratPlays)
|
|
pa INTEGER NOT NULL DEFAULT 0,
|
|
ab INTEGER NOT NULL DEFAULT 0,
|
|
run INTEGER NOT NULL DEFAULT 0,
|
|
hit INTEGER NOT NULL DEFAULT 0,
|
|
double INTEGER NOT NULL DEFAULT 0,
|
|
triple INTEGER NOT NULL DEFAULT 0,
|
|
homerun INTEGER NOT NULL DEFAULT 0,
|
|
rbi INTEGER NOT NULL DEFAULT 0,
|
|
bb INTEGER NOT NULL DEFAULT 0,
|
|
so INTEGER NOT NULL DEFAULT 0,
|
|
bphr INTEGER NOT NULL DEFAULT 0,
|
|
bpfo INTEGER NOT NULL DEFAULT 0,
|
|
bp1b INTEGER NOT NULL DEFAULT 0,
|
|
bplo INTEGER NOT NULL DEFAULT 0,
|
|
gidp INTEGER NOT NULL DEFAULT 0,
|
|
hbp INTEGER NOT NULL DEFAULT 0,
|
|
sac INTEGER NOT NULL DEFAULT 0,
|
|
ibb INTEGER NOT NULL DEFAULT 0,
|
|
sb INTEGER NOT NULL DEFAULT 0,
|
|
cs INTEGER NOT NULL DEFAULT 0,
|
|
|
|
-- Calculated statistics
|
|
avg REAL NOT NULL DEFAULT 0.000,
|
|
obp REAL NOT NULL DEFAULT 0.000,
|
|
slg REAL NOT NULL DEFAULT 0.000,
|
|
ops REAL NOT NULL DEFAULT 0.000,
|
|
woba REAL NOT NULL DEFAULT 0.000,
|
|
k_pct REAL NOT NULL DEFAULT 0.0,
|
|
|
|
-- Constraints
|
|
PRIMARY KEY (player_id, season),
|
|
FOREIGN KEY (player_id) REFERENCES player(id),
|
|
FOREIGN KEY (sbaplayer_id) REFERENCES sbaplayer(id),
|
|
FOREIGN KEY (team_id) REFERENCES team(id)
|
|
);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX idx_seasonbattingstats_season ON seasonbattingstats (season);
|
|
CREATE INDEX idx_seasonbattingstats_teamseason ON seasonbattingstats (season, team_id);
|
|
CREATE INDEX idx_seasonbattingstats_sbaplayer ON seasonbattingstats (sbaplayer_id);
|
|
|
|
-- Comments for documentation
|
|
COMMENT ON TABLE seasonbattingstats IS 'Aggregated seasonal batting statistics for fast querying. Updated via selective updates when games are posted.';
|
|
COMMENT ON COLUMN seasonbattingstats.player_id IS 'Player ID - part of composite primary key';
|
|
COMMENT ON COLUMN seasonbattingstats.season IS 'Season number - part of composite primary key';
|
|
COMMENT ON COLUMN seasonbattingstats.sbaplayer_id IS 'SBA Player ID - can be NULL for some players'; |