Run Environment 4: Run Values A Different Way

My attempt in the last post resulted in a view of linear weights that, while it seemed to work, was very unwieldy.  It was so because for each of the linear weights, I had to spell out fully the calculations for runPlus, runMinus, and wobaScale.

I’ve been aware of this code from Colin Wyers for a while now, but wanted to try my hand at translating Tango’s views into MySQL on my own.  I’ve done that.  Now, I’m going to try to pick apart how Wyers uses variables to simplify the process and configure this back to my OOTP tables.  Probably best to have that link open in another tab while I walk through this.

I’ll be breaking this across several posts.

We’re going to ignore the first statement that creates a primary position (PrimPos) table.  As mentioned before, this table would serve to eliminate stats created by non-pitchers pitching.  My feeling is that they are part of the run environment and should be included and, besides, this is a rarer event in OOTP than it is in real life.

The second thing to note is that Wyers is creating a table rather than a view.  This is actually a good idea.  Views save disk space but have slower performance than tables.  That’s because views are essentially stored queries that are run every time they are accessed.  Tables are created once and saved as a data object in their own right.  Since space is not a concern for me and all of the advanced stats will require complex queries, I think that opting for performance here is the right thing to do.

Next, I am going to leave in place my view vLeagueRunsPerOut – the foundational view/table.  They’re too similar for me to make any changes.  Besides, I like that I thought to total up league Plate Appearances in mine.  It comes in handy later.

Minor changes to the RunValues table give me this:

DROP TABLE IF EXISTS tblRunValues;
CREATE TABLE IF NOT EXISTS tblRunValues
AS SELECT year
, league_id
, RperOut 
, @rb := RperOut+0.14 AS runBB 
, @rb+0.025 AS runHB 
, @rs := @rb+0.155 AS run1B 
, @rd := @rs+0.3 AS run2B 
, @rd+0.27 AS run3B 
, 1.4 AS runHR 
, 0.2 AS runSB 
, 2*RperOut+0.075 AS runCS 
FROM vLeagueRunsPerOut;

 

Two things are happening here that I didn’t think we could do.  First, declaring variables with just the ‘@’ and the ‘:=’ operator.  Murach, whose book I’ve been using to learn MySQL, doesn’t mention this as an option.  He does usually point out shortcuts that some devs take before advising against using them yourself.  This squares with the other web searches I’ve done regarding variables in MySQL.  But, it seems to work, so…

Second, I could have sworn that I had read that you can’t rely on how MySQL will evaluate variables when used like this.  That is, there’s not a guarantee that it will evaluate ‘@rb’ first and use that value to evaluate ‘@rs’.  It could decide to do ‘@rs’ first and force ‘@rb’ to null.  That didn’t happen here, and I am a little confused. (NOTE: I emailed Wyers about this and will update if he gets back to me.)

Bottom line, though, is that this worked.  Comparing the results from my original view without variables to Wyers’ table with them shows identical results if we disregard all of the trailing 0’s:

Oirignal View created without variables
Results from modified RunValues table with variables

OK then.  On to the next one.

Leave a Reply

Your email address will not be published. Required fields are marked *