This query goes against the retrosheet database loaded into MySql
on base percentage is calculated this way
# Replace these parameters with what you want to use.
SET @fromYear = '2004';
SET @thruYear = '2019';
SET @startInning = 7; # Start from the 7th inning
SET @secondHalfStartMonth = 7; # July
select
s.Team,
ind,
obp * 1000 as obp
from (
select
case
when e.home_team_id = 'FLO' then 'MIA'
when e.home_team_id = 'MON' then 'WSH'
else e.home_team_id
end
as Team
,'H1' as ind
,(
sum(case when event_cd = '20' then 1 else 0 end) #1b
+ sum(case when event_cd = '21' then 1 else 0 end) #2b
+ sum(case when event_cd = '22' then 1 else 0 end) # 3b
+ sum(case when event_cd = '23' then 1 else 0 end) # hr
+ sum(case when event_cd = '14' then 1 else 0 end) # bb
+ sum(case when event_cd = '15' then 1 else 0 end) # ibb
+ sum(case when event_cd = '16' then 1 else 0 end) # hbp
) /
(
sum(case when ab_fl = 'T' then 1 else 0 end) #ab
+ sum(case when event_cd = '14' then 1 else 0 end) # bb
+ sum(case when event_cd = '15' then 1 else 0 end) # ibb
+ sum(case when event_cd = '16' then 1 else 0 end) # hbp
+ sum(case when sf_fl = 'T' then 1 else 0 end) #sf
) as obp
from
events e
, games g
where
e.game_id = g.game_id
and e.inn_ct >= @startInning
and e.year_id >= @fromYear and e.year_id <= @thruYear
and EXTRACT(MONTH FROM g.game_dt) < @secondHalfStartMonth
and e.BAT_HOME_ID = 1
group by
e.home_team_id, 2
) s
union
select
s.Team,
ind,
obp * 1000 as obp
from (
select
case
when e.away_team_id = 'FLO' then 'MIA'
when e.away_team_id = 'MON' then 'WSH'
else e.away_team_id
end
as Team
, 'R1' as ind
,(
sum(case when event_cd = '20' then 1 else 0 end) #1b
+ sum(case when event_cd = '21' then 1 else 0 end) #2b
+ sum(case when event_cd = '22' then 1 else 0 end) # 3b
+ sum(case when event_cd = '23' then 1 else 0 end) # hr
+ sum(case when event_cd = '14' then 1 else 0 end) # bb
+ sum(case when event_cd = '15' then 1 else 0 end) # ibb
+ sum(case when event_cd = '16' then 1 else 0 end) # hbp
) /
(
sum(case when ab_fl = 'T' then 1 else 0 end) #ab
+ sum(case when event_cd = '14' then 1 else 0 end) # bb
+ sum(case when event_cd = '15' then 1 else 0 end) # ibb
+ sum(case when event_cd = '16' then 1 else 0 end) # hbp
+ sum(case when sf_fl = 'T' then 1 else 0 end) #sf
) as obp
from
events e
, games g
where
e.game_id = g.game_id
and e.inn_ct >= @startInning
and e.year_id >= @fromYear and e.year_id <= @thruYear
and EXTRACT(MONTH FROM g.game_dt) < @secondHalfStartMonth
and e.BAT_HOME_ID = 0
group by
e.away_team_id, 2
) s
#
# 2nd half of the season
#
union
select
s.Team,
ind,
obp * 1000 as obp
from (
select
case
when e.home_team_id = 'FLO' then 'MIA'
when e.home_team_id = 'MON' then 'WSH'
else e.home_team_id
end
as Team
,'H2' as ind
,(
sum(case when event_cd = '20' then 1 else 0 end) #1b
+ sum(case when event_cd = '21' then 1 else 0 end) #2b
+ sum(case when event_cd = '22' then 1 else 0 end) # 3b
+ sum(case when event_cd = '23' then 1 else 0 end) # hr
+ sum(case when event_cd = '14' then 1 else 0 end) # bb
+ sum(case when event_cd = '15' then 1 else 0 end) # ibb
+ sum(case when event_cd = '16' then 1 else 0 end) # hbp
) /
(
sum(case when ab_fl = 'T' then 1 else 0 end) #ab
+ sum(case when event_cd = '14' then 1 else 0 end) # bb
+ sum(case when event_cd = '15' then 1 else 0 end) # ibb
+ sum(case when event_cd = '16' then 1 else 0 end) # hbp
+ sum(case when sf_fl = 'T' then 1 else 0 end) #sf
) as obp
from
events e
, games g
where
e.game_id = g.game_id
and e.inn_ct >= @startInning
and e.year_id >= @fromYear and e.year_id <= @thruYear
and EXTRACT(MONTH FROM g.game_dt) >= @secondHalfStartMonth
and e.BAT_HOME_ID = 1
group by
e.home_team_id, 2
) s
union
select
s.Team,
ind,
obp * 1000 as obp
from (
select
case
when e.away_team_id = 'FLO' then 'MIA'
when e.away_team_id = 'MON' then 'WSH'
else e.away_team_id
end
as Team
, 'R2' as ind
,(
sum(case when event_cd = '20' then 1 else 0 end) #1b
+ sum(case when event_cd = '21' then 1 else 0 end) #2b
+ sum(case when event_cd = '22' then 1 else 0 end) # 3b
+ sum(case when event_cd = '23' then 1 else 0 end) # hr
+ sum(case when event_cd = '14' then 1 else 0 end) # bb
+ sum(case when event_cd = '15' then 1 else 0 end) # ibb
+ sum(case when event_cd = '16' then 1 else 0 end) # hbp
) /
(
sum(case when ab_fl = 'T' then 1 else 0 end) #ab
+ sum(case when event_cd = '14' then 1 else 0 end) # bb
+ sum(case when event_cd = '15' then 1 else 0 end) # ibb
+ sum(case when event_cd = '16' then 1 else 0 end) # hbp
+ sum(case when sf_fl = 'T' then 1 else 0 end) #sf
) as obp
from
events e
, games g
where
e.game_id = g.game_id
and e.inn_ct >= @startInning
and e.year_id >= @fromYear and e.year_id <= @thruYear
and EXTRACT(MONTH FROM g.game_dt) >= @secondHalfStartMonth
and e.BAT_HOME_ID = 0
group by
e.away_team_id, 2
) s
order by
1, 2
;
Comments