Run Environment 6: Run Values 2

The last table for our Run Environment set is almost exactly the same as the one I presented before with two differences: we’re returning the runMinus and RunPlus from the intermediate table, and we’re using a variable to define wOBAscale- making the whole thing much more elegant and easy to understand.

# The last Run Values table, returning fields from RunValues1A, defining wOBAscale
# as a variable, and giving us the weighted factors by league year for batting events
DROP TABLE IF EXISTS tblRunValues2;
CREATE TABLE tblRunValues2 AS
SELECT year
, league_id
, RperOut 
, runBB 
, runHB 
, run1B 
, run2B 
, run3B 
, runHR 
, runSB 
, runCS 
, runMinus 
, runPlus 
, wOBA 
, @ws := 1/(runPlus+runMinus) AS wOBAscale 
, (runBB+runMinus)*@ws AS wobaBB 
, (runHB+runMinus)*@ws AS wobaHB 
, (run1B+runMinus)*@ws AS woba1B 
, (run2B+runMinus)*@ws AS woba2B 
, (run3B+runMinus)*@ws AS woba3B 
, (runHR+runMinus)*@ws AS wobaHR 
, runSB*@ws AS wobaSB 
, runCS*@ws AS wobaCS 
FROM tblRunValues1A;

This is the table that we’ll refer to when calculating wOBA for individual players, factoring wobaXX against their counting stats XX.  It’ll make more sense when we actually do it.

This is almost all of the data we need to complete our batting stats.  What remains is park factor.  I had been hoping that we could use the factors listed on the parks table.  Unfortunately, OOTP’s resident MySQL Whisperer says it’s not true. I’m going to set that aside for today- while I figure out how to calculate park factors – and use an incremental approach to developing the batting stats view.  I figure that will give me one, maybe two, posts to get a good method for that.