Tag: mysql

Run Environment 3: The Second Run Values View

cavebutter1 Comment

Here’s what we’re doing for the next view.  We’re taking all of the run expectancy values from the previous view, making 3 more calculations that I will explain in a moment, and creating linear weights for each non-out batting event that we’ll use to derive wOBA.

runMinus establishes a run value for outs and other events that are not part of OBP.  It’s AB – H + SF, and is equivalent to a batting out.

runPlus gives us the average run value for all non-out batting events: Hits, Hit-by-Pitches, and Walks.

wOBA will give us the Weighted On Base Average for the league

wobaScale will give us the factor to apply to all of the other linear weights and for each league year in order to scale players’ wOBAs to their matching, unweighted On Base Percentage.

Two notes before the code.  First, we’re using stats from the players_career_batting_stats table.  I wrote a join to the league_history_stats table when I was thinking of doing it another way.  I may go back and switch it out to the other table as it will save us doing the sums.

Second, there’s got to be a more elegant way to do this.  I think there is, and it’s to do with defining runPlus, runMinus, and wobaScale as variables so that I don’t have to elaborate the code each time they are mentioned.  I know that I can’t use column aliases within the select statement.  So my options are leaving it as is (I am getting results that look reasonable), try my luck with variables, or create another table or view to store these values and call them from this view.

I will come back to this to try to clean it up.

 

CREATE OR REPLACE VIEW vRunValues2 AS
SELECT 
r.year
, r.league_id
, r.RperOut 
, r.runBB 
, r.runHB 
, r.run1B 
, r.run2B 
, r.run3B 
, r.runHR 
, r.runSB 
, r.runCS 
, Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr)
    + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS)
    / Sum(b.ab -b.h + b.SF) AS runMinus 
, Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr)
    + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)
    / Sum(b.BB - b.IBB + b.HP +b.H) AS runPlus 

, #1/([runPlus]+[runMinus]) AS wOBAscale 
1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr)
    + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS))
    / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP
    + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR
    + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) AS wOBAscale

, #([runBB]+[runMinus])*[wOBAscale] AS wobaBB 
(r.runBB + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B
    * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB
    * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) 
    * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B
    * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr
    + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H)
    + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B 
    * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
    + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaBB

, #([runHB]+[runMinus])*[wOBAscale] AS wobaHB 
(r.runHB +(Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B 
   * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) 
   * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) 
   + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) 
   / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP 
   + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaHB

, #([run1B]+[runMinus])*[wOBAscale] AS woba1B 
(r.run1b + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP 
   +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr 
   + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) 
   + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF)))) AS woba1B

, #([run2B]+[runMinus])*[wOBAscale] AS woba2B 
(r.run2b + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP 
   +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr 
   + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) 
   + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP 
   + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS woba2B

, #([run3B]+[runMinus])*[wOBAscale] AS woba3B 
(r.run3B + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP 
   +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr 
   + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) 
   + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF)))) AS woba3B

, #([runHR]+[runMinus])*[wOBAscale] AS wobaHR 
(r.runHR + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP 
   +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr 
   + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) 
   + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) 
   + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) 
   / Sum(b.ab -b.h + b.SF)))) AS wobaHR

, #[runSB]*[wOBAscale] AS wobaSB 
r.runSB * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) 
   + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) 
   / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP 
   + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaSB

, #[runCS]*[wOBAscale] AS wobaCS
r.runCS * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) 
   + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) 
   / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP 
   + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR 
   + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaCS

FROM 
vRunValues r
INNER JOIN players_career_batting_stats b ON r.year=b.year AND r.league_id=b.league_id
INNER JOIN league_history_batting_stats lhb ON r.year=lhb.year AND r.league_id=lhb.league_id

GROUP BY 
r.year
, r.league_id
, r.RperOut 
, r.runBB 
, r.runHB 
, r.run1B 
, r.run2B 
, r.run3B 
, r.runHR 
, r.runSB 
, r.runCS


ORDER BY 
r.year DESC;

Run Environment 2: Run Values Part 1

cavebutterLeave a comment

I am not a statistician by any stretch of the imagination.  Nor am I a sabermetrician.  I can barely follow along with the conversation threads with the big brains on Tango’s website.  As a result, I have to take some things on faith.  Sometimes I feel good about that, sometimes not.  The things I am taking on faith for this first Run Values view are giving me heartburn.  Here’s what’s happening:

In the previous view, we established a run environment for each league year.  That is, we determined how many runs were scored for every plate appearance and for every out.  In this next view, we are adding a run value to the runs per out for every non-out batting outcome.  For example, let’s say that in 2009 the RperOut for my league was .172.  That’s basically saying that, no matter the outcome, stepping up to the plate in 2009 was worth at least .172 runs on average.  That RperOut establishes a baseline to which successful outcomes will add value.  How much value?  Here’s where the heartburn comes in.

The value added is a constant for each event.  Across all years and leagues.  A walk is worth 0.14 runs more than an out in every year, league, planet, park, etc.  It’s difficult to accept for real world historical MLB and it’s even harder to accept in OOTP where the baseball environment can be much different.  I have been looking, and will continue to look, for an explanation of how these constants were derived.  Haven’t found one yet and I’m anxious to move forward, so I will accept them for now.  However, when I start getting wacky results for my OOTP stats, this is the first place I am going to look.

It’s to do with Run Expectancy, and this Tango post and this article talk about it, but I was having some trouble parsing it, so I am leaving it for now, but with some misgivings.  So to recap, this view we are adding an expected run value to the runs per out for every non-out batting outcome.  Here’s that view:

CREATE OR REPLACE VIEW vRunValues AS
SELECT l.year
, l.league_id
, l.rperout
, l.rperout+0.14 AS runBB
, l.rperout+0.14+0.025 AS runHB
, l.rperout+0.14+0.155 AS run1b
, l.rperout+0.14+0.155+0.3 AS run2b
, l.rperout+0.14+0.155+0.3+0.27 AS run3b
, 1.4 AS runHR
, 0.2 AS runSB
, (2*l.RperOut)+0.075 AS runCS
FROM vLeagueRunsPerOut AS l;

Tables 10: Team History Financials and Team Relations

cavebutterLeave a comment

Creating team_history_financials table

This table tracks a whole bunch of team financial data per year.  Useful in doing team by team financial comparisons as well as deriving fun statistics such as $/win or looking at correlation between fan interest/loyalty and wins. Of note, the inclusion of league, sub_league, and division columns is a little confusing and seems to be asking for trouble.  Those relationships are established on the ‘teams’ table and seem superfluous here.

CREATE TABLE `team_history_financials` (
  `team_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `league_id` int(11) DEFAULT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `division_id` int(11) DEFAULT NULL,
  `gate_revenue` int(11) DEFAULT NULL,
  `season_ticket_revenue` int(11) DEFAULT NULL,
  `media_revenue` int(11) DEFAULT NULL,
  `merchandising_revenue` int(11) DEFAULT NULL,
  `other_revenue` int(11) DEFAULT NULL,
  `revenue_sharing` int(11) DEFAULT NULL,
  `playoff_revenue` int(11) DEFAULT NULL,
  `cash` int(11) DEFAULT NULL,
  `cash_owner` int(11) DEFAULT NULL,
  `cash_trades` int(11) DEFAULT NULL,
  `previous_balance` int(11) DEFAULT NULL,
  `player_expenses` int(11) DEFAULT NULL,
  `staff_expenses` int(11) DEFAULT NULL,
  `stadium_expenses` int(11) DEFAULT NULL,
  `season_tickets` int(11) DEFAULT NULL,
  `attendance` int(11) DEFAULT NULL,
  `fan_interest` smallint(6) DEFAULT NULL,
  `fan_loyalty` smallint(6) DEFAULT NULL,
  `fan_modifier` smallint(6) DEFAULT NULL,
  `ticket_price` double DEFAULT NULL,
  `local_media_contract` int(11) DEFAULT NULL,
  `local_media_contract_expires` int(11) DEFAULT NULL,
  `national_media_contract` int(11) DEFAULT NULL,
  `national_media_contract_expires` int(11) DEFAULT NULL,
  `development_budget` int(11) DEFAULT NULL,
  `draft_budget` int(11) DEFAULT NULL,
  `draft_expenses` int(11) DEFAULT NULL,
  `budget` int(11) DEFAULT NULL,
  `market` smallint(6) DEFAULT NULL,
  `owner_expectation` smallint(6) DEFAULT NULL,
  `scouting_budget` int(11) DEFAULT NULL,
  `scouting_amateur` int(11) DEFAULT NULL,
  `scouting_major` int(11) DEFAULT NULL,
  `scouting_minor` int(11) DEFAULT NULL,
  `scouting_international` int(11) DEFAULT NULL,
  PRIMARY KEY (`team_id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Creating team_relations table

I think I included this table in the dump because I thought it did what team_affiliations does.  It does not.  I think this table is only useful for showing current standings without having to do complicated joins on the teams, leagues, sub_leagues, and divisions tables.  Well, not complicated exactly, but cumbersome.

CREATE TABLE `team_relations` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `division_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`,`division_id`,`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 9: Individual Batting Stats, States, and Team Affiliations

cavebutterLeave a comment

Three small tables (at least as far as column count goes) that don’t have impact on my first goal of producing a lot of statistics.  However, I do see some use for them down the road.

Creating players_individual_batting_stats table

This one pairs batters and pitchers (‘player_id’ and ‘opponent_id’, respectively) and sums the results of each AB in terms of Hits and HR.  Significantly, this is not Plate Appearances and, therefore, does not include Walks, Sacrifice Hits, or Sacrifice Flies.

CREATE TABLE `players_individual_batting_stats` (
  `player_id` int(11) NOT NULL, #Batter
  `opponent_id` int(11) NOT NULL, #Pitcher
  `ab` smallint(6) DEFAULT NULL,
  `h` smallint(6) DEFAULT NULL,
  `hr` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`player_id`,`opponent_id`),
  INDEX `pibs_ix1` (`opponent_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Creating states table

Logically, this table belongs further up with nations and cities.  It simply defines states, which reside in nations.  Cities defines cities, which reside in states, which reside in nations.  Nations reside in continents, but I have not imported that table and don’t see the need to.

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL,
  `nation_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbreviation` varchar(50) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Creating team_affiliations table

This is essentially a join table that establishes a relationship with an affiliate team.  The parent team (i.e. the Major League team) is the ‘affiliated_team_id’ and the minor league team is the ‘team_id’ team.

Actually, it could be reversed – due to an oversight I didn’t include this data in the dump – just set up the table.  It won’t be used until all the stats are done anyway – and it will be used to help identify useful players in trade negotiations.  For example, let’s say I am close to getting a deal done with Seattle and want to find one more pitching prospect from their farm system.  I’d use this table to find players on teams affiliated with Seattle.  Will have to come back to this one later, obviously.

CREATE TABLE `team_affiliations` (
  `team_id` int(11) NOT NULL,
  `affiliated_team_id` int(11) NOT NULL,
  PRIMARY KEY (`team_id`,`affiliated_team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 8: Player Career Pitching Stats

cavebutterLeave a comment

Same breakdown here as the previous career stats tables, with indexes on player_id, year, split_id, stint, and team_id.  Similar to the batting stats table, we have a couple calculated statistics here: WPA (though I am not sure how this works for pitchers…is it per AB?), and WAR.  We also have a column that I haven’t been able to identify: ‘li’.

I will come back to this in another section, but this is the table we’ll reference when aggregating data for Run Environments as we don’t have a reliable way to group league history pitching data by subleague or of calculating outs.  On this table, outs are conveniently recorded in the ‘outs’ column.

CREATE TABLE `players_career_pitching_stats` (
  `pcps_id` int(11) NOT NULL AUTO_INCREMENT,    
  `player_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) NOT NULL,
  `ip` smallint(6) DEFAULT NULL,
  `ab` smallint(6) DEFAULT NULL,
  `tb` smallint(6) DEFAULT NULL,
  `ha` smallint(6) DEFAULT NULL,
  `k` smallint(6) DEFAULT NULL,
  `bf` smallint(6) DEFAULT NULL,
  `rs` smallint(6) DEFAULT NULL,
  `bb` smallint(6) DEFAULT NULL,
  `r` smallint(6) DEFAULT NULL,
  `er` smallint(6) DEFAULT NULL,
  `gb` smallint(6) DEFAULT NULL,
  `fb` smallint(6) DEFAULT NULL,
  `pi` smallint(6) DEFAULT NULL,
  `ipf` smallint(6) DEFAULT NULL,
  `g` smallint(6) DEFAULT NULL,
  `gs` smallint(6) DEFAULT NULL,
  `w` smallint(6) DEFAULT NULL,
  `l` smallint(6) DEFAULT NULL,
  `s` smallint(6) DEFAULT NULL,
  `sa` smallint(6) DEFAULT NULL,
  `da` smallint(6) DEFAULT NULL,
  `sh` smallint(6) DEFAULT NULL,
  `sf` smallint(6) DEFAULT NULL,
  `ta` smallint(6) DEFAULT NULL,
  `hra` smallint(6) DEFAULT NULL,
  `bk` smallint(6) DEFAULT NULL,
  `ci` smallint(6) DEFAULT NULL,
  `iw` smallint(6) DEFAULT NULL,
  `wp` smallint(6) DEFAULT NULL,
  `hp` smallint(6) DEFAULT NULL,
  `gf` smallint(6) DEFAULT NULL,
  `dp` smallint(6) DEFAULT NULL,
  `qs` smallint(6) DEFAULT NULL,
  `svo` smallint(6) DEFAULT NULL,
  `bs` smallint(6) DEFAULT NULL,
  `ra` smallint(6) DEFAULT NULL,
  `cg` smallint(6) DEFAULT NULL,
  `sho` smallint(6) DEFAULT NULL,
  `sb` smallint(6) DEFAULT NULL,
  `cs` smallint(6) DEFAULT NULL,
  `hld` smallint(6) DEFAULT NULL,
  `ir` double DEFAULT NULL,
  `irs` double DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  `li` double DEFAULT NULL,
  `stint` smallint(6) NOT NULL,
  `outs` smallint(6) DEFAULT NULL,
  `war` double DEFAULT NULL,
  PRIMARY KEY (`pcps_id`),
  INDEX `pcps_ix1` (`league_id`),
  INDEX `pcps_ix2` (year),
  INDEX `pcps_ix3` (`player_id`),
  INDEX `pcps_ix4` (`team_id`),
  INDEX `pcps_ix5` (`split_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 7: Player Career Fielding Stats

cavebutterLeave a comment

Fielding stats are the last group of stats I am going to look at, so I don’t have all that much to say about this table at the moment.  I set up indexes on the same fields as I did for the Career Batting Stats table.

There are a few columns that I need to understand still:

  • ‘rto’
  • ‘ipf’
  • We also have a calculated stat for Zone Rating.  This one, too, would be pretty hard to calculate on our own (though I would like to try!)  There’s definitely not enough of the right data in this table to calculate it or UZR or other advanced defensive metrics without additional gameplay data.  It’s likely that it’s in the data dump options somewhere, but I haven’t looked hard enough yet to say.
CREATE TABLE `players_career_fielding_stats` (
  `pcfs_id` int(11) NOT NULL Auto_Increment,    
  `player_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `position` smallint(6) NOT NULL,
  `tc` smallint(6) DEFAULT NULL,
  `a` smallint(6) DEFAULT NULL,
  `po` smallint(6) DEFAULT NULL,
  `er` smallint(6) DEFAULT NULL,
  `ip` smallint(6) DEFAULT NULL,
  `g` smallint(6) DEFAULT NULL,
  `gs` smallint(6) DEFAULT NULL,
  `e` smallint(6) DEFAULT NULL,
  `dp` smallint(6) DEFAULT NULL,
  `tp` smallint(6) DEFAULT NULL,
  `pb` smallint(6) DEFAULT NULL,
  `sba` smallint(6) DEFAULT NULL,
  `rto` smallint(6) DEFAULT NULL,
  `ipf` smallint(6) DEFAULT NULL,
  `plays` smallint(6) DEFAULT NULL,
  `plays_base` smallint(6) DEFAULT NULL,
  `roe` smallint(6) DEFAULT NULL,
  `zr` double DEFAULT NULL,
  PRIMARY KEY (`pcfs_id`),
  INDEX `pcfs_ix1` (`league_id`),
  INDEX `pcfs_ix2` (year),
  INDEX `pcfs_ix3` (`player_id`),
  INDEX `pcfs_ix4` (`team_id`),
  INDEX `pcfs_ix5` (`split_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 6: Player Career Batting Stats

cavebutter1 Comment

This table contains all batting statistics for all players for all years.  If data is dumped in the middle of a year, that year’s stats are also in this table.  There are multiple records for every year based on a number of columns.  I added an AUTO_INCREMENT id column to avoid a composite PK made up of the following:

  • ‘player_id’
  • ‘year’
  • ‘split_id’ – as mentioned earlier, this breaks out a players stats overall, vs lefties, and vs righties
  • ‘stint’ – a player gets a new record if he changes teams during the course of a season

We’re indexing all of these as well as ‘team_id’.

There are two columns that contain static records of calculated stats:  ‘wpa’ and ‘war’.

Win Percentage Added  is a stat that I don’t find much use for.  Essentially it sums the percentage increase of a team’s chances of winning after an at-bat.  I believe it is a real measure, but I feel that there are other measures that better capture offensive contribution without relying on situational hitting and specific game scenarios.

Wins Above Replacement is a counting stat that takes offense, base running, and defense into account and represents it as a number of wins that a player is responsible for above a replacement level player.  It’s a complicated formula that relies on some defensive metrics that I don’t fully understand yet, so it’s nice that it’s here.  Particularly because this is a counting stat, it can stand alone.

CREATE TABLE `players_career_batting_stats` (
  `pcbs_id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) NOT NULL,
  `position` smallint(6) DEFAULT NULL,
  `ab` smallint(6) DEFAULT NULL,
  `h` smallint(6) DEFAULT NULL,
  `k` smallint(6) DEFAULT NULL,
  `pa` smallint(6) DEFAULT NULL,
  `pitches_seen` smallint(6) DEFAULT NULL,
  `g` smallint(6) DEFAULT NULL,
  `gs` smallint(6) DEFAULT NULL,
  `d` smallint(6) DEFAULT NULL,
  `t` smallint(6) DEFAULT NULL,
  `hr` smallint(6) DEFAULT NULL,
  `r` smallint(6) DEFAULT NULL,
  `rbi` smallint(6) DEFAULT NULL,
  `sb` smallint(6) DEFAULT NULL,
  `cs` smallint(6) DEFAULT NULL,
  `bb` smallint(6) DEFAULT NULL,
  `ibb` smallint(6) DEFAULT NULL,
  `gdp` smallint(6) DEFAULT NULL,
  `sh` smallint(6) DEFAULT NULL,
  `sf` smallint(6) DEFAULT NULL,
  `hp` smallint(6) DEFAULT NULL,
  `ci` smallint(6) DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  `stint` smallint(6) NOT NULL,
  `war` double DEFAULT NULL,
  PRIMARY KEY (`pcbs_id`),
  INDEX `pcbs_ix1` (`league_id`),
  INDEX `pcbs_ix2` (year),
  INDEX `pcbs_ix3` (`player_id`),
  INDEX `pcbs_ix4` (`team_id`),
  INDEX `pcbs_ix5` (`split_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 5: The Players Table

cavebutterLeave a comment

Creating players table

This table contains all of the biographic and demographic detail of all players.  A couple notes here.

  • ‘team_id’ and ‘league_id’ refer to the team and league of a given player at the time of the last data dump.  We will be ignoring these columns from this table in all of our career and prior-year stats.  They will be useful when we’re looking for a player’s team mid-season.
  • At some point, I posted a request for a new feature.  The request was that although we can view a coach’s former player profile, we can’t see a player’s coach profile if that player had retired and became a coach.  That feature can be implemented by adding a column ‘coach_id’ to this table.
CREATE TABLE `players` (
  `player_id` int(11) NOT NULL,
  `team_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `nick_name` varchar(50) DEFAULT NULL,
  `age` smallint(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `city_of_birth_id` int(11) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `second_nation_id` int(11) DEFAULT NULL,
  `weight` smallint(6) DEFAULT NULL,
  `height` smallint(6) DEFAULT NULL,
  `retired` tinyint(4) DEFAULT NULL,
  `free_agent` tinyint(4) DEFAULT NULL,
  `last_league_id` int(11) DEFAULT NULL,
  `last_team_id` int(11) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `last_organization_id` int(11) DEFAULT NULL,
  `language_ids0` int(11) DEFAULT NULL,
  `language_ids1` int(11) DEFAULT NULL,
  `uniform_number` smallint(6) DEFAULT NULL,
  `experience` smallint(6) DEFAULT NULL,
  `person_type` smallint(6) DEFAULT NULL,
  `bats` smallint(6) DEFAULT NULL,
  `throws` smallint(6) DEFAULT NULL,
  `personality_greed` smallint(6) DEFAULT NULL,
  `personality_loyalty` smallint(6) DEFAULT NULL,
  `personality_play_for_winner` smallint(6) DEFAULT NULL,
  `personality_work_ethic` smallint(6) DEFAULT NULL,
  `personality_intelligence` smallint(6) DEFAULT NULL,
  `personality_leader` smallint(6) DEFAULT NULL,
  `historical_id` varchar(50) DEFAULT NULL,
  `historical_team_id` varchar(50) DEFAULT NULL,
  `best_contract_offer_id` int(11) DEFAULT NULL,
  `injury_is_injured` tinyint(4) DEFAULT NULL,
  `injury_dtd_injury` tinyint(4) DEFAULT NULL,
  `injury_career_ending` tinyint(4) DEFAULT NULL,
  `injury_dl_left` smallint(6) DEFAULT NULL,
  `injury_dl_playoff_round` smallint(6) DEFAULT NULL,
  `injury_left` smallint(6) DEFAULT NULL,
  `dtd_injury_effect` smallint(6) DEFAULT NULL,
  `injury_id` int(11) DEFAULT NULL,
  `prone_overall` smallint(6) DEFAULT NULL,
  `prone_leg` smallint(6) DEFAULT NULL,
  `prone_back` smallint(6) DEFAULT NULL,
  `prone_arm` smallint(6) DEFAULT NULL,
  `fatigue_pitches0` smallint(6) DEFAULT NULL,
  `fatigue_pitches1` smallint(6) DEFAULT NULL,
  `fatigue_pitches2` smallint(6) DEFAULT NULL,
  `fatigue_pitches3` smallint(6) DEFAULT NULL,
  `fatigue_pitches4` smallint(6) DEFAULT NULL,
  `fatigue_pitches5` smallint(6) DEFAULT NULL,
  `fatigue_points` smallint(6) DEFAULT NULL,
  `fatigue_played_today` tinyint(4) DEFAULT NULL,
  `running_ratings_speed` smallint(6) DEFAULT NULL,
  `running_ratings_stealing` smallint(6) DEFAULT NULL,
  `running_ratings_baserunning` smallint(6) DEFAULT NULL,
  `position` smallint(6) DEFAULT NULL,
  `role` smallint(6) DEFAULT NULL,
  `college` tinyint(4) DEFAULT NULL,
  `draft_year` smallint(6) DEFAULT NULL,
  `draft_round` smallint(6) DEFAULT NULL,
  `draft_supplemental` tinyint(4) DEFAULT NULL,
  `draft_pick` smallint(6) DEFAULT NULL,
  `draft_overall_pick` smallint(6) DEFAULT NULL,
  `draft_eligible` tinyint(4) DEFAULT NULL,
  `hidden` tinyint(4) DEFAULT NULL,
  `draft_league_id` int(11) DEFAULT NULL,
  `draft_team_id` int(11) DEFAULT NULL,
  `turned_coach` tinyint(4) DEFAULT NULL,
  `hall_of_fame` tinyint(4) DEFAULT NULL,
  `rust` smallint(6) DEFAULT NULL,
  `inducted` smallint(6) DEFAULT NULL,
  `strategy_override_team` tinyint(4) DEFAULT NULL,
  `strategy_stealing` int(11) DEFAULT NULL,
  `strategy_running` int(11) DEFAULT NULL,
  `strategy_bunt_for_hit` int(11) DEFAULT NULL,
  `strategy_sac_bunt` int(11) DEFAULT NULL,
  `strategy_hit_run` int(11) DEFAULT NULL,
  `strategy_hook_start` int(11) DEFAULT NULL,
  `strategy_hook_relief` int(11) DEFAULT NULL,
  `strategy_pitch_count` int(11) DEFAULT NULL,
  `strategy_only_allow_single_inning_saves` tinyint(4) DEFAULT NULL,
  `strategy_pitch_around` int(11) DEFAULT NULL,
  `strategy_sticky_lineup` tinyint(4) DEFAULT NULL,
  `strategy_sticky_position` tinyint(4) DEFAULT NULL,
  `strategy_no_pinch_if_rested` tinyint(4) DEFAULT NULL,
  `strategy_never_pinch_hit` tinyint(4) DEFAULT NULL,
  `strategy_defensive_sub` tinyint(4) DEFAULT NULL,
  `strategy_never_defensive_sub_for` tinyint(4) DEFAULT NULL,
  `local_pop` smallint(6) DEFAULT NULL,
  `national_pop` smallint(6) DEFAULT NULL,
  `draft_protected` tinyint(4) DEFAULT NULL,
  `morale` smallint(6) DEFAULT NULL,
  `morale_player_performance` smallint(6) DEFAULT NULL,
  `morale_team_performance` smallint(6) DEFAULT NULL,
  `morale_team_transactions` smallint(6) DEFAULT NULL,
  `expectation` smallint(6) DEFAULT NULL,
  `morale_player_role` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`player_id`),
  INDEX `players_ix1` (`team_id`),
  INDEX `players_ix2` (`league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 4: Parks and Teams

cavebutterLeave a comment

Creating parks table

Parks is another one of those tables that has a ton of columns of which we need only a couple.  It’s a standalone table that doesn’t have any columns that could be construed as a foreign key (if we were using them).  We’re really only interested in ‘park_id’ and the park factor columns about halfway down:

  • ‘avg’
  • ‘avg_l’
  • ‘avg_r’
  • ‘d’
  • ‘t’
  • ‘hr’
  • ‘hr_l’
  • ‘hr_r’

And of those, at least for the time being, we’re only interested in ‘avg’.  This is the factor we’ll be using to adjust some of the advanced statistics by to account for the specific conditions of the park in which each player played his home games.

CREATE TABLE `parks` (
  `park_id` int(11) NOT NULL,
  `dimensions_x` smallint(6) DEFAULT NULL,
  `dimensions_y` smallint(6) DEFAULT NULL,
  `batter_left_x` smallint(6) DEFAULT NULL,
  `batter_left_y` smallint(6) DEFAULT NULL,
  `batter_right_x` smallint(6) DEFAULT NULL,
  `batter_right_y` smallint(6) DEFAULT NULL,
  `bases_x0` smallint(6) DEFAULT NULL,
  `bases_x1` smallint(6) DEFAULT NULL,
  `bases_x2` smallint(6) DEFAULT NULL,
  `bases_y0` smallint(6) DEFAULT NULL,
  `bases_y1` smallint(6) DEFAULT NULL,
  `bases_y2` smallint(6) DEFAULT NULL,
  `positions_x0` smallint(6) DEFAULT NULL,
  `positions_x1` smallint(6) DEFAULT NULL,
  `positions_x2` smallint(6) DEFAULT NULL,
  `positions_x3` smallint(6) DEFAULT NULL,
  `positions_x4` smallint(6) DEFAULT NULL,
  `positions_x5` smallint(6) DEFAULT NULL,
  `positions_x6` smallint(6) DEFAULT NULL,
  `positions_x7` smallint(6) DEFAULT NULL,
  `positions_x8` smallint(6) DEFAULT NULL,
  `positions_x9` smallint(6) DEFAULT NULL,
  `positions_y0` smallint(6) DEFAULT NULL,
  `positions_y1` smallint(6) DEFAULT NULL,
  `positions_y2` smallint(6) DEFAULT NULL,
  `positions_y3` smallint(6) DEFAULT NULL,
  `positions_y4` smallint(6) DEFAULT NULL,
  `positions_y5` smallint(6) DEFAULT NULL,
  `positions_y6` smallint(6) DEFAULT NULL,
  `positions_y7` smallint(6) DEFAULT NULL,
  `positions_y8` smallint(6) DEFAULT NULL,
  `positions_y9` smallint(6) DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `avg_l` double DEFAULT NULL,
  `avg_r` double DEFAULT NULL,
  `d` double DEFAULT NULL,
  `t` double DEFAULT NULL,
  `hr` double DEFAULT NULL,
  `hr_r` double DEFAULT NULL,
  `hr_l` double DEFAULT NULL,
  `temperature0` smallint(6) DEFAULT NULL,
  `temperature1` smallint(6) DEFAULT NULL,
  `temperature2` smallint(6) DEFAULT NULL,
  `temperature3` smallint(6) DEFAULT NULL,
  `temperature4` smallint(6) DEFAULT NULL,
  `temperature5` smallint(6) DEFAULT NULL,
  `temperature6` smallint(6) DEFAULT NULL,
  `temperature7` smallint(6) DEFAULT NULL,
  `temperature8` smallint(6) DEFAULT NULL,
  `temperature9` smallint(6) DEFAULT NULL,
  `temperature10` smallint(6) DEFAULT NULL,
  `temperature11` smallint(6) DEFAULT NULL,
  `rain0` smallint(6) DEFAULT NULL,
  `rain1` smallint(6) DEFAULT NULL,
  `rain2` smallint(6) DEFAULT NULL,
  `rain3` smallint(6) DEFAULT NULL,
  `rain4` smallint(6) DEFAULT NULL,
  `rain5` smallint(6) DEFAULT NULL,
  `rain6` smallint(6) DEFAULT NULL,
  `rain7` smallint(6) DEFAULT NULL,
  `rain8` smallint(6) DEFAULT NULL,
  `rain9` smallint(6) DEFAULT NULL,
  `rain10` smallint(6) DEFAULT NULL,
  `rain11` smallint(6) DEFAULT NULL,
  `wind` smallint(6) DEFAULT NULL,
  `wind_direction` smallint(6) DEFAULT NULL,
  `distances0` smallint(6) DEFAULT NULL,
  `distances1` smallint(6) DEFAULT NULL,
  `distances2` smallint(6) DEFAULT NULL,
  `distances3` smallint(6) DEFAULT NULL,
  `distances4` smallint(6) DEFAULT NULL,
  `distances5` smallint(6) DEFAULT NULL,
  `distances6` smallint(6) DEFAULT NULL,
  `wall_heights0` smallint(6) DEFAULT NULL,
  `wall_heights1` smallint(6) DEFAULT NULL,
  `wall_heights2` smallint(6) DEFAULT NULL,
  `wall_heights3` smallint(6) DEFAULT NULL,
  `wall_heights4` smallint(6) DEFAULT NULL,
  `wall_heights5` smallint(6) DEFAULT NULL,
  `wall_heights6` smallint(6) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `picture` varchar(200) DEFAULT NULL,
  `picture_night` varchar(200) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `capacity` int(11) DEFAULT NULL,
  `type` smallint(6) DEFAULT NULL,
  `foul_ground` smallint(6) DEFAULT NULL,
  `turf` tinyint(4) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `relative_path_3d_model` varchar(200) DEFAULT NULL,
  `file_name_3d_model` varchar(200) DEFAULT NULL,
  `home_team_dugout_is_at_first_base` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`park_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Creating teams table

This table contains, obviously, information about all of the teams in our OOTP universe.  Again, there are a bunch of columns that are only useful in-game.  We’re interested in the teams’ names, nicknames, league, sub_league, and park_id.

CREATE TABLE `teams` (
  `team_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(50) DEFAULT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `logo_file_name` varchar(200) DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `park_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `division_id` int(11) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `parent_team_id` int(11) DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  `prevent_any_moves` tinyint(4) DEFAULT NULL,
  `human_team` tinyint(4) DEFAULT NULL,
  `human_id` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `background_color_id` varchar(8) DEFAULT NULL,
  `text_color_id` varchar(8) DEFAULT NULL,
  `ballcaps_main_color_id` varchar(8) DEFAULT NULL,
  `ballcaps_visor_color_id` varchar(8) DEFAULT NULL,
  `jersey_main_color_id` varchar(8) DEFAULT NULL,
  `jersey_away_color_id` varchar(8) DEFAULT NULL,
  `jersey_secondary_color_id` varchar(8) DEFAULT NULL,
  `jersey_pin_stripes_color_id` varchar(8) DEFAULT NULL,
  `allstar_team` tinyint(4) DEFAULT NULL,
  `historical_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`team_id`),
  INDEX `teams_ix1` (`park_id`),
  INDEX `teams_ix2` (`league_id`),
  INDEX `teams_ix3` (`sub_league_id`),
  INDEX `teams_ix4` (`division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 3: The League History Tables.

cavebutterLeave a comment

Edited based on some really useful tips from the OOTP Community’s resident MySQL whisperer

Where in Lahman, you have to build your own views for league statistics by season, OOTP does that for you.  This is nice.  I have a couple issues with the some of the columns in these tables and I will explain them as we build them.  Also, here’s where I started adding AUTO_INCREMENT ID fields to these tables.  I tried playing around with massive, composite PK’s and it wasn’t good.

Creating the league_history table

So, I thought this table was going to be useful when I configured the MySQL dump in-game.  Turns out, it’s not all that useful for my purposes.  It just identifies the award winners for each year in each league and sub_league.  More out of laziness than anything else, I am keeping it in the schema:

CREATE TABLE `league_history` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `best_hitter_id` int(11) DEFAULT NULL,
  `best_pitcher_id` int(11) DEFAULT NULL,
  `best_rookie_id` int(11) DEFAULT NULL,
  `best_manager_id` int(11) DEFAULT NULL,
  `best_fielder_id0` int(11) DEFAULT NULL,
  `best_fielder_id1` int(11) DEFAULT NULL,
  `best_fielder_id2` int(11) DEFAULT NULL,
  `best_fielder_id3` int(11) DEFAULT NULL,
  `best_fielder_id4` int(11) DEFAULT NULL,
  `best_fielder_id5` int(11) DEFAULT NULL,
  `best_fielder_id6` int(11) DEFAULT NULL,
  `best_fielder_id7` int(11) DEFAULT NULL,
  `best_fielder_id8` int(11) DEFAULT NULL,
  `best_fielder_id9` int(11) DEFAULT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create league_history_batting_stats table

There are a couple puzzles in this and the next two tables.  The columns ‘game_id’ and ‘team_id’ are hard to figure out.  In fact, I have not figured them out.  Their purpose, I’m sure, is to identify the sub_league.  Unfortunately, it doesn’t use a column called ‘sub_league’.  I haven’t been able to find any table that links either of these columns to sub_league.  So, there’s not much we can do with them.  Moreover, we’ll still have to create a view later on that sums the totals for each ‘team_id’ and ‘game_id’ pair.  I even posted about this on the OOTP Boards, but no one has chimed in yet.  Wishing doesn’t make it true.  Those columns most likely do not represent usable data.

‘split_id’ refers to stats accumulated against all pitchers, just righties, just lefties.  This is important at the player level.  At the league level, though, all split_id=0.

Also, this table conveniently tracks singles, ‘s’, as a column.  Would be nice if it did the same at the player level to save us the calculation in some statistics, but oh well.

Note that I added an AUTO_INCREMENT ID as PK and indexed ‘year’, ‘league_id’, and ‘split_id’.

CREATE TABLE `league_history_batting_stats` (
  `lhbs_id` int(11) NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) NOT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `pa` int(11) DEFAULT NULL,
  `ab` int(11) DEFAULT NULL,
  `h` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `tb` int(11) DEFAULT NULL,
  `s` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `t` int(11) DEFAULT NULL,
  `hr` int(11) DEFAULT NULL,
  `sb` int(11) DEFAULT NULL,
  `cs` int(11) DEFAULT NULL,
  `rbi` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `bb` int(11) DEFAULT NULL,
  `ibb` int(11) DEFAULT NULL,
  `hp` int(11) DEFAULT NULL,
  `sh` int(11) DEFAULT NULL,
  `sf` int(11) DEFAULT NULL,
  `ci` int(11) DEFAULT NULL,
  `gdp` int(11) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `ebh` int(11) DEFAULT NULL,
  `pitches_seen` int(11) DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `obp` double DEFAULT NULL,
  `slg` double DEFAULT NULL,
  `rc` double DEFAULT NULL,
  `rc27` double DEFAULT NULL,
  `iso` double DEFAULT NULL,
  `woba` double DEFAULT NULL,
  `ops` double DEFAULT NULL,
  `sbp` double DEFAULT NULL,
  `ws` double DEFAULT NULL,
  `kp` double DEFAULT NULL,
  `bbp` double DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  `babip` double DEFAULT NULL,
  PRIMARY KEY (`lhbs_id`),
  INDEX `lhbs_ix1` (`year`),
  INDEX `lhbs_ix2` (`league_id`),
  INDEX `lhbs_ix3` (`split_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create  league_history_fielding_stats table

Similar to the above, but for fielding stats at league level.  I haven’t yet dug into what fielding stats I will be calculating, so I don’t have all that much to say about this table.

CREATE TABLE `league_history_fielding_stats` (
  `lhfs_id` int(11) NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) NOT NULL,
  `position` smallint(6) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `tc` int(11) DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `po` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `dp` int(11) DEFAULT NULL,
  `tp` int(11) DEFAULT NULL,
  `pb` int(11) DEFAULT NULL,
  `sba` int(11) DEFAULT NULL,
  `rto` int(11) DEFAULT NULL,
  `er` int(11) DEFAULT NULL,
  `ip` int(11) DEFAULT NULL,
  `ipf` int(11) DEFAULT NULL,
  `pct` double DEFAULT NULL,
  `range` double DEFAULT NULL,
  `rtop` double DEFAULT NULL,
  `cera` double DEFAULT NULL,
  `zr` double DEFAULT NULL,
  `plays` int(11) DEFAULT NULL,
  `plays_base` int(11) DEFAULT NULL,
  `roe` int(11) DEFAULT NULL,
  `eff` int(11) DEFAULT NULL,
  PRIMARY KEY (`lhfs_id`),
  INDEX `lhfs_ix1` (`year`),
  INDEX `lhfs_ix2` (`league_id`),
  INDEX `lhfs_ix3` (`split_id`) 
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create league_history_pitching_stats table

This table may come in handy at some point – I haven’t done a lot of work on pitching stats yet.  But it does NOT come in handy for establishing a run environment for a league.  Why?  Because you can’t determine the number of outs!  It’s strange – as the player table has an outs column.  This table has an IP column, which would be OK if it weren’t an integer.  IP is usually tracked with a baseball-specific decimal number (x.y) where x is the number of complete innings pitched and y (0, 1, 2)  represent the outs of incomplete innings.  What are you supposed to do with only the integer?  Yes, well, apparently the column ‘ipf’  stands for Innings Pitched Fraction.  So, Outs = (3*IP) + IPF.  Anyway, I am sure I will find plenty of uses for this table in the future:

CREATE TABLE `league_history_pitching_stats` (
  `lhps_id` int(11)  NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) NOT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `ab` int(11) DEFAULT NULL,
  `ip` int(11) DEFAULT NULL,
  `bf` int(11) DEFAULT NULL,
  `tb` int(11) DEFAULT NULL,
  `ha` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `rs` int(11) DEFAULT NULL,
  `bb` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `er` int(11) DEFAULT NULL,
  `gb` int(11) DEFAULT NULL,
  `fb` int(11) DEFAULT NULL,
  `pi` int(11) DEFAULT NULL,
  `ipf` int(11) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `w` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `s` int(11) DEFAULT NULL,
  `sa` int(11) DEFAULT NULL,
  `da` int(11) DEFAULT NULL,
  `sh` int(11) DEFAULT NULL,
  `sf` int(11) DEFAULT NULL,
  `ta` int(11) DEFAULT NULL,
  `hra` int(11) DEFAULT NULL,
  `bk` int(11) DEFAULT NULL,
  `ci` int(11) DEFAULT NULL,
  `iw` int(11) DEFAULT NULL,
  `wp` int(11) DEFAULT NULL,
  `hp` int(11) DEFAULT NULL,
  `gf` int(11) DEFAULT NULL,
  `dp` int(11) DEFAULT NULL,
  `qs` int(11) DEFAULT NULL,
  `svo` int(11) DEFAULT NULL,
  `bs` int(11) DEFAULT NULL,
  `ra` int(11) DEFAULT NULL,
  `ir` int(11) DEFAULT NULL,
  `irs` int(11) DEFAULT NULL,
  `cg` int(11) DEFAULT NULL,
  `sho` int(11) DEFAULT NULL,
  `sb` int(11) DEFAULT NULL,
  `cs` int(11) DEFAULT NULL,
  `hld` int(11) DEFAULT NULL,
  `r9` double DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `obp` double DEFAULT NULL,
  `slg` double DEFAULT NULL,
  `ops` double DEFAULT NULL,
  `h9` double DEFAULT NULL,
  `k9` double DEFAULT NULL,
  `hr9` double DEFAULT NULL,
  `bb9` double DEFAULT NULL,
  `cgp` double DEFAULT NULL,
  `fip` double DEFAULT NULL,
  `qsp` double DEFAULT NULL,
  `winp` double DEFAULT NULL,
  `rsg` double DEFAULT NULL,
  `svp` double DEFAULT NULL,
  `bsvp` double DEFAULT NULL,
  `irsp` double DEFAULT NULL,
  `gfp` double DEFAULT NULL,
  `era` double DEFAULT NULL,
  `pig` double DEFAULT NULL,
  `ws` double DEFAULT NULL,
  `whip` double DEFAULT NULL,
  `gbfbp` double DEFAULT NULL,
  `kbb` double DEFAULT NULL,
  `babip` double DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  PRIMARY KEY (`lhps_id`),
  INDEX `lhps_ix1` (`year`),
  INDEX `lhps_ix2` (`league_id`),
  INDEX `lhps_ix3` (`split_id`)     
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;