Skip to content

Instantly share code, notes, and snippets.

@SpencerBingol
Created March 1, 2014 16:55
Show Gist options
  • Save SpencerBingol/9292934 to your computer and use it in GitHub Desktop.
Save SpencerBingol/9292934 to your computer and use it in GitHub Desktop.
[MYSQL] Multi-year wOBA Function for Retrosheet
-- 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 ;
@LeeZee1
Copy link

LeeZee1 commented Dec 30, 2015

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

@LeeZee1
Copy link

LeeZee1 commented Jan 1, 2016

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 ;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment