104 lines
3.4 KiB
SQL
104 lines
3.4 KiB
SQL
CREATE MATERIALIZED VIEW season_batting_stats_view AS
|
|
WITH batting_stats AS (
|
|
SELECT
|
|
p.name,
|
|
p.id AS player_id,
|
|
p.sbaplayer_id,
|
|
sg.season,
|
|
p.team_id AS player_team_id,
|
|
p.team_abbrev AS player_team_abbrev,
|
|
|
|
-- Counting statistics (summed from StratPlays)
|
|
SUM(sp.pa) AS pa,
|
|
SUM(sp.ab) AS ab,
|
|
SUM(sp.run) AS run,
|
|
SUM(sp.hit) AS hit,
|
|
SUM(sp.double) AS double,
|
|
SUM(sp.triple) AS triple,
|
|
SUM(sp.homerun) AS hr,
|
|
SUM(sp.rbi) AS rbi,
|
|
SUM(sp.bb) AS bb,
|
|
SUM(sp.so) AS so,
|
|
SUM(sp.bphr) AS bphr,
|
|
SUM(sp.bpfo) AS bpfo,
|
|
SUM(sp.bp1b) AS bp1b,
|
|
SUM(sp.bplo) AS bplo,
|
|
SUM(sp.gidp) AS gidp,
|
|
SUM(sp.hbp) AS hbp,
|
|
SUM(sp.sac) AS sac,
|
|
SUM(sp.ibb) AS ibb,
|
|
|
|
-- Calculated statistics using formulas
|
|
CASE
|
|
WHEN SUM(sp.ab) > 0
|
|
THEN ROUND(SUM(sp.hit)::DECIMAL / SUM(sp.ab), 3)
|
|
ELSE 0.000
|
|
END AS avg,
|
|
|
|
CASE
|
|
WHEN SUM(sp.pa) > 0
|
|
THEN ROUND((SUM(sp.hit) + SUM(sp.bb) + SUM(sp.hbp) + SUM(sp.ibb))::DECIMAL / SUM(sp.pa), 3)
|
|
ELSE 0.000
|
|
END AS obp,
|
|
|
|
CASE
|
|
WHEN SUM(sp.ab) > 0
|
|
THEN ROUND((SUM(sp.hit) + SUM(sp.double) + 2 * SUM(sp.triple) + 3 *
|
|
SUM(sp.homerun))::DECIMAL / SUM(sp.ab), 3)
|
|
ELSE 0.000
|
|
END AS slg,
|
|
|
|
CASE
|
|
WHEN SUM(sp.pa) > 0 AND SUM(sp.ab) > 0
|
|
THEN ROUND(
|
|
((SUM(sp.hit) + SUM(sp.bb) + SUM(sp.hbp) + SUM(sp.ibb))::DECIMAL / SUM(sp.pa)) +
|
|
((SUM(sp.hit) + SUM(sp.double) + 2 * SUM(sp.triple) + 3 *
|
|
SUM(sp.homerun))::DECIMAL / SUM(sp.ab)), 3)
|
|
ELSE 0.000
|
|
END AS ops,
|
|
|
|
-- wOBA calculation (simplified version - adjust weights as needed)
|
|
CASE
|
|
WHEN SUM(sp.pa) > 0
|
|
THEN ROUND((0.690 * SUM(sp.bb) + 0.722 * SUM(sp.hbp) + 0.888 * (SUM(sp.hit) -
|
|
SUM(sp.double) - SUM(sp.triple) - SUM(sp.homerun)) +
|
|
1.271 * SUM(sp.double) + 1.616 * SUM(sp.triple) + 2.101 *
|
|
SUM(sp.homerun))::DECIMAL / SUM(sp.pa), 3)
|
|
ELSE 0.000
|
|
END AS woba,
|
|
|
|
CASE
|
|
WHEN SUM(sp.pa) > 0
|
|
THEN ROUND(SUM(sp.so)::DECIMAL / SUM(sp.pa) * 100, 1)
|
|
ELSE 0.0
|
|
END AS k_pct
|
|
|
|
FROM stratplay sp
|
|
JOIN stratgame sg ON sg.id = sp.game_id
|
|
JOIN player p ON p.id = sp.batter_id
|
|
GROUP BY p.id, sg.season
|
|
),
|
|
running_stats AS (
|
|
SELECT
|
|
sp.runner_id AS player_id,
|
|
sg.season,
|
|
SUM(sp.sb) AS sb,
|
|
SUM(sp.cs) AS cs
|
|
FROM stratplay sp
|
|
JOIN stratgame sg ON sg.id = sp.game_id
|
|
WHERE sp.runner_id IS NOT NULL
|
|
GROUP BY sp.runner_id, sg.season
|
|
)
|
|
SELECT
|
|
bs.*,
|
|
COALESCE(rs.sb, 0) AS sb,
|
|
COALESCE(rs.cs, 0) AS cs
|
|
FROM batting_stats bs
|
|
LEFT JOIN running_stats rs ON bs.player_id = rs.player_id
|
|
AND bs.season = rs.season;
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX idx_season_batting_stats_season ON season_batting_stats_view (season);
|
|
CREATE INDEX idx_season_batting_stats_teamseason ON season_batting_stats_view (season, player_team_id);
|
|
CREATE INDEX idx_season_batting_stats_player ON season_batting_stats_view (player_id);
|
|
CREATE INDEX idx_season_batting_stats_sbaplayer ON season_batting_stats_view (sbaplayer_id); |