{"id":272,"date":"2018-04-06T12:32:41","date_gmt":"2018-04-06T19:32:41","guid":{"rendered":"http:\/\/www.ootp.cavebutter.net\/blog\/?p=272"},"modified":"2018-04-06T12:38:41","modified_gmt":"2018-04-06T19:38:41","slug":"batting-stats-11-wrc-revisited","status":"publish","type":"post","link":"http:\/\/www.ootp.cavebutter.net\/blog\/archives\/272","title":{"rendered":"Batting Stats 11: wRC+ Revisited"},"content":{"rendered":"

The results that we got from testing the wRC+ stat weren’t great. \u00a0In testing some other stuff, I realized that we’re getting duplicate entries for batters on the CalcBatting table:<\/p>\n

\"\"<\/a><\/p>\n

Clearly the same player, same year, stint, team, and stats. \u00a0Well, all stats except for wRC+. \u00a0The issue is not being able to return a unique subleague for each row. \u00a0To deal with this, we’re going to make a couple of adjustments:<\/p>\n

First, we’re going to change the sub_league_history_batting table to mirror the structure of the sub_league_history_pitching table<\/a>. \u00a0This doesn’t directly solve the problem, but on reflection, I didn’t like how this table was calling data from a table it was sending data to. \u00a0It seems like a circular reference to me, even if the data is static.<\/p>\n

DROP TABLE IF EXISTS sub_league_history_batting;\r\nCREATE TABLE IF NOT EXISTS sub_league_history_batting AS\r\n\r\nSELECT\r\n       year\r\n     , league_id\r\n     , sub_league_id\r\n     , slg_PA\r\n     , slg_r\r\n     \r\n     FROM  (        \r\n     SELECT p.year\r\n          , p.league_id\r\n          , t.sub_league_id\r\n          , sum(pa) AS slg_PA\r\n          , sum(r) AS slg_r\r\n     FROM players_career_batting_stats AS p INNER JOIN team_relations AS t ON p.team_id=t.team_id\r\n         INNER JOIN players ON p.player_id=players.player_id\r\n     WHERE p.split_id=1 AND players.position<>1\r\n     GROUP BY year, league_id, sub_league_id\r\n      ) AS x ;<\/code><\/pre>\n
Now we have the subleague data pulling from the game-generated players career batting stats table. \u00a0A quick check shows that subleague runs are unchanged by this, but PA counts have changed a tiny bit – less than 10 over tens of thousands. \u00a0Not sure why, but I think the raw data from players_career_batting_stats table is more accurate.<\/p>\n
Next, we’re going to add a sub_league column to CalcBatting, but we are going to do it in a way that avoids circular references. We’re actually going to alter the players_career_batting_stats table to include a subleague. \u00a0Then, we’ll get fancy and write a trigger that adds subleague to new records while leaving old ones unchanged. \u00a0Let’s start with altering the existing records.<\/p>\n
We’ll add the subleague column thusly:<\/p>\n
ALTER TABLE players_career_batting_stats
\nADD COLUMN sub_league_id INT AFTER league_id;<\/code><\/p>\n
Then, populate it:<\/p>\n
UPDATE players_career_batting_stats2 AS b
\nINNER JOIN team_relations AS t ON b.league_id=t.league_id AND b.team_id=t.team_id
\nSET b.sub_league_id=t.sub_league_id;<\/code><\/p>\n
We’ll come back to the trigger in another post. \u00a0We include the sub_league_id column near the top of CalcBatting returning the sub_league column we just included above:<\/p>\n
USE mystruggle;\r\n#Calculated batting stats for OOTP\r\n    DROP TABLE IF EXISTS CalcBatting;\r\n    CREATE TABLE IF NOT EXISTS CalcBatting AS\r\n\r\n    SELECT b.year\r\n    , b.league_id\r\n    , b.player_id\r\n    , b.stint \r\n    , b.split_id \r\n    , b.team_id \r\n    , b.sub_league_id\r\n    , b.g\r\n    , b.ab\r\nEtc, etc, etc<\/code><\/pre>\n
You’ll notice I did a little other cleanup as well; I removed the league_abbr and team_abbr from the table. \u00a0We don’t need these columns taking up space in the table when we can easily pull them in when we need them.<\/p>\n
Finally, and the cause of the duplicate records problem, was adding a missing JOIN element between players_career_batting_stats and sub_league_history_batting. \u00a0I had joined on league and sub-league, but not year. \u00a0Adding year brought me back to normal.<\/p>\n
After I made a few more adjustments that I will talk about in \u00a0a later post, I re-ran a test for wRC+, adjusting my Happy Zone down to 5 points.<\/p>\n
\"\"<\/a><\/p>\n