DROP TABLE IF EXISTS sub_league_history_pitching;\r\nCREATE TABLE IF NOT EXISTS sub_league_history_pitching AS\r\n\r\nSELECT\r\n year\r\n , league_id\r\n , sub_league_id\r\n , round((totER\/totIP)*9,2) AS slgERA \r\n , round((adjHRA + adjBB + adjHP - adjK)\/totIP+FIPConstant,2) AS slgFIP\r\n #FIP = ((13*HR)+(3*(BB+HBP))-(2*K))\/IP + constant\r\nFROM ( \r\n SELECT p.year\r\n , p.league_id\r\n , t.sub_league_id\r\n , ((sum(ip)*3)+sum(ipf))\/3 AS totIP\r\n , sum(er) AS totER\r\n , 13*sum(hra) AS adjHRA\r\n , 3*sum(bb) AS adjBB\r\n , 3*sum(hp) AS adjHP\r\n , 2*sum(k) AS adjK\r\n , f.FIPConstant\r\n FROM CalcPitching AS p INNER JOIN team_relations AS t ON p.team_id=t.team_id\r\n INNER JOIN FIPConstant AS f ON p.year=f.year AND p.league_id=f.league_id\r\n GROUP BY year, league_id, sub_league_id\r\n ) AS x ;<\/code><\/pre>\nThe calculation for FIP- is exactly the same as ERA-:<\/p>\n
FIP Minus = 100*((FIP + (FIP \u2013 FIP*(PF\/100)) )\/ AL or NL FIP)<\/code><\/p>\nWe’ve already got all of the data points we need, so let’s plug it in and see what happens.<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/www.ootp.cavebutter.net\/blog\/wp-content\/uploads\/2018\/03\/Fip-v1.jpg?resize=574%2C551\")
<\/a><\/p>\nPretty good. 25 of 30 within 5 points. \u00a0Two that were ridiculously off and 3 that are meh. \u00a0I 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. \u00a0I wouldn’t want my real life money riding on this, maybe, but it’s good enough for video games.<\/p>\n
The script for CalcPitching table is now:<\/p>\n
DROP TABLE IF EXISTS CalcPitching;\r\nCREATE TABLE IF NOT EXISTS CalcPitching AS\r\n\r\nSELECT\r\n i.player_id\r\n , i.year\r\n , i.stint\r\n , i.team_id\r\n , i.league_id\r\n , r.sub_league_id\r\n , split_id\r\n , i.ip\r\n , i.ab\r\n , i.tb\r\n , i.ha\r\n , i.k\r\n , i.bf\r\n , i.rs\r\n , i.bb\r\n , i.r\r\n , i.er\r\n , i.gb\r\n , i.fb\r\n , i.pi\r\n , i.ipf\r\n , i.g\r\n , i.gs\r\n , i.w\r\n , i.l\r\n , i.s\r\n , i.sa\r\n , i.da\r\n , i.sh\r\n , i.sf\r\n , i.ta\r\n , i.hra\r\n , i.bk\r\n , i.ci\r\n , i.iw\r\n , i.wp\r\n , i.hp\r\n , i.gf\r\n , i.dp\r\n , i.qs\r\n , i.svo\r\n , i.bs\r\n , i.ra\r\n , i.cg\r\n , i.sho\r\n , i.sb\r\n , i.cs\r\n , i.hld\r\n , i.ir\r\n , i.irs\r\n , i.wpa\r\n , i.li\r\n , i.outs\r\n , i.war\r\n , @InnPitch := ((3*ip)+ipf)\/3 AS InnPitch\r\n , round((9*i.k)\/@InnPitch,1) AS 'k9'\r\n , round((9*i.bb)\/@InnPitch,1) AS 'bb9'\r\n , round((9*i.hra)\/@InnPitch,1) AS 'HR9'\r\n , round((i.bb+i.ha)\/@InnPitch,2) AS WHIP\r\n , round(i.k\/i.bb,2) AS 'K\/BB'\r\n , i.gb\/i.fb AS 'gb\/fb'\r\n , round((i.ha-i.hra)\/(i.ab-i.k-i.hra-i.sh+i.sf),3) AS BABIP\r\n , @ERA := round((i.er\/@InnPitch)*9,2) AS ERA\r\n , @FIP := round(((13*i.hra)+(3*(i.bb+i.hp))-(2*i.k))\/@InnPitch+f.FIPConstant,2) AS FIP \r\n , round(((13*(i.fb*f.hr_fb_pct))+(3*(i.bb+i.hp))-(2*i.k))\/@InnPitch+f.FIPConstant,2) AS xFIP\r\n , round(100*((@ERA + (@ERA - @ERA*(p.avg)))\/slg.slgERA),0) AS ERAminus\r\n , round(100*(slg.slgERA\/@ERA)*p.avg,0) AS ERAplus\r\n , round(100*((@FIP + (@FIP - @FIP*(p.avg)))\/slg.slgFIP),0) AS FIPminus\r\n FROM players_career_pitching_stats AS i\r\n INNER JOIN team_relations AS r ON i.team_id=r.team_id AND i.league_id=r.league_id\r\n INNER JOIN FIPConstant AS f ON i.year=f.year AND i.league_id=f.league_id\r\n 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\r\n INNER JOIN teams AS t ON i.team_id=t.team_id\r\n INNER JOIN parks AS p ON t.park_id=p.park_id\r\nWHERE i.split_id=1 AND i.league_id<>0;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"FIP- does for FIP just what ERA- does for ERA: it scales it to 100 and accounts for park factors and league run environment. \u00a0I 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. \u00a0I’ll go back and adjust… Continue reading Pitching Stats 7: FIP-<\/span>
![\"\"](\"https:\/\/i0.wp.com\/www.ootp.cavebutter.net\/blog\/wp-content\/uploads\/2018\/03\/Fip-v1.jpg\")
<\/a><\/p>\n