FIP- does for FIP just what ERA- does for ERA: it scales it to 100 and accounts for park factors and league run environment. I am still searching for a definitive formula- but I know that to begin with, I will need to calculate the league FIP for comparison purposes. I’ll go back and adjust the sub-league-pitching-stats table to include it.
In fact, I am going one step further, and generating a FIP for each sub-league. I am guessing that this will cause me to deviate a bit from the game’s generated scores, but in this case, I think this will lead me to more accurate results.
The revised sub-league-pitching-stats table now looks like this:
DROP TABLE IF EXISTS sub_league_history_pitching;
CREATE TABLE IF NOT EXISTS sub_league_history_pitching AS
SELECT
year
, league_id
, sub_league_id
, round((totER/totIP)*9,2) AS slgERA
, round((adjHRA + adjBB + adjHP - adjK)/totIP+FIPConstant,2) AS slgFIP
#FIP = ((13*HR)+(3*(BB+HBP))-(2*K))/IP + constant
FROM (
SELECT p.year
, p.league_id
, t.sub_league_id
, ((sum(ip)*3)+sum(ipf))/3 AS totIP
, sum(er) AS totER
, 13*sum(hra) AS adjHRA
, 3*sum(bb) AS adjBB
, 3*sum(hp) AS adjHP
, 2*sum(k) AS adjK
, f.FIPConstant
FROM CalcPitching AS p INNER JOIN team_relations AS t ON p.team_id=t.team_id
INNER JOIN FIPConstant AS f ON p.year=f.year AND p.league_id=f.league_id
GROUP BY year, league_id, sub_league_id
) AS x ;The calculation for FIP- is exactly the same as ERA-:
FIP Minus = 100*((FIP + (FIP – FIP*(PF/100)) )/ AL or NL FIP)
We’ve already got all of the data points we need, so let’s plug it in and see what happens.
Pretty good. 25 of 30 within 5 points. Two that were ridiculously off and 3 that are meh. I can rely on this stat to be game equivalent 85% of the time; in the right ballpark 93% of the time; so ridiculously off that I will be able to spot it immediately 6% of the time. I wouldn’t want my real life money riding on this, maybe, but it’s good enough for video games.
The script for CalcPitching table is now:
DROP TABLE IF EXISTS CalcPitching;
CREATE TABLE IF NOT EXISTS CalcPitching AS
SELECT
i.player_id
, i.year
, i.stint
, i.team_id
, i.league_id
, r.sub_league_id
, split_id
, i.ip
, i.ab
, i.tb
, i.ha
, i.k
, i.bf
, i.rs
, i.bb
, i.r
, i.er
, i.gb
, i.fb
, i.pi
, i.ipf
, i.g
, i.gs
, i.w
, i.l
, i.s
, i.sa
, i.da
, i.sh
, i.sf
, i.ta
, i.hra
, i.bk
, i.ci
, i.iw
, i.wp
, i.hp
, i.gf
, i.dp
, i.qs
, i.svo
, i.bs
, i.ra
, i.cg
, i.sho
, i.sb
, i.cs
, i.hld
, i.ir
, i.irs
, i.wpa
, i.li
, i.outs
, i.war
, @InnPitch := ((3*ip)+ipf)/3 AS InnPitch
, round((9*i.k)/@InnPitch,1) AS 'k9'
, round((9*i.bb)/@InnPitch,1) AS 'bb9'
, round((9*i.hra)/@InnPitch,1) AS 'HR9'
, round((i.bb+i.ha)/@InnPitch,2) AS WHIP
, round(i.k/i.bb,2) AS 'K/BB'
, i.gb/i.fb AS 'gb/fb'
, round((i.ha-i.hra)/(i.ab-i.k-i.hra-i.sh+i.sf),3) AS BABIP
, @ERA := round((i.er/@InnPitch)*9,2) AS ERA
, @FIP := round(((13*i.hra)+(3*(i.bb+i.hp))-(2*i.k))/@InnPitch+f.FIPConstant,2) AS FIP
, round(((13*(i.fb*f.hr_fb_pct))+(3*(i.bb+i.hp))-(2*i.k))/@InnPitch+f.FIPConstant,2) AS xFIP
, round(100*((@ERA + (@ERA - @ERA*(p.avg)))/slg.slgERA),0) AS ERAminus
, round(100*(slg.slgERA/@ERA)*p.avg,0) AS ERAplus
, round(100*((@FIP + (@FIP - @FIP*(p.avg)))/slg.slgFIP),0) AS FIPminus
FROM players_career_pitching_stats AS i
INNER JOIN team_relations AS r ON i.team_id=r.team_id AND i.league_id=r.league_id
INNER JOIN FIPConstant AS f ON i.year=f.year AND i.league_id=f.league_id
INNER JOIN sub_league_history_pitching AS slg ON i.year=slg.year AND i.league_id=slg.league_id AND r.sub_league_id=slg.sub_league_id
INNER JOIN teams AS t ON i.team_id=t.team_id
INNER JOIN parks AS p ON t.park_id=p.park_id
WHERE i.split_id=1 AND i.league_id<>0;
