Tag: leagues

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:

OK then.  On to the next one.

Here’s what we’re doing for the next view.  We’re taking all of the run expectancy values from the previous view, making 3 more calculations that I will explain in a moment, and creating linear weights for each non-out batting event that we’ll use to derive wOBA.

runMinus establishes a run value for outs and other events that are not part of OBP.  It’s AB – H + SF, and is equivalent to a batting out.

runPlus gives us the average run value for all non-out batting events: Hits, Hit-by-Pitches, and Walks.

wOBA will give us the Weighted On Base Average for the league

wobaScale will give us the factor to apply to all of the other linear weights and for each league year in order to scale players’ wOBAs to their matching, unweighted On Base Percentage.

Two notes before the code.  First, we’re using stats from the players_career_batting_stats table.  I wrote a join to the league_history_stats table when I was thinking of doing it another way.  I may go back and switch it out to the other table as it will save us doing the sums.

Second, there’s got to be a more elegant way to do this.  I think there is, and it’s to do with defining runPlus, runMinus, and wobaScale as variables so that I don’t have to elaborate the code each time they are mentioned.  I know that I can’t use column aliases within the select statement.  So my options are leaving it as is (I am getting results that look reasonable), try my luck with variables, or create another table or view to store these values and call them from this view.

I will come back to this to try to clean it up.

 

CREATE OR REPLACE VIEW vRunValues2 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(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr)
    + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS)
    / Sum(b.ab -b.h + b.SF) AS runMinus 
, Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr)
    + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)
    / Sum(b.BB - b.IBB + b.HP +b.H) AS runPlus 

, #1/([runPlus]+[runMinus]) AS wOBAscale 
1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr)
    + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS))
    / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP
    + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR
    + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) AS wOBAscale

, #([runBB]+[runMinus])*[wOBAscale] AS wobaBB 
(r.runBB + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B
    * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB
    * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) 
    * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B
    * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr
    + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H)
    + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B 
    * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
    + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaBB

, #([runHB]+[runMinus])*[wOBAscale] AS wobaHB 
(r.runHB +(Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B 
   * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) 
   * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) 
   + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) 
   / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP 
   + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaHB

, #([run1B]+[runMinus])*[wOBAscale] AS woba1B 
(r.run1b + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP 
   +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr 
   + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) 
   + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF)))) AS woba1B

, #([run2B]+[runMinus])*[wOBAscale] AS woba2B 
(r.run2b + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP 
   +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr 
   + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) 
   + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP 
   + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS woba2B

, #([run3B]+[runMinus])*[wOBAscale] AS woba3B 
(r.run3B + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP 
   +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr 
   + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) 
   + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF)))) AS woba3B

, #([runHR]+[runMinus])*[wOBAscale] AS wobaHR 
(r.runHR + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP 
   +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr 
   + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) 
   + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF)))) AS wobaHR

, #[runSB]*[wOBAscale] AS wobaSB 
r.runSB * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) 
   + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) 
   / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP 
   + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaSB

, #[runCS]*[wOBAscale] AS wobaCS
r.runCS * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) 
   + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) 
   / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP 
   + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaCS

FROM 
vRunValues r
INNER JOIN players_career_batting_stats b ON r.year=b.year AND r.league_id=b.league_id
INNER JOIN league_history_batting_stats lhb ON r.year=lhb.year AND r.league_id=lhb.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;