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;