players | MySQL MyStruggle

Tables 7: Player Career Fielding Stats

September 21, 2017 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

September 21, 2017 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

September 21, 2017 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;