A short post for three pretty straightforward rate stats: Slugging Percentage, On Base Plus Slugging, and Isolated Power.
All three are measures of a batter’s hitting for power. Keeping with my tradition here of not explaining things that I understand, I will get straight to the point. Seeking to keep things simple and readable, and wanting to show off my mad variable skills, I set them all as variables, including batting average. The result is:
DROP TABLE IF EXISTS CalcBatting;
CREATE TABLE IF NOT EXISTS CalcBatting AS
SELECT b.year
, b.league_id
, b.player_id
, b.stint #We can eventually move this down the list
, b.split_id #We can eventually remove
, b.team_id #We can eventually move this down the list
, l.abbr as Lg
, t.abbr as Team
, b.g
, b.ab
, @PA := b.ab+b.bb+b.sh+b.sf+b.hp AS PA
, @BA := round(b.h/b.ab,3) AS ba
, round(b.k/@PA,3) as krate
, round((b.bb)/@PA,3) as bbrate
, @OBP := round((b.h + b.bb + b.hp)/(@PA-b.sh-b.ci),3) AS obp
, round(100*(@OBP/r.woba),0) as OBPplus
, @SLG := round((b.h+b.d+2*b.t+3*b.hr)/b.ab,3) as slg
, round(@OBP+@SLG,3) as ops
, round(@SLG-@BA,3) as iso
FROM
players_career_batting_stats b
INNER JOIN leagues l ON b.league_id=l.league_id
INNER JOIN teams t ON b.team_id=t.team_id
INNER JOIN tblRunValues2 r ON b.year=r.year AND b.league_id=r.league_id
WHERE b.ab<>0 AND b.split_id=1
ORDER BY b.player_id, b.year
The full script as it stands now:
#Calculated batting stats for OOTP
DROP TABLE IF EXISTS CalcBatting;
CREATE TABLE IF NOT EXISTS CalcBatting AS
SELECT b.year
, b.league_id
, b.player_id
, b.stint #We can eventually move this down the list
, b.split_id #We can eventually remove
, b.team_id #We can eventually move this down the list
, l.abbr as Lg
, t.abbr as Team
, b.g
, b.ab
, @PA := b.ab+b.bb+b.sh+b.sf+b.hp AS PA
, b.r
, b.h
, b.d
, b.t
, b.hr
, b.rbi
, b.sb
, b.cs
, b.bb
, b.k
, b.ibb
, b.hp
, b.sh
, b.sf
, b.gdp
, b.ci
, @BA := round(b.h/b.ab,3) AS ba
, round(b.k/@PA,3) as krate
, round((b.bb)/@PA,3) as bbrate
, @OBP := round((b.h + b.bb + b.hp)/(@PA-b.sh-b.ci),3) AS obp
, round(100*(@OBP/r.woba),0) as OBPplus
, @SLG := round((b.h+b.d+2*b.t+3*b.hr)/b.ab,3) as slg
, round(@OBP+@SLG,3) as ops
, round(@SLG-@BA,3) as iso
/* NOT yet modified for OOTP and MySQL
, round((r.wobaBB*nz(b.bb,0) + r.wobahb*nz(b.hbp,0) + r.woba1b*(b.h-b.[2b]-b.[3b]-b.hr) + r.woba2b*b.[2b] + r.woba3b*b.[3b] + r.wobahr*b.hr)/(b.ab+nz(b.bb,0)-nz(b.ibb,0)+nz(b.sf,0)+nz(b.hbp,0)),3) as woba
, round((([woba]-r.lgwoba)/r.wobascale)*[PA],1) as wRAA
, round(((([woba]-r.lgwoba)/r.wobascale)+(l.totr/l.totpa))*[PA],0) as wRC
, ((([wRAA]/[PA] + l.RperPA) + (l.RperPA - t.bpf*l.RperPA))/(lb.wRC/lb.PA))*100 AS [wRC+]
*/
FROM
players_career_batting_stats b
INNER JOIN leagues l ON b.league_id=l.league_id
INNER JOIN teams t ON b.team_id=t.team_id
INNER JOIN tblRunValues2 r ON b.year=r.year AND b.league_id=r.league_id
WHERE b.ab<>0 AND b.split_id=1
ORDER BY b.player_id, b.year