Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
WITH data AS (
SELECT
CAST(SUBSTR(game_id, 4, 4) AS UNSIGNED) season,
CAST(SUBSTR(game_id, 4) AS UNSIGNED) date,
game_id,
pit_id,
SUM(event_outs_ct) AS outs,
SUM(IF(bat_dest_id >= 4, 1, 0) + IF(run1_dest_id >= 4, 1, 0) + IF(run2_dest_id >= 4, 1, 0) + IF( run3_dest_id >= 4, 1, 0 )) runs,
SUM(IF(event_cd = 3, 1, 0 )) strikeouts,
SUM(IF(event_cd = 23, 1, 0 )) homeruns,
SUM(IF(event_cd = 14, 1, 0 )) walks
FROM s_retrosheet_events
WHERE
pit_id = resp_pit_id
AND CAST(SUBSTR(game_id, 4, 4) AS UNSIGNED) = 2019
GROUP BY
1, 2, 3, 4
)
SELECT
n.season,
n.date,
n.pit_id,
n.game_id,
SUM(b.outs) DIV 3 + .1 * MOD(SUM(b.outs), 3) inningsPitched,
SUM(b.outs) outs,
SUM(b.runs) runs,
SUM(b.strikeouts) strikeouts,
SUM(b.homeruns) homeruns,
SUM(b.walks) walks
FROM data n
INNER JOIN data b
ON n.pit_id = b.pit_id
AND n.date > b.date
GROUP BY
1, 2, 3, 4;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.