Pitching game log

31 views
Skip to first unread message

John Choiniere

unread,
Nov 30, 2013, 12:49:50 PM11/30/13
to baseball-sq...@googlegroups.com
I've found it useful from time to time to have a gamelog table for pitchers.  Mine includes mostly just basic box score stuff, but it wouldn't be hard to customize it to include other information you might want.  Here's the code I used; as ever, if you see mistakes point them out!

create table pitching_gamelog AS
select
/* player and game data */
a.res_pit_ID AS player_ID,
if(a.bat_home_ID = 1,a.away_team_ID,substring(a.game_ID,1,3)) AS team,
if(if(a.bat_home_ID = 1,a.away_team_ID,substring(a.game_ID,1,3)) = a.away_team_ID,"road","home") AS loc,
concat(
substring(a.game_ID,10,2),"-",
if(substring(a.game_ID,8,2)="01","Jan",
if(substring(a.game_ID,8,2)="02","Feb",
if(substring(a.game_ID,8,2)="03","Mar",
if(substring(a.game_ID,8,2)="04","Apr",
if(substring(a.game_ID,8,2)="05","May",
if(substring(a.game_ID,8,2)="06","Jun",
if(substring(a.game_ID,8,2)="07","Jul",
if(substring(a.game_ID,8,2)="08","Aug",
if(substring(a.game_ID,8,2)="09","Sep",
if(substring(a.game_ID,8,2)="10","Oct",
if(substring(a.game_ID,8,2)="11","Nov","Dec"))))))))))),
"-",substring(a.game_ID,4,4)) AS date,
/* stats area - modify this stuff if you want other information */
if(if(a.bat_home_ID = "T",a.away_team_ID,substring(a.game_ID,1,3)) = a.away_team_ID,b.away_score_ct,b.home_score_ct) AS team_runs,
if(if(a.bat_home_ID = "T",a.away_team_ID,substring(a.game_ID,1,3)) = a.away_team_ID,b.home_score_ct,b.away_score_ct) AS opp_runs,
sum(a.event_outs_ct)/3 AS innings,
sum(a.balls_ct + strikes_ct) AS pitch_count,
sum(a.balls_ct) AS balls_count,
sum(a.strikes_ct) AS strikes_count,
sum(a.event_runs_ct) AS runs_allowed,
sum(if(a.h_cd != 0,1,0)) AS hits_allowed,
sum(if(a.event_cd = 14 OR a.event_cd = 15,1,0)) AS walks_allowed,
sum(if(a.event_cd = 21,1,0)) AS 2B,
sum(if(a.event_cd = 22,1,0)) AS 3B,
sum(if(a.event_cd = 23,1,0)) AS HR,
sum(if(a.event_cd = 16,1,0)) AS HBP,
sum(if(a.battedball_cd = "G",1,0)) AS GB,
sum(if(a.battedball_cd = "F",1,0)) AS FB,
sum(if(a.battedball_cd = "L",1,0)) AS LD,
sum(if(a.event_cd = 9,1,0)) AS wild_pitch,
sum(if(a.bat_event_FL = "T",1,0)) AS TBF,
sum(if(a.ab_fl = "T",1,0)) AS AB,
if(a.res_pit_ID = b.away_start_pit_ID OR a.res_pit_ID = b.home_start_pit_ID,"Starter","Reliever") AS pitcher_type,
if(a.res_pit_ID = b.win_pit_ID,"W",if(a.res_pit_ID = b.lose_pit_ID,"L",if(a.res_pit_ID = b.save_pit_ID,"S","ND"))) AS game_decision,
a.game_ID AS game_ID

/* As-is, includes only non-playoff games; just change table names below if you want to include playoffs */
from events_regseason a
inner join games_regseason b
on a.game_ID = b.game_ID
group by a.res_pit_ID, a.game_ID
order by a.res_pit_ID, substring(a.game_ID,4,9) asc
pitching gamelog build.sql

jonath...@gmail.com

unread,
Nov 30, 2013, 1:00:14 PM11/30/13
to baseball-sq...@googlegroups.com
This is really cool John. Is this from the Retrosheet db? I don't have the x_regseason tables in mine.  Did you create those tables or are you using a different database?

John Choiniere

unread,
Nov 30, 2013, 1:06:42 PM11/30/13
to baseball-sq...@googlegroups.com
They're homemade from "events" and "games" respectively.  I manually, using bbref, found the dates of the last regular season games, then did this:

Step 1: Duplicate events and games exactly as-is, naming them events_regseason and games_regseason (or whatever you want, just keep track throughout)
Step 2: Run this code

DELETE from games_regseason
where (substring(game_id,4,4)="1974" AND substring(game_id,4,8)>="19741003");
DELETE from games_regseason
where (substring(game_id,4,4)="1975" AND substring(game_id,4,8)>="19751001");
DELETE from games_regseason
where (substring(game_id,4,4)="1976" AND substring(game_id,4,8)>="19761005");
DELETE from games_regseason
where (substring(game_id,4,4)="1977" AND substring(game_id,4,8)>="19771004");
DELETE from games_regseason
where (substring(game_id,4,4)="1978" AND substring(game_id,4,8)>="19781003");
DELETE from games_regseason
where (substring(game_id,4,4)="1979" AND substring(game_id,4,8)>="19791002");
DELETE from games_regseason
where (substring(game_id,4,4)="1980" AND substring(game_id,4,8)>="19801006");
DELETE from games_regseason
where (substring(game_id,4,4)="1981" AND substring(game_id,4,8)>="19811005");
DELETE from games_regseason
where (substring(game_id,4,4)="1982" AND substring(game_id,4,8)>="19821005");
DELETE from games_regseason
where (substring(game_id,4,4)="1983" AND substring(game_id,4,8)>="19831004");
DELETE from games_regseason
where (substring(game_id,4,4)="1984" AND substring(game_id,4,8)>="19841002");
DELETE from games_regseason
where (substring(game_id,4,4)="1985" AND substring(game_id,4,8)>="19851008");
DELETE from games_regseason
where (substring(game_id,4,4)="1986" AND substring(game_id,4,8)>="19861007");
DELETE from games_regseason
where (substring(game_id,4,4)="1987" AND substring(game_id,4,8)>="19871006");
DELETE from games_regseason
where (substring(game_id,4,4)="1988" AND substring(game_id,4,8)>="19881004");
DELETE from games_regseason
where (substring(game_id,4,4)="1989" AND substring(game_id,4,8)>="19891003");
DELETE from games_regseason
where (substring(game_id,4,4)="1990" AND substring(game_id,4,8)>="19901005");
DELETE from games_regseason
where (substring(game_id,4,4)="1991" AND substring(game_id,4,8)>="19911008");
DELETE from games_regseason
where (substring(game_id,4,4)="1992" AND substring(game_id,4,8)>="19921006");
DELETE from games_regseason
where (substring(game_id,4,4)="1993" AND substring(game_id,4,8)>="19931005");
DELETE from games_regseason
where (substring(game_id,4,4)="1994" AND substring(game_id,4,8)>="19940810");
DELETE from games_regseason
where (substring(game_id,4,4)="1995" AND substring(game_id,4,8)>="19951003");
DELETE from games_regseason
where (substring(game_id,4,4)="1996" AND substring(game_id,4,8)>="19961001");
DELETE from games_regseason
where (substring(game_id,4,4)="1997" AND substring(game_id,4,8)>="19970930");
DELETE from games_regseason
where (substring(game_id,4,4)="1998" AND substring(game_id,4,8)>="19980929");
DELETE from games_regseason
where (substring(game_id,4,4)="1999" AND substring(game_id,4,8)>="19991006");
DELETE from games_regseason
where (substring(game_id,4,4)="2000" AND substring(game_id,4,8)>="20001003");
DELETE from games_regseason
where (substring(game_id,4,4)="2001" AND substring(game_id,4,8)>="20011009");
DELETE from games_regseason
where (substring(game_id,4,4)="2002" AND substring(game_id,4,8)>="20021002");
DELETE from games_regseason
where (substring(game_id,4,4)="2003" AND substring(game_id,4,8)>="20030930");
DELETE from games_regseason
where (substring(game_id,4,4)="2004" AND substring(game_id,4,8)>="20041005");
DELETE from games_regseason
where (substring(game_id,4,4)="2005" AND substring(game_id,4,8)>="20051004");
DELETE from games_regseason
where (substring(game_id,4,4)="2006" AND substring(game_id,4,8)>="20061003");
DELETE from games_regseason
where (substring(game_id,4,4)="2007" AND substring(game_id,4,8)>="20071002");
DELETE from games_regseason
where (substring(game_id,4,4)="2008" AND substring(game_id,4,8)>="20081001");
DELETE from games_regseason
where (substring(game_id,4,4)="2009" AND substring(game_id,4,8)>="20091007");
DELETE from games_regseason
where (substring(game_id,4,4)="2010" AND substring(game_id,4,8)>="20101005");
DELETE from games_regseason
where (substring(game_id,4,4)="2011" AND substring(game_id,4,8)>="20110930");
DELETE from games_regseason
where (substring(game_id,4,4)="2012" AND substring(game_id,4,8)>="20121005");
DELETE from events_regseason
where (substring(game_id,4,4)="1974" AND substring(game_id,4,8)>="19741003");
DELETE from events_regseason
where (substring(game_id,4,4)="1975" AND substring(game_id,4,8)>="19751001");
DELETE from events_regseason
where (substring(game_id,4,4)="1976" AND substring(game_id,4,8)>="19761005");
DELETE from events_regseason
where (substring(game_id,4,4)="1977" AND substring(game_id,4,8)>="19771004");
DELETE from events_regseason
where (substring(game_id,4,4)="1978" AND substring(game_id,4,8)>="19781003");
DELETE from events_regseason
where (substring(game_id,4,4)="1979" AND substring(game_id,4,8)>="19791002");
DELETE from events_regseason
where (substring(game_id,4,4)="1980" AND substring(game_id,4,8)>="19801006");
DELETE from events_regseason
where (substring(game_id,4,4)="1981" AND substring(game_id,4,8)>="19811005");
DELETE from events_regseason
where (substring(game_id,4,4)="1982" AND substring(game_id,4,8)>="19821005");
DELETE from events_regseason
where (substring(game_id,4,4)="1983" AND substring(game_id,4,8)>="19831004");
DELETE from events_regseason
where (substring(game_id,4,4)="1984" AND substring(game_id,4,8)>="19841002");
DELETE from events_regseason
where (substring(game_id,4,4)="1985" AND substring(game_id,4,8)>="19851008");
DELETE from events_regseason
where (substring(game_id,4,4)="1986" AND substring(game_id,4,8)>="19861007");
DELETE from events_regseason
where (substring(game_id,4,4)="1987" AND substring(game_id,4,8)>="19871006");
DELETE from events_regseason
where (substring(game_id,4,4)="1988" AND substring(game_id,4,8)>="19881004");
DELETE from events_regseason
where (substring(game_id,4,4)="1989" AND substring(game_id,4,8)>="19891003");
DELETE from events_regseason
where (substring(game_id,4,4)="1990" AND substring(game_id,4,8)>="19901005");
DELETE from events_regseason
where (substring(game_id,4,4)="1991" AND substring(game_id,4,8)>="19911008");
DELETE from events_regseason
where (substring(game_id,4,4)="1992" AND substring(game_id,4,8)>="19921006");
DELETE from events_regseason
where (substring(game_id,4,4)="1993" AND substring(game_id,4,8)>="19931005");
DELETE from events_regseason
where (substring(game_id,4,4)="1994" AND substring(game_id,4,8)>="19940810");
DELETE from events_regseason
where (substring(game_id,4,4)="1995" AND substring(game_id,4,8)>="19951003");
DELETE from events_regseason
where (substring(game_id,4,4)="1996" AND substring(game_id,4,8)>="19961001");
DELETE from events_regseason
where (substring(game_id,4,4)="1997" AND substring(game_id,4,8)>="19970930");
DELETE from events_regseason
where (substring(game_id,4,4)="1998" AND substring(game_id,4,8)>="19980929");
DELETE from events_regseason
where (substring(game_id,4,4)="1999" AND substring(game_id,4,8)>="19991006");
DELETE from events_regseason
where (substring(game_id,4,4)="2000" AND substring(game_id,4,8)>="20001003");
DELETE from events_regseason
where (substring(game_id,4,4)="2001" AND substring(game_id,4,8)>="20011009");
DELETE from events_regseason
where (substring(game_id,4,4)="2002" AND substring(game_id,4,8)>="20021002");
DELETE from events_regseason
where (substring(game_id,4,4)="2003" AND substring(game_id,4,8)>="20030930");
DELETE from events_regseason
where (substring(game_id,4,4)="2004" AND substring(game_id,4,8)>="20041005");
DELETE from events_regseason
where (substring(game_id,4,4)="2005" AND substring(game_id,4,8)>="20051004");
DELETE from events_regseason
where (substring(game_id,4,4)="2006" AND substring(game_id,4,8)>="20061003");
DELETE from events_regseason
where (substring(game_id,4,4)="2007" AND substring(game_id,4,8)>="20071002");
DELETE from events_regseason
where (substring(game_id,4,4)="2008" AND substring(game_id,4,8)>="20081001");
DELETE from events_regseason
where (substring(game_id,4,4)="2009" AND substring(game_id,4,8)>="20091007");
DELETE from events_regseason
where (substring(game_id,4,4)="2010" AND substring(game_id,4,8)>="20101005");
DELETE from events_regseason
where (substring(game_id,4,4)="2011" AND substring(game_id,4,8)>="20110930");
DELETE from events_regseason
where (substring(game_id,4,4)="2012" AND substring(game_id,4,8)>="20121005");

It's nice to have the option of which table to use depending on if you're interested in playoffs or not; also makes it easy to see the effects of including playoff games by running the same query on each.

On Saturday, November 30, 2013 10:00:14 AM UTC-8, jonath...@gmail.com wrote:
This is really cool John. Is this from the Retrosheet db? I don't have the x_regseason tables in mine.  Did you create those tables or are you using a different database?

On Saturday, November 30, 2013 10:49:50 AM UTC-7, John Choiniere wrote:
I've found it useful from time to time to have a gamelog table for pitchers.  Mine includes mostly just basic box score stuff, but it wouldn't be hard to customize it to include other information you might want.  Here's the code I used; as ever, if you see mistakes point them out!

*snipped*
Reply all
Reply to author
Forward
0 new messages