Sabermetrics 3

I continue covering some neat Sabermetrics formulas in this video. If you missed part 1 of my Sabermetrics Tutorial, check it out first.

I’m going to cover a few new complicated formulas including: Theoretical Team Runs Created, Batting Average on Balls in Play, Weighted On Base Average and more. I’m also going to show you how to easily save queries using views.

This tutorial has been a great deal of fun. All formulas follow the video.

If you like videos like this tell Google [googleplusone]
To make me extra happy, feel free to share it

Code From the Video

TOP 50 LOWEST ERAS (MINUS THE DEAD BALL PERIOD)

SELECT p.playerID,
CONCAT(m.nameFirst, " ", m.nameLast) AS NAME,
SUM(p.ER)/SUM(p.IPOuts/3)*9 AS ERA, # IPOuts: Total Outs
SUM(p.IPOuts/3) AS IP,
m.birthYear AS BirthYear
FROM Pitching p, Master m
WHERE p.playerID = m.playerID
GROUP BY playerID
HAVING (IP >= 1000) AND (BirthYear > 1900)
ORDER BY ERA ASC
LIMIT 50;

THEORETICAL TEAM RUNS CREATED CAREER
(Estimates the Number of Runs a Player is Responsible for)

select b.playerID, 
CONCAT(m.nameFirst, " ", m.nameLast) AS NAME,  ((b.H+b.BB)+(2.4*(b.AB+b.BB)))*(t.TB+(3*(b.AB+b.BB)))/(9*(b.AB+b.BB))-(.9*(b.AB+b.BB)) AS TTRC
FROM Batting b, Master m, TOTBASES as t
WHERE b.playerID = m.playerID AND t.playerID = m.playerID
GROUP BY playerID  
ORDER BY TTRC 
DESC LIMIT 50;

TOTAL BASES CAREER
(Single = 1 Base + Double = 2 Bases + Triple = 3 Bases + Homer = 4 Bases)

CREATE VIEW TOTBASES AS
SELECT playerID,
(SUM(H)+(SUM(2B)*2)+(SUM(3B)*3)+(SUM(HR)*4)) AS TB 
FROM Batting
GROUP BY playerID;

SELECT playerID, TB FROM TOTBASES; // CALLS FOR TOTBASES

BEST TOTAL BASES YEAR

CREATE VIEW TOTBYR AS # We create a view to store a query
SELECT playerID, yearID,
(H+(2B*2)+(3B*3)+(HR*4)) AS TB 
FROM Batting;

SELECT playerID, TB FROM TOTBYR // Returns Top 50 TB for 2010
WHERE yearID = 2010
ORDER BY TB DESC
LIMIT 50;

THEORETICAL TEAM RUNS CREATED EVER IN A YEAR

select b.yearID, b.playerID, 
CONCAT(m.nameFirst, " ", m.nameLast) AS NAME,  ((b.H+b.BB)+(2.4*(b.AB+b.BB)))*(t.TB+(3*(b.AB+b.BB)))/(9*(b.AB+b.BB))-(.9*(b.AB+b.BB)) AS TTRC
FROM Batting b, Master m, TOTBYR as t
WHERE b.playerID = m.playerID AND t.playerID = m.playerID 
AND b.yearID = t.yearID AND t.yearID = 2011 # Every year or just 2010
ORDER BY TTRC 
DESC LIMIT 50;

SHOW ALL VIEWS IN DATABASE

SHOW FULL TABLES 
IN bbstats 
WHERE TABLE_TYPE LIKE 'VIEW';

DELETE A DATABASE VIEW

drop view databaseView;

BATTING AVERAGE ON BALLS IN PLAY
(A Players Skill at Getting Hits)

SELECT b.playerID, CONCAT(m.nameFirst, " ", m.nameLast) AS NAME, 
yearID, teamID, 
(b.H-b.HR)/(b.AB-b.SO-b.HR+b.SF) AS BABIP
FROM batting b, Master m
WHERE b.playerID = m.playerID AND b.yearID = 2011 AND b.AB > 300
ORDER BY BABIP DESC
LIMIT 50;

Weighted On Base Average or wOBA
(OBP doesn't weight for how far they reached base. SLG doesn't consider walks)

SELECT b.playerID, CONCAT(m.nameFirst, " ", m.nameLast) AS NAME, 
yearID, teamID, 
(.72*(b.BB-b.IBB))+(.75*b.hbp)+(.90*b.h)+(1.84)+(1.24*b.2b)+(1.56*b.3b)+(1.95*b.hr)/b.ab AS wOBA 
FROM batting b, Master m
WHERE b.playerID = m.playerID AND b.yearID = 2011 AND b.AB > 300
ORDER BY wOBA DESC
LIMIT 50;

5 Responses to “Sabermetrics 3”

1. mr.roshik says:

   April 21, 2012 at 1:46 pm

   Thanks for sharing 🙂

   Reply
   • admin says:

     April 22, 2012 at 5:45 pm

     You’re very welcome. I love your avatar by the way 🙂

     Reply
     • mr.roshik says:

       April 22, 2012 at 9:06 pm

       thank you 🙂

       Reply
2. umair says:

   November 26, 2013 at 2:53 pm

   you are like 21st century Einstein. Do you actually remember all the stuff you have worked on so far? I just wanted to know how do you do your research.

   Thanks for your videos. I am learning a lot from you videos rather than my university.

   Reply
   • Derek Banas says:

     November 29, 2013 at 1:16 pm

     Thank you, but I’m really not that smart. Almost everything I do is based around reusing programming knowledge. For example I can’t for the life of me learn how to play a musical instrument. Since I’ve been programming since I was a boy I think my brain just learned how to do this stuff naturally. Here is a tutorial I made on study methods I use.

     Reply