Run Environment 3: The Second Run Values View

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;