LeeZee1’s gists

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_a13)+(3(walks_a+HBP))-(2*strike_outs))/innings_pitched)+cFIP;

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 / code for creating season-to-date FIP- column

Created May 16, 2016 06:27

	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 / 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;

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;

Lee Zee LeeZee1