The goal here is to create a table (or view) for all offensive stats for each player-year-stint-split. Let’s go back and parse that briefly: Stats are collected for each player who accumulates them. Each player gets his own row. For each year that a player accumulates stats, a new row of data is created for that player. For each team that a player plays in a given year (stint), a new row of data is created for that player. Stats are accumulated and placed into three splits for each player-year-stint: Overall, vs. Left, and vs. Right.
I will most likely create views for each split, but for the moment I am going to focus only on Overall (split_id=1).
As this view is where I have gotten tripped up in the past, I’m going to take it slowly here, checking against game data periodically. So, easy bit first: Let’s get the counting stats and the more traditional rate stats out of the way:
#Calculated batting stats for OOTP
CREATE OR REPLACE VIEW CalcBatting AS
SELECT b.year
, b.league_id
, b.player_id
, b.stint #We can eventually move this down the list
, b.split_id #We can eventually remove this
, b.team_id #We can eventually move this down the list
, l.abbr as Lg
, t.abbr as Team
, b.g
, b.ab
, b.ab+b.bb+b.sh+b.sf+b.hp as PA
, b.r
, b.h
, b.d
, b.t
, b.hr
, b.rbi
, b.sb
, b.cs
, b.bb
, b.k
, b.ibb
, b.hp
, b.sh
, b.sf
, b.gdp
FROM
players_career_batting_stats b
INNER JOIN leagues l ON b.league_id=l.league_id
INNER JOIN teams t ON b.team_id=t.team_id
WHERE b.ab<>0 AND b.split_id=1
ORDER BY b.player_id, b.yearWe’ll pick a random player from in-game and make sure we line up. Let’s pick player #14 from 2015 and compare to OOTP. Not expecting any problems on the easy stuff. Still, just to be sure.
So far so good. Next post will finish up with the standard stats and some of the easier advanced stats.
