Skip to content

Instantly share code, notes, and snippets.

@LeeZee1
LeeZee1 / season-to-date average park factor code that goes into FIP- formula
Last active May 16, 2016 06:20
This procedure creates a season-to-date park factor (std_park_factor_avg) which is part of what is necessary to create a season-to-date FIPminus column from Retrosheet for starting pitchers in MySQL. Since FIP minus corrects for the parks that a pitcher pitches in, this is a necessary step. The full park factor values used are the ones calculate…
DROP PROCEDURE IF EXISTS std_park_factor_avg;
CREATE PROCEDURE std_park_factor_avg()
BEGIN
DECLARE pit_id CHAR(10);
DECLARE lgID CHAR (2);
DECLARE YEARID INT;
DECLARE gdate DATE;
DECLARE seq INT;
DECLARE F_park_factor INT;
DECLARE RNUMBER INT;
@LeeZee1
LeeZee1 / create season-to-date league average FIP column
Created May 16, 2016 06:17
creates row_number column that restarts at 1 at the start of a new year or new starting pitcher, which is necessary in order to create a meaningful season-to-date park factor column using the code for creating this season-to-date full park factor column.
DROP PROCEDURE IF EXISTS starting_pitcher_AL_NL_std_lg_FIP;
CREATE PROCEDURE starting_pitcher_AL_NL_std_lg_FIP()
BEGIN
DECLARE gdate DATE;
DECLARE YEARID INT;
DECLARE lgID CHAR (2);
DECLARE HR INT;
DECLARE walks INT;
DECLARE HitBP INT;
DECLARE K INT;
ALTER TABLE starting_pitcher_stats ADD COLUMN std_FIPm1 DOUBLE;
UPDATE starting_pitcher_stats SET std_FIPm1 = (std_FIP + (std_FIP - (std_FIP*(std_F_parkfactor/100))))/(std_lg_FIP)*100;
@LeeZee1
LeeZee1 / Retrosheet Multi-Year FIP
Created May 23, 2016 05:02 — forked from SpencerBingol/Retrosheet Multi-Year FIP
[MYSQL] Multi-year FIP Function for Retrosheet
-- function to calculate multi-year FIP for Retrosheet
-- This snippet supposes you have the Fangraphs GUTS table located with-in retrosheet.
-- Currently calculates career FIP. Just add a year parameter and it works for single season calculations.
DELIMITER //
CREATE FUNCTION FIP(pitcher VARCHAR(8))
RETURNS DECIMAL(10,2)
BEGIN
DECLARE FIP DECIMAL(10,2);
@LeeZee1
LeeZee1 / gist:5d7707b66401e2a0b3098a449c3b1344
Last active June 22, 2021 15:50
Create game-by-game FIP over the course of multiple seasons using MySQL
ALTER TABLE starting_pitcher_game_log ADD COLUMN FIP_g DOUBLE;
UPDATE starting_pitcher_game_log SET FIP_g = (((HR_a*13)+(3*(walks_a+HBP))-(2*strike_outs))/innings_pitched)+cFIP;