Run Expectancy Without Play-By-Play

Find run expectancy season-by-season using only the Baseball Databank. Based upon a blog post from Tango.

CREATE TABLE FATE_RE
AS
SELECT yearID, lgID, SUM(R)/SUM(IPOuts/3) AS RE_0_0
    , ((SUM(R)/SUM(IPOuts/3)) - (SUM(HR)/SUM(IPOuts/3))) *.5 + (SUM(HR)/SUM(IPOuts/3)) * 2/3 AS RE_0_1
    , ((SUM(R)/SUM(IPOuts/3)) - (SUM(HR)/SUM(IPOuts/3))) * 1/6 + (SUM(HR)/SUM(IPOuts/3)) * 1/3 AS RE_0_2
    , SUM(HR)/SUM(IPOuts/3) AS HR_I
FROM bdb.pitching
GROUP BY yearID, lgID;
 
CREATE TABLE scoring_from_third
AS
SELECT b.yearID
    , b.lgID
    , b.H/b.AB AS BA
    , f.E/b.PA AS E
    , b.BB/b.PA AS BB
    , (f.PB+p.WP)/b.PA AS WP_PB
    , p.SO/b.AB AS SO
FROM (SELECT yearID, lgID, SUM(H) AS H, SUM(SO) AS SO
        , SUM(AB+COALESCE(SF,0)) AS AB
        , SUM(AB+BB+COALESCE(IBB,0)+COALESCE(HBP,0)+COALESCE(SH,0)+COALESCE(SF,0)) AS PA
        , SUM(BB+COALESCE(IBB,0)+COALESCE(HBP,0)) AS BB FROM bdb.batting GROUP BY yearID, lgID) b
    , (SELECT yearID, lgID, SUM(E) AS E, SUM(COALESCE(PB,0)) AS PB FROM bdb.fielding GROUP BY yearID, lgID) f
    , (SELECT yearID, lgID, SUM(COALESCE(WP,0)) AS WP, SUM(SO) AS SO FROM bdb.pitching GROUP BY yearID, lgID) p
WHERE b.yearID = f.yearID
    AND b.lgID = f.lgID
    AND b.yearID = p.yearID
    AND b.lgID = p.lgID;
 
CREATE TABLE THIRD_RE
AS
SELECT yearID
    , lgID
    , @two := (BA+E+(BB*BB*BB)+WP_PB) AS RE_3_2
    , @sf := (1-(BA+E+SO))*.5 AS SF
    , @one := @two + @sf + (1-(@two + @sf))*@two AS RE_3_1
    , @zero := @two + @sf + (1-(@two + @sf))*@one AS RE_3_0
FROM scoring_from_third;
 
CREATE TABLE scoring_from_other AS
SELECT f.yearID
    , f.lgID
    , @r := (f.RE_0_0 - f.HR_I) AS R_I
    , @sr3 := (t.RE_3_0+t.RE_3_1+t.RE_3_2)/3 AS SR3
    , @br3 := b.BR3 AS BR3
    , ((@r - @sr3*@br3)-.17*BR2)/BR AS R_1
    , ((@r - @sr3*@br3)-.17*BR2)/BR+.17 AS R_2
FROM fate_re f
    , third_re t
    , (SELECT yearID, lgID
        , SUM(3B)/SUM(IPOuts/3) AS BR3
        , SUM(2B)/SUM(IPOuts/3) AS BR2
        , SUM(H-HR-3B+BB+COALESCE(HBP,0)+E)/SUM(IPOuts/3) AS BR
        FROM bdb.teams GROUP BY yearID, lgID) b
WHERE f.yearID = t.yearID
AND f.lgID = t.lgID
AND f.yearID = b.yearID
AND f.lgID = b.lgID;
 
CREATE TABLE first_re AS
SELECT yearID, lgID, 1.5*R_1 AS RE_1_0, R_1 AS RE_1_1, .5*R_1 AS RE_1_2 FROM scoring_from_other;
 
CREATE TABLE second_re AS
SELECT yearID, lgID, 1.5*R_2 AS RE_2_0, R_2 AS RE_2_1, .5*R_2 AS RE_2_2 FROM scoring_from_other;
 
CREATE TABLE re_matrices AS
SELECT a.yearID
    , a.lgID
    , RE_0_0
    , RE_0_1
    , RE_0_2
    , (RE_0_0+RE_1_0) AS RE_01_0
    , (RE_0_1+RE_1_1) AS RE_01_1
    , (RE_0_2+RE_1_2) AS RE_01_2
    , (RE_0_0+RE_2_0) AS RE_02_0
    , (RE_0_1+RE_2_1) AS RE_02_1
    , (RE_0_2+RE_2_2) AS RE_02_2
    , (RE_0_0+RE_3_0) AS RE_03_0
    , (RE_0_1+RE_3_1) AS RE_03_1
    , (RE_0_2+RE_3_2) AS RE_03_2
    , (RE_0_0+RE_1_0+RE_2_0) AS RE_012_0
    , (RE_0_1+RE_1_1+RE_2_1) AS RE_012_1
    , (RE_0_2+RE_1_2+RE_2_2) AS RE_012_2
    , (RE_0_0+RE_1_0+RE_3_0) AS RE_013_0
    , (RE_0_1+RE_1_1+RE_3_1) AS RE_013_1
    , (RE_0_2+RE_1_2+RE_3_2) AS RE_013_2
    , (RE_0_0+RE_2_0+RE_3_0) AS RE_023_0
    , (RE_0_1+RE_2_1+RE_3_1) AS RE_023_1
    , (RE_0_2+RE_2_2+RE_3_2) AS RE_023_2
    , (RE_0_0+RE_1_0+RE_2_0+RE_3_0) AS RE_0123_0
    , (RE_0_1+RE_1_1+RE_2_1+RE_3_1) AS RE_0123_1
    , (RE_0_2+RE_1_2+RE_2_2+RE_3_2) AS RE_0123_2
FROM first_re f, second_re s, third_re t, fate_re a
WHERE a.yearID= f.yearID
AND a.yearID= s.yearID
AND a.yearID= t.yearID
AND a.lgID = f.lgID
AND a.lgID = s.lgID
AND a.lgID = t.lgID;