{"id":250,"date":"2018-03-29T11:10:53","date_gmt":"2018-03-29T18:10:53","guid":{"rendered":"http:\/\/www.ootp.cavebutter.net\/blog\/?p=250"},"modified":"2018-03-29T11:10:53","modified_gmt":"2018-03-29T18:10:53","slug":"pitching-stats-5-era","status":"publish","type":"post","link":"http:\/\/www.ootp.cavebutter.net\/blog\/archives\/250","title":{"rendered":"Pitching Stats 5: ERA-"},"content":{"rendered":"

It’s that time, once again, to try to deal with park adjusted stats. \u00a0Again, and against counsel, I will be pulling the park factors from the teams table rather than doing the calculations myself. \u00a0I got within spitting distance of a good result set for wRC+<\/a>, so I am hoping for similar with these park-adjusted pitching stats.<\/p>\n

First up is ERA-. \u00a0ERA- takes a pitcher’s ERA and puts it in the context of his league and his home park. \u00a0This makes it possible to compare players across eras and leagues, essentially normalizing the data. \u00a0100 is league average. \u00a0Every point below<\/em> 100 is 1 percent better<\/em> than average.<\/p>\n

The formula is pretty straight-forward:
\nERA Minus = 100*((ERA + (ERA \u2013 ERA*(PF\/100)) )\/ AL or NL ERA)<\/code><\/p>\n

A few things have to happen in order to run this calc. \u00a0First, we’ll need sub-league ERA’s. \u00a0As mentioned in the first FIP post, we sort of do but really don’t have this on the league_history_table. \u00a0Better to roll our own from players_career_pitching_stats table. \u00a0We’ll do this in the same manner that we did it for batting- joining to the team relations table to get subleague.<\/p>\n

Here’s how:<\/p>\n

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\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     FROM CalcPitching AS p INNER JOIN team_relations AS t ON p.team_id=t.team_id\r\n     GROUP BY year, league_id, sub_league_id\r\n      ) AS x ;<\/code><\/pre>\n
Before we move on to the park factor, we have to make sure that we can associate a player’s team with his sub-league. \u00a0As usual, I’m sure that there’s a more elegant way to go about this than where I landed. \u00a0The problem I needed to solve was that sub-leagues do not have unique identifiers; they are uniquely identified only as composites of league_id and sub_league_id. \u00a0So, it’s not enough to refer to a sub-league as sub-league-1. \u00a0<\/em>There are as many sub-league-1’s as there are leagues. \u00a0To make matters more complicated, the teams table does not carry a sub-league field. \u00a0That’s why we had to refer to the team_relations table. \u00a0Unfortunately, the team_relations table is the only table that contains all three necessary data points to pin down a team\/sub-league relationship. \u00a0When I tried to let the database do the thinking for me by joining to it, it wasn’t consistently choosing the correct sub-league for each team.<\/p>\n
I decided to add sub-league as a field to the already-crowded CalcPitching table. \u00a0It worked in testing, correctly pulling the right slgERA for each league-sub_league-year. \u00a0Like I said, I bet there’s a way to do this only with joins, but I wasn’t able to figure it out. \u00a0I am going to go back to the CalcBatting table and do the same thing. \u00a0Here’s the code for the new joins:<\/p>\n
INNER JOIN team_relations AS r ON i.team_id=r.team_id AND i.league_id=r.league_id
\nINNER 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<\/code><\/p>\n
The next thing is to return the park factor for each pitcher-stint-year. \u00a0We’ll do this by joining to the teams table, then to the parks table:<\/p>\n
INNER JOIN teams AS t ON i.team_id=t.team_id
\nINNER JOIN parks AS p ON t.park_id=p.park_id<\/code>
\nWith all that done, we’ve got to go back and define ERA as a variable so that we can reference it here without elaborating it. \u00a0Then, the formula is simple. \u00a0OOTP doesn’t track this stat either, so it’s hard to say with any certainty how well this works or how badly I’m getting bad results from using hard-coded park factors. \u00a0I did a quick sniff test, looking at ranges of ERA’s in my league and sniffing the ERA- stats for each. \u00a0It looks OK, I guess?<\/p>\n
OOTP uses ERA+ instead, which seems to be more or less the same stat scaled up from 100 rather than down. \u00a0I will tackle that one next.<\/p>\n
Here’s the full script for CalcPitching so far:<\/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    , 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      \r\nFROM 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
 <\/p>\n","protected":false},"excerpt":{"rendered":"
It’s that time, once again, to try to deal with park adjusted stats. \u00a0Again, and against counsel, I will be pulling the park factors from the teams table rather than doing the calculations myself. \u00a0I got within spitting distance of a good result set for wRC+, so I am hoping for similar with these park-adjusted… Continue reading Pitching Stats 5: ERA-<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[29],"tags":[7,4,15,6],"class_list":["post-250","post","type-post","status-publish","format-standard","hentry","category-pitching","tag-mysql","tag-ootp","tag-pitching","tag-tables"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9cxb5-42","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/250","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/comments?post=250"}],"version-history":[{"count":2,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/250\/revisions"}],"predecessor-version":[{"id":252,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/250\/revisions\/252"}],"wp:attachment":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/media?parent=250"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/categories?post=250"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/tags?post=250"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}