Run Environment 5: An Intermediate Table

In addition to using variables, Wyers also does the other thing I had thought about using: an intermediate table to store the runMinus, runPlus, and wOBAScale values.  On the one hand, this makes sense as we will only have to write out the calculations once and then refer to them in this table when we need them.  On the other hand, if we managed to use variables for defining the run values in the previous table, we should be able to do so with these values as well.

Also, in this table, we define league wOBA.  It makes sense that the weighted On Base Average for the entire league is just the entire league’s On Base Percentage.  If we’re looking at an average for the entire league, there is nothing to weigh it against.  We’ll see this idea again when we look at wRC+.

A few minor adjustments to the code to allow for OOTP’s naming conventions and one final difference:  I’m taking the batting stats from the league_history_batting table rather than the players_ file.  It’s already there and there are far fewer rows to sort through.  And given that we’re not filtering out any stats, it’s silly not to use it.  And, because we’re using it, we can use the column for singles (s) rather than deriving singles from H-2b-3b-HR.

#Creating an intermediate table so as not to have to write out formulae for rumMinus etc.
DROP TABLE IF EXISTS tblRunValues1A;
CREATE TABLE IF NOT EXISTS tblRunValues1A AS
SELECT r.year
, r.league_id
, r.RperOut 
, r.runBB 
, r.runHB 
, r.run1B 
, r.run2B 
, r.run3B 
, r.runHR 
, r.runSB 
, r.runCS 
, SUM(runBB*(BB-IBB) + runHB * HP + run1B * s + run2B * d 
   + run3B * t + 1.4 * HR + runSB * SB - runCS * CS) 
   / SUM(AB - H + SF) AS runMinus 

, SUM(runBB * (BB-IBB) + runHB * HP + run1B * s + run2B * d 
   + run3B * t + 1.4 * HR + runSB * SB - runCS * CS) 
   / SUM(BB-IBB + HP + H) AS runPlus

, SUM(H+BB-IBB+HP) / SUM(AB+BB-IBB+HP+SF) AS wOBA 
 
FROM tblRunValues r
INNER JOIN league_history_batting_stats 
   ON r.year=league_history_batting_stats.year 
   AND r.league_id=league_history_batting_stats.league_id

 
GROUP BY 
r.year
, r.league_id
, r.RperOut 
, r.runBB 
, r.runHB 
, r.run1B 
, r.run2B 
, r.run3B 
, r.runHR 
, r.runSB 
, r.runCS 
 
ORDER BY 
r.year DESC;