DROP TABLE IF EXISTS FIPConstant;\r\nCREATE TABLE IF NOT EXISTS FIPConstant AS\r\n\r\nSELECT\r\n year\r\n , league_id\r\n , hra_totals\r\n , bb_totals\r\n , hp_totals\r\n , k_totals\r\n , er_totals\r\n , ip_totals\r\n , ipf_totals\r\n , @HRAdj := 13*hra_totals AS Adjusted_HR\r\n , @BBAdj := 3*bb_totals AS Adjusted_BB\r\n , @HPAdj := 3*hp_totals AS Adjusted_HP\r\n , @KAdj := 2*k_totals AS Adjusted_K\r\n , @InnPitch := ((ip_totals*3)+ipf_totals)\/3 AS InnPitch\r\n , @lgERA := round((er_totals\/@InnPitch)*9,2) AS lgERA\r\n , round(@lgERA - ((@HRAdj+@BBAdj+@HPAdj-@KAdj)\/@InnPitch),2) AS FIPConstant\r\nFROM (\r\n SELECT year\r\n , league_id\r\n , sum(hra) as hra_totals\r\n , sum(bb) as bb_totals\r\n , sum(hp) as hp_totals\r\n , sum(k) as k_totals\r\n , sum(er) as er_totals\r\n , sum(ip) as ip_totals\r\n , sum(ipf) as ipf_totals\r\n FROM players_career_pitching_stats\r\n GROUP BY year, league_id\r\n ) AS x;<\/code><\/pre>\nAnd how did it work? \u00a0Better.<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/www.ootp.cavebutter.net\/blog\/wp-content\/uploads\/2018\/03\/FIP-v2.jpg?resize=489%2C562\")
<\/a>9 within 0.05; 26 within 0.11. \u00a0I’m still curious as to why I’m not matching up even better. \u00a0I still have a lingering suspicion that HBP is behind this, but I am going to let it lie for now unless it comes back to bite me on other calculations.<\/p>\nOur CalcPitching table to this point:<\/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 , 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 , round((i.er\/@InnPitch)*9,2) AS ERA\r\n , round(((13*i.hra)+(3*(i.bb+i.hp))-(2*i.k))\/@InnPitch+f.FIPConstant,2) AS fip\r\n \r\n \r\nFROM players_career_pitching_stats AS i\r\n INNER JOIN FIPConstant AS f ON i.year=f.year AND i.league_id=f.league_id\r\nWHERE i.split_id=1;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"I redid the FIPConstant table to pull summed data from the players_career_pitching_stats table. \u00a0That table now looks like this: DROP TABLE IF EXISTS FIPConstant; CREATE TABLE IF NOT EXISTS FIPConstant AS SELECT year , league_id , hra_totals , bb_totals , hp_totals , k_totals , er_totals , ip_totals , ipf_totals , @HRAdj := 13*hra_totals AS Adjusted_HR… Continue reading Pitching Stats 3: FIP – The Conclusion<\/span>
![\"\"](\"https:\/\/i0.wp.com\/www.ootp.cavebutter.net\/blog\/wp-content\/uploads\/2018\/03\/FIP-v2.jpg\")
<\/a>9 within 0.05; 26 within 0.11. \u00a0I’m still curious as to why I’m not matching up even better. \u00a0I still have a lingering suspicion that HBP is behind this, but I am going to let it lie for now unless it comes back to bite me on other calculations.<\/p>\n