Here’s the same explanation of how the stats tables are organized as we used in the first Batting Stats post:<\/p>\n
Stats are collected for each player who accumulates them. \u00a0Each player gets his own row. \u00a0For each year that a player accumulates stats, a new row of data is created for that player. \u00a0For each team that a player plays in a given year (stint), a new row of data is created for that player. \u00a0Stats are accumulated and placed into three splits for each player-year-stint: Overall, vs. Left, and vs. Right.<\/p><\/blockquote>\n
As we did for the batting stats, we’ll be creating a new table for all of the pitching stats together in one place; counting stats provided by the game and calculated stats that we’ll derive here.<\/p>\n
We’re carrying over all of the counting stats, plus WPA and WAR. \u00a0The calculated stats we’re adding in this post fall in the category of Easy Stuff:<\/p>\n
\n
- InnPitch – I set this as a variable to avoid having to elaborate every time.\u00a0This is the IP integer plus the IPF (innings pitched fraction) x 0.33\n
round(IP + (IPF * .33),1).<\/code><\/pre>\n<\/li>\n- All of the “x9” stats: K\/9, BB\/9 etc.<\/li>\n
- WHIP<\/li>\n
- GB\/FB – Ground Ball\/Fly Ball outs<\/li>\n
- BABIP (see the batting post<\/a> for more on this)<\/li>\n
- ERA<\/li>\n<\/ul>\n
Here’s the code:<\/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 := round(i.ip + (i.ipf*.33),1) 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 \r\n \r\nFROM players_career_pitching_stats AS i;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"Here’s the same explanation of how the stats tables are organized as we used in the first Batting Stats post: Stats are collected for each player who accumulates them. \u00a0Each player gets his own row. \u00a0For each year that a player accumulates stats, a new row of data is created for that player. \u00a0For each… Continue reading Pitching Stats 1: The Easy Stuff<\/span>