[MYSQL] Multi-year wOBA Function for Retrosheet

Retrosheet Multi-Year wOBA

-- function to calculate multi-year wOBA for Retrosheet
-- This snippet supposes you have the Fangraphs GUTS table located with-in retrosheet.
-- Currently calculates career wOBA. Just add a year parameter and it works for single season calculations.
-- Also good for vs. Pitcher matchups and date ranges

DELIMITER //

CREATE FUNCTION wOBA(batter VARCHAR(8))
RETURNS DECIMAL(10,3)
BEGIN
	DECLARE wOBA decimal(10,3);
	SET wOBA = 0;
	SELECT cast((SELECT SUM(weighted_BB) as wBB_sum 
		FROM (
		  	SELECT g.wBB*SUM(if(e.event_CD=14,1,0)) as weighted_BB 
		  	FROM events e 
		  	INNER JOIN GUTS g 
		  		ON substring(e.game_ID,4,4)=g.season 
		  	WHERE bat_ID=batter 
		  	GROUP BY g.season) 
		as BB)  
		+ (SELECT SUM(weighted_HBP) as wHBP_sum 
		FROM (
			SELECT g.wHBP*SUM(if(e.event_CD=16,1,0)) as weighted_HBP 
			FROM events e 
			INNER JOIN GUTS g 
				ON substring(e.game_ID,4,4)=g.season 
			WHERE bat_ID=batter 
			GROUP BY g.season) 
		as HBP)		
		+ (SELECT SUM(weighted_1B) as w1B_sum 
		FROM (
			SELECT g.w1B*SUM(if(e.event_CD=20,1,0)) as weighted_1B 
			FROM events e 
			INNER JOIN GUTS g 
				ON substring(e.game_ID,4,4)=g.season 
			WHERE bat_ID=batter 
			GROUP BY g.season) 
		as 1B)		
		+ (SELECT SUM(weighted_2B) as w2B_sum 
		FROM ( 
			SELECT g.w2B*SUM(if(e.event_CD=21,1,0)) as weighted_2B 
			FROM events e 
			INNER JOIN GUTS g 
				ON substring(e.game_ID,4,4)=g.season 
			WHERE bat_ID=batter 
			GROUP BY g.season) 
		as 2B)		
		+ (SELECT SUM(weighted_3B) as w3B_sum 
		FROM (
			SELECT g.w3B*SUM(if(e.event_CD=22,1,0)) as weighted_3B 
			FROM events e 
			INNER JOIN GUTS g 
				ON substring(e.game_ID,4,4)=g.season 
			WHERE bat_ID=batter 
			GROUP BY g.season) 
		as 3B)		
		+ (SELECT SUM(weighted_HR) as wHR_sum 
		FROM (
			SELECT g.wHR*SUM(if(e.event_CD=23,1,0)) as weighted_HR 
			FROM events e 
			INNER JOIN GUTS g 
				ON substring(e.game_ID,4,4)=g.season 
			WHERE bat_ID=batter 
			GROUP BY g.season) 
		as HR) as decimal(10,3))
		/
		cast(SUM(if(e.ab_fl="T",1,0)) 
			+ SUM(if(e.event_cd=14,1,0)) 
			+ SUM(if(e.SF_fl="T",1,0)) 
			+ SUM(if(e.event_cd=16,1,0)) as unsigned) INTO wOBA	
		FROM events e
		WHERE e.bat_ID=batter 
		LIMIT 1;
	RETURN wOBA;
END //

DELIMITER ;

Hi Spencer,
Thank you for this code. I'm having a bit of a problem that I wonder if you can help with. I'd like to populate the column "wOBA" in my table "starting_pitcher_stats" with correct values that incorporate season-specific wOBA coefficients in the GUTS table that I imported into my retrosheet database. How I can actually call up the function to actually add the values into the "wOBA" column of my starting_pitcher_stats table? Any help with this would go a long way. Thank you in advance.
Lee

Hi spencer,
I'm using MySQL in SequelPro and am using the following code for a procedure to call up your function to populate the wOBA column of my starting_pitcher_stats table, but it's not working. I'd like to populate it for each game of each starting pitcher of each season. I wonder if you can offer some guidance on how to correctly modify your code and the following procedure code to make it work? Thank you in advance for any help with this.

DROP PROCEDURE IF EXISTS starting_pitcher_stats_wOBA;
DELIMITER $$
CREATE PROCEDURE starting_pitcher_stats_wOBA()
BEGIN

DECLARE Starting_Pitcher CHAR (8);
DECLARE Game_Date DATE;
DECLARE Game_Number INT;
DECLARE u_walks_a REAL;
DECLARE HBP REAL;
DECLARE singles_a REAL;
DECLARE doubles_a REAL;
DECLARE triples_a REAL;
DECLARE HR_a REAL;
DECLARE sacrifice_flies_a REAL;
DECLARE at_bats REAL;
DECLARE wOBA DECIMAL;

SELECT Starting_Pitcher, Game_Date, Game_Number, u_walks_a, HBP,singles_a,doubles_a,triples_a,HR_a,sacrifice_flies_a,at_bats,wOBA
    FROM starting_pitcher_stats
    ORDER BY Starting_Pitcher, Game_Date, Game_Number;

UPDATE starting_pitcher_stats AS a, events AS b
    SET a.wOBA=wOBA_func(wOBA)
    WHERE a.Starting_Pitcher = b.PIT_ID
      AND a.Game_ID=b.GAME_ID;

END
$$
DELIMITER ;