Retrosheet III: Data Cumulativa de Pitchers

Una vez que se tiene la data en MySQL, es posible calcular data para lanzadores. En el siguiente código, escribimos una consulta que obtiene diferentes estadísticas de lanzadores. Esta consulta no tiene todas, pero si las necesarias para calcular FIP y ERA. Ojo, la línea 14 es a proposito.

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;

Deja un comentario