tRA

CREATE TABLE expected_outs_raw AS
SELECT * FROM (SELECT YEAR_ID
    , (CASE WHEN EVENT_CD = 23 THEN 23
    WHEN BATTEDBALL_CD != "" THEN BATTEDBALL_CD
    ELSE EVENT_CD END) AS EVENT
    , AVG(EVENT_OUTS_CT) AS OUTS
    , COUNT(1) AS NUM
FROM retrosheet.events e
WHERE YEAR_ID > 1992
AND BAT_EVENT_FL = "T"
AND IF(EVENT_CD IN (2,18,19,20,21,22) AND BATTEDBALL_CD = "",1,0) = 0
AND EVENT_CD != 17
GROUP BY YEAR_ID, EVENT
UNION ALL
SELECT YEAR_ID
    , "M" AS EVENT
    , AVG(EVENT_OUTS_CT) AS OUTS
    , SUM(IF(BATTEDBALL_CD = "",1,0)) AS NUM
FROM retrosheet.events e
WHERE YEAR_ID > 1992
AND BAT_EVENT_FL = "T"
AND EVENT_CD IN (2,18,19,20,21,22)
GROUP BY YEAR_ID, EVENT) a
ORDER BY YEAR_ID, EVENT;
 
CREATE TABLE expected_outs_correction AS
SELECT YEAR_ID, SUM(EVENT_OUTS_CT)/SUM(IF(BAT_EVENT_FL = "T" AND EVENT_CD != 17,1,0)) AS OUT_RATE
    , SUM(IF(BAT_EVENT_FL = "T" AND EVENT_CD != 17,EVENT_OUTS_CT,0))
    /SUM(IF(BAT_EVENT_FL = "T" AND EVENT_CD != 17,1,0)) AS PA_OUT_RATE
FROM retrosheet.events e
WHERE YEAR_ID > 1992
GROUP BY YEAR_ID;
 
CREATE TABLE expected_outs AS
SELECT r.YEAR_ID, r.EVENT, r.OUTS+(c.OUT_RATE-c.PA_OUT_RATE) AS OUTS, r.NUM
FROM expected_outs_raw r, expected_outs_correction c
WHERE r.YEAR_ID = c.YEAR_ID;
 
CREATE TABLE event_runs_raw AS
SELECT * FROM (SELECT e.YEAR_ID
    , (CASE WHEN e.EVENT_CD = 23 THEN 23
    WHEN e.BATTEDBALL_CD != "" THEN BATTEDBALL_CD
    ELSE e.EVENT_CD END) AS EVENT
    , AVG(e.EVENT_RUNS_CT+r2.RE-r1.RE) AS LWTS
    , COUNT(1) AS NUM
FROM retrosheet.events e, lwts.re_complete r1, lwts.re_complete r2
WHERE e.YEAR_ID > 1992
AND e.BAT_EVENT_FL = "T"
AND IF(e.EVENT_CD IN (2,18,19,20,21,22) AND e.BATTEDBALL_CD = "",1,0) = 0
AND e.EVENT_CD != 17
AND e.YEAR_ID = r1.YEAR_ID
AND e.OUTS_CT = r1.OUTS_CT
AND e.START_BASES_CD = r1.BASES_CD
AND e.YEAR_ID = r2.YEAR_ID
AND (e.OUTS_CT+e.EVENT_OUTS_CT) = r2.OUTS_CT
AND e.END_BASES_CD = r2.BASES_CD
GROUP BY e.YEAR_ID, EVENT
UNION ALL
SELECT e.YEAR_ID
    , "M" AS EVENT
    , AVG(e.EVENT_RUNS_CT+r2.RE-r1.RE) AS LWTS
    , SUM(IF(e.BATTEDBALL_CD = "",1,0)) AS NUM
FROM retrosheet.events e, lwts.re_complete r1, lwts.re_complete r2
WHERE e.YEAR_ID > 1992
AND e.BAT_EVENT_FL = "T"
AND e.EVENT_CD IN (2,18,19,20,21,22)
AND e.YEAR_ID = r1.YEAR_ID
AND e.OUTS_CT = r1.OUTS_CT
AND e.START_BASES_CD = r1.BASES_CD
AND e.YEAR_ID = r2.YEAR_ID
AND (e.OUTS_CT+e.EVENT_OUTS_CT) = r2.OUTS_CT
AND e.END_BASES_CD = r2.BASES_CD
GROUP BY YEAR_ID, EVENT) a
ORDER BY YEAR_ID, EVENT;
 
CREATE TABLE runs_per_out AS
SELECT YEAR_ID, SUM(EVENT_RUNS_CT)/SUM(EVENT_OUTS_CT) AS R_O
FROM retrosheet.events
WHERE YEAR_ID > 1992
GROUP BY YEAR_ID;
 
CREATE TABLE event_runs_out_raw AS
SELECT r.YEAR_ID, r.EVENT, r.LWTS+(o.OUTS*p.R_O) AS LWTS, o.OUTS, r.NUM
FROM event_runs_raw r, expected_outs o, runs_per_out p
WHERE r.YEAR_ID = o.YEAR_ID
AND r.YEAR_ID = p.YEAR_ID
AND r.EVENT = o.EVENT;
 
CREATE TABLE runs_correction AS
SELECT a.YEAR_ID, (b.RUNS-a.RUNS)/NUM AS Cor
FROM (SELECT YEAR_ID, SUM(LWTS*NUM) AS RUNS, SUM(NUM) AS NUM
FROM event_runs_out_raw
GROUP BY YEAR_ID) a, (SELECT YEAR_ID, SUM(EVENT_RUNS_CT) AS RUNS
FROM retrosheet.events
WHERE YEAR_ID > 1992
GROUP BY YEAR_ID) b
WHERE a.YEAR_ID = b.YEAR_ID;
 
CREATE TABLE tra_events AS
SELECT r.YEAR_ID, r.EVENT, r.LWTS+c.Cor AS LWTS, r.OUTS, r.NUM
FROM runs_correction c, event_runs_out_raw r
WHERE c.YEAR_ID = r.YEAR_ID;
 
CREATE INDEX tra_events_idx ON tra_events (YEAR_ID, EVENT);
 
CREATE TABLE pitcher_tra AS
SELECT e.PIT_ID, e.YEAR_ID, SUM(EVENT_OUTS_CT)/3 AS IP
    , SUM(t.OUTS)/3 AS tIP
    , SUM(t.LWTS) AS tR
    , SUM(t.LWTS)/(SUM(t.OUTS)/3)*9 AS tRA
FROM retrosheet.events e, tra_events t
WHERE (e.YEAR_ID BETWEEN 1993 AND 1999 OR e.YEAR_ID > 2002)
AND e.BAT_EVENT_FL = "T"
AND e.EVENT_CD != 17
AND e.YEAR_ID = t.YEAR_ID
AND (CASE WHEN e.EVENT_CD = 23 THEN 23
    WHEN e.EVENT_CD IN (2,18,19,20,21,22) AND e.BATTEDBALL_CD = "" THEN "M"
    WHEN e.BATTEDBALL_CD != "" THEN e.BATTEDBALL_CD
    ELSE e.EVENT_CD END) = t.EVENT
GROUP BY PIT_ID, YEAR_ID;