Baseball Prospectus Run Estimators
Calculates a player's Equivalent Runs and Marginal Lineup Value, the two competing run estimation techniques used by Baseball Prospectus.
Thanks to Patriot for helping explain EqR.
MySQL
CREATE TABLE league_averages AS SELECT yearID , lgID , SUM(R) AS R , SUM(AB + BB + HBP) AS PA , SUM(R) / SUM(AB + BB + HBP) AS LgR_PA , ( (SUM(H) + SUM(H + 2B + 2*3B + 3*HR) + 1.5*SUM(BB + HBP+SB) + SUM(SH + SF) )/ SUM( AB + BB + HBP + CS + SB + CS )) AS LgRAW , SUM(H)/SUM(AB) AS LgAVG , SUM(H+BB+HBP) / SUM(AB+BB+HBP+SH+SF) AS LgOBP , SUM(H + 2B + 2*3B + 3*HR)/ SUM(AB) AS LgSLG FROM bdb.batting where yearID > 1953 GROUP BY yearID, lgID; CREATE TABLE team_pas AS SELECT yearID , teamID , SUM(AB+BB+HBP+SH+SF) AS TmPA FROM bdb.batting where yearID > 1953 GROUP BY yearID, teamID; CREATE TABLE braa AS SELECT b.playerID , b.yearID , b.stint , b.teamID , ( (b.H + (b.H + b.2B + 2*b.3B + 3*b.HR) + 1.5*(b.BB + b.HBP+ b.SB) + b.SH + b.SF )/ ( b.AB + b.BB + b.HBP + b.CS + b.SB + b.CS )) AS RAW , (2 * ( (b.H + (b.H + b.2B + 2*b.3B + 3*b.HR) + 1.5*(b.BB + b.HBP) + b.SB )/ ( b.AB + b.BB + b.HBP + b.CS + b.SB/3 ))/l.LgRAW - 1) * ( b.AB + b.BB + b.HBP) * (l.LgR_PA) AS EqR , ( b.AB + b.BB + b.HBP) * (l.LgR_PA) AS LgEqR , ((2 * ( (b.H + (b.H + b.2B + 2*b.3B + 3*b.HR) + 1.5*(b.BB + b.HBP) + b.SB )/ ( b.AB + b.BB + b.HBP + b.CS + b.SB/3 ))/l.LgRAW - 1) * ( b.AB + b.BB + b.HBP) * (l.LgR_PA)) - (( b.AB + b.BB + b.HBP) * (l.LgR_PA)) AS BRAA FROM bdb.batting b, league_averages l WHERE b.yearID > 1953 AND b.yearID = l.yearID AND b.lgID = l.lgID; CREATE TABLE mlv AS SELECT b.playerID , b.yearID , b.stint , b.teamID , (AB+BB+HBP+SH+SF) AS PA , (H/AB) AS AVG , ((H+BB+HBP)/(AB+BB+HBP+SH+SF)) AS OBP , ((H + 2B + 2*3B + 3*HR)/AB) AS SLG , (CASE WHEN b.yearID > 1960 THEN 4131*(1/9 * (8*l.LgOBP+((H+BB+HBP)/(AB+BB+HBP+SH+SF))) / (9-8*l.LgOBP-((H+BB+HBP)/(AB+BB+HBP+SH+SF))) *((8*l.LgSLG*(1-l.LgOBP))/(1-l.LgAVG) + ((H + 2B + 2*3B + 3*HR)/AB)*(1-((H+BB+HBP)/(AB+BB+HBP+SH+SF)))/(1-(H/AB))) - l.LgOBP*l.LgSLG/(1-l.LgAVG)) ELSE 3927*(1/9 * (8*l.LgOBP+((H+BB+HBP)/(AB+BB+HBP+SH+SF))) / (9-8*l.LgOBP-((H+BB+HBP)/(AB+BB+HBP+SH+SF))) *((8*l.LgSLG*(1-l.LgOBP))/(1-l.LgAVG) + ((H + 2B + 2*3B + 3*HR)/AB)*(1-((H+BB+HBP)/(AB+BB+HBP+SH+SF)))/(1-(H/AB))) - l.LgOBP*l.LgSLG/(1-l.LgAVG)) END) AS MLV_FULL , (CASE WHEN b.yearID > 1960 THEN 4131*(1/9 * (8*l.LgOBP+((H+BB+HBP)/(AB+BB+HBP+SH+SF))) / (9-8*l.LgOBP-((H+BB+HBP)/(AB+BB+HBP+SH+SF))) *((8*l.LgSLG*(1-l.LgOBP))/(1-l.LgAVG) + ((H + 2B + 2*3B + 3*HR)/AB)*(1-((H+BB+HBP)/(AB+BB+HBP+SH+SF)))/(1-(H/AB))) - l.LgOBP*l.LgSLG/(1-l.LgAVG)) ELSE 3927*(1/9 * (8*l.LgOBP+((H+BB+HBP)/(AB+BB+HBP+SH+SF))) / (9-8*l.LgOBP-((H+BB+HBP)/(AB+BB+HBP+SH+SF))) *((8*l.LgSLG*(1-l.LgOBP))/(1-l.LgAVG) + ((H + 2B + 2*3B + 3*HR)/AB)*(1-((H+BB+HBP)/(AB+BB+HBP+SH+SF)))/(1-(H/AB))) - l.LgOBP*l.LgSLG/(1-l.LgAVG)) END) * (((AB+BB+HBP+SH+SF)/p.TmPA)/.111) AS MLV FROM bdb.batting b, league_averages l, team_pas p WHERE b.yearID > 1953 AND b.yearID = l.yearID AND b.lgID = l.lgID AND b.yearID = p.yearID AND b.teamID = p.teamID; CREATE TABLE mlv_braa AS SELECT m.playerID , m.yearID , m.stint , m.teamID , m.PA , m.MLV , b.BRAA from mlv m, braa b WHERE b.playerID = m.playerID AND b.yearID = m.yearID AND b.stint = m.stint AND b.teamID = m.teamID AND m.MLV is not NULL;
page revision: 2, last edited: 29 Nov 2008 03:36