class PitchStats_By_Team_View(db.Base): __table__ = create_view( name="pitchstats_by_team", selectable=select([ db.PitchStats.player_id.label("id"), db.PitchStats.player_id_mlb.label("mlb_id"), db.PitchStats.player_id_bbref.label("bbref_id"), db.PitchStats.player_team_id_bbref.label("team_id_bbref"), func.count(db.PitchStats.id).label("total_games"), func.sum(db.PitchStats.is_sp).label("games_as_sp"), func.sum(db.PitchStats.is_rp).label("games_as_rp"), func.sum(db.PitchStats.is_wp).label("wins"), func.sum(db.PitchStats.is_lp).label("losses"), func.sum(db.PitchStats.is_sv).label("saves"), innings_pitched, func.sum(db.PitchStats.total_outs).label("total_outs"), func.sum(db.PitchStats.batters_faced).label("batters_faced"), func.sum(db.PitchStats.runs).label("runs"), func.sum(db.PitchStats.earned_runs).label("earned_runs"), func.sum(db.PitchStats.hits).label("hits"), func.sum(db.PitchStats.homeruns).label("homeruns"), func.sum(db.PitchStats.strikeouts).label("strikeouts"), func.sum(db.PitchStats.bases_on_balls).label("bases_on_balls"), era, whip, k_per_nine, bb_per_nine, hr_per_nine, k_per_bb, k_rate, bb_rate, k_minus_bb, hr_per_fb, func.sum(db.PitchStats.pitch_count).label("pitch_count"), func.sum(db.PitchStats.strikes).label("strikes"), func.sum(db.PitchStats.strikes_contact).label("strikes_contact"), func.sum(db.PitchStats.strikes_swinging).label("strikes_swinging"), func.sum(db.PitchStats.strikes_looking).label("strikes_looking"), func.sum(db.PitchStats.ground_balls).label("ground_balls"), func.sum(db.PitchStats.fly_balls).label("fly_balls"), func.sum(db.PitchStats.line_drives).label("line_drives"), func.sum(db.PitchStats.unknown_type).label("unknown_type"), func.sum( db.PitchStats.inherited_runners).label("inherited_runners"), func.sum(db.PitchStats.inherited_scored).label("inherited_scored"), func.sum(db.PitchStats.wpa_pitch).label("wpa_pitch"), func.sum(db.PitchStats.re24_pitch).label("re24_pitch"), ]).select_from(db.PitchStats).group_by( db.PitchStats.player_id).group_by( db.PitchStats.player_team_id_bbref).order_by( db.PitchStats.player_id_mlb), metadata=db.Base.metadata, cascade_on_drop=False, ) @classmethod def get_pitch_stats_by_team_for_player(cls, db_engine, mlb_id): s = select([cls]).where(cls.mlb_id == mlb_id) results = db_engine.execute(s).fetchall() return PitchStatsMetrics.from_query_results(results) if results else []
class BatStats_By_Opp_Team_Year_View(db.Base): __table__ = create_view( name="batstats_by_opp_team_year", selectable=select([ db.BatStats.player_id.label("id"), db.BatStats.player_id_mlb.label("mlb_id"), db.BatStats.player_id_bbref.label("bbref_id"), db.BatStats.season_id.label("season_id"), db.Season.year.label("year"), db.BatStats.opponent_team_id.label("opponent_team_id"), db.BatStats.opponent_team_id_bbref.label("opponent_team_id_bbref"), func.count(db.BatStats.id).label("total_games"), avg, obp, slg, ops, iso, bb_rate, k_rate, contact_rate, func.sum(db.BatStats.plate_appearances).label("plate_appearances"), func.sum(db.BatStats.at_bats).label("at_bats"), func.sum(db.BatStats.hits).label("hits"), func.sum(db.BatStats.runs_scored).label("runs_scored"), func.sum(db.BatStats.rbis).label("rbis"), func.sum(db.BatStats.bases_on_balls).label("bases_on_balls"), func.sum(db.BatStats.strikeouts).label("strikeouts"), func.sum(db.BatStats.doubles).label("doubles"), func.sum(db.BatStats.triples).label("triples"), func.sum(db.BatStats.homeruns).label("homeruns"), func.sum(db.BatStats.stolen_bases).label("stolen_bases"), func.sum(db.BatStats.caught_stealing).label("caught_stealing"), func.sum(db.BatStats.hit_by_pitch).label("hit_by_pitch"), func.sum(db.BatStats.intentional_bb).label("intentional_bb"), func.sum(db.BatStats.gdp).label("gdp"), func.sum(db.BatStats.sac_fly).label("sac_fly"), func.sum(db.BatStats.sac_hit).label("sac_hit"), func.sum(db.BatStats.total_pitches).label("total_pitches"), func.sum(db.BatStats.total_strikes).label("total_strikes"), func.sum(db.BatStats.wpa_bat).label("wpa_bat"), func.sum(db.BatStats.wpa_bat_pos).label("wpa_bat_pos"), func.sum(db.BatStats.wpa_bat_neg).label("wpa_bat_neg"), func.sum(db.BatStats.re24_bat).label("re24_bat"), ]).select_from( join(db.BatStats, db.Season, db.BatStats.season_id == db.Season.id)).group_by( db.BatStats.season_id).group_by( db.BatStats.player_id).group_by( db.BatStats.opponent_team_id).order_by( db.BatStats.player_id_mlb), metadata=db.Base.metadata, cascade_on_drop=False, ) @classmethod def get_bat_stats_by_opp_by_year_for_player(cls, db_engine, mlb_id): s = select([cls]).where(cls.mlb_id == mlb_id) results = db_engine.execute(s).fetchall() return BatStatsMetrics.from_query_results(results) if results else []
class Team_PitchStats_SP_By_Year_View(db.Base): __table__ = create_view( name="team_pitchstats_sp_by_year", selectable=select( [ db.PitchStats.player_team_id.label("team_id"), db.PitchStats.player_team_id_bbref.label("team_id_bbref"), db.Season.year.label("year"), func.count(db.PitchStats.game_status_id.distinct()).label("total_games"), func.sum(db.PitchStats.is_sp).label("games_as_sp"), func.sum(db.PitchStats.is_rp).label("games_as_rp"), func.sum(db.PitchStats.is_wp).label("wins"), func.sum(db.PitchStats.is_lp).label("losses"), func.sum(db.PitchStats.is_sv).label("saves"), innings_pitched, func.sum(db.PitchStats.total_outs).label("total_outs"), func.sum(db.PitchStats.batters_faced).label("batters_faced"), func.sum(db.PitchStats.runs).label("runs"), func.sum(db.PitchStats.earned_runs).label("earned_runs"), func.sum(db.PitchStats.hits).label("hits"), func.sum(db.PitchStats.homeruns).label("homeruns"), func.sum(db.PitchStats.strikeouts).label("strikeouts"), func.sum(db.PitchStats.bases_on_balls).label("bases_on_balls"), era, whip, k_per_nine, bb_per_nine, hr_per_nine, k_per_bb, k_rate, bb_rate, k_minus_bb, hr_per_fb, func.sum(db.PitchStats.pitch_count).label("pitch_count"), func.sum(db.PitchStats.strikes).label("strikes"), func.sum(db.PitchStats.strikes_contact).label("strikes_contact"), func.sum(db.PitchStats.strikes_swinging).label("strikes_swinging"), func.sum(db.PitchStats.strikes_looking).label("strikes_looking"), func.sum(db.PitchStats.ground_balls).label("ground_balls"), func.sum(db.PitchStats.fly_balls).label("fly_balls"), func.sum(db.PitchStats.line_drives).label("line_drives"), func.sum(db.PitchStats.unknown_type).label("unknown_type"), func.sum(db.PitchStats.inherited_runners).label("inherited_runners"), func.sum(db.PitchStats.inherited_scored).label("inherited_scored"), func.sum(db.PitchStats.wpa_pitch).label("wpa_pitch"), func.sum(db.PitchStats.re24_pitch).label("re24_pitch"), db.PitchStats.season_id.label("season_id"), ] ) .select_from(join(db.PitchStats, db.Season, db.PitchStats.season_id == db.Season.id)) .where(db.PitchStats.is_sp == 1) .group_by(db.PitchStats.season_id) .group_by(db.PitchStats.player_team_id) .order_by(db.PitchStats.player_team_id) .order_by(db.PitchStats.season_id), metadata=db.Base.metadata, cascade_on_drop=False, ) @classmethod def get_pitch_stats_for_sp_for_team(cls, db_engine, team_id_bbref, year): s = select([cls]).where(and_(cls.team_id_bbref == team_id_bbref, cls.year == year)) results = db_engine.execute(s).fetchall() return PitchStatsMetrics.from_query_results(results)[0] if results else None @classmethod def get_pitch_stats_for_sp_by_year_for_team(cls, db_engine, team_id_bbref): s = select([cls]).where(cls.team_id_bbref == team_id_bbref) results = db_engine.execute(s).fetchall() return PitchStatsMetrics.from_query_results(results) if results else [] @classmethod def get_pitch_stats_for_sp_for_season_for_all_teams(cls, db_engine, year): s = select([cls]).where(cls.year == year) results = db_engine.execute(s).fetchall() return PitchStatsMetrics.from_query_results(results) if results else []
class Team_BatStats_By_BatOrder_By_Year(db.Base): __table__ = create_view( name="team_batstats_by_batorder_by_year", selectable=select([ db.BatStats.player_team_id.label("team_id"), db.BatStats.player_team_id_bbref.label("team_id_bbref"), db.Season.year.label("year"), db.BatStats.bat_order.label("bat_order"), func.count( db.BatStats.game_status_id.distinct()).label("total_games"), avg, obp, slg, ops, iso, bb_rate, k_rate, contact_rate, func.sum(db.BatStats.plate_appearances).label("plate_appearances"), func.sum(db.BatStats.at_bats).label("at_bats"), func.sum(db.BatStats.hits).label("hits"), func.sum(db.BatStats.runs_scored).label("runs_scored"), func.sum(db.BatStats.rbis).label("rbis"), func.sum(db.BatStats.bases_on_balls).label("bases_on_balls"), func.sum(db.BatStats.strikeouts).label("strikeouts"), func.sum(db.BatStats.doubles).label("doubles"), func.sum(db.BatStats.triples).label("triples"), func.sum(db.BatStats.homeruns).label("homeruns"), func.sum(db.BatStats.stolen_bases).label("stolen_bases"), func.sum(db.BatStats.caught_stealing).label("caught_stealing"), func.sum(db.BatStats.hit_by_pitch).label("hit_by_pitch"), func.sum(db.BatStats.intentional_bb).label("intentional_bb"), func.sum(db.BatStats.gdp).label("gdp"), func.sum(db.BatStats.sac_fly).label("sac_fly"), func.sum(db.BatStats.sac_hit).label("sac_hit"), func.sum(db.BatStats.total_pitches).label("total_pitches"), func.sum(db.BatStats.total_strikes).label("total_strikes"), func.sum(db.BatStats.wpa_bat).label("wpa_bat"), func.sum(db.BatStats.wpa_bat_pos).label("wpa_bat_pos"), func.sum(db.BatStats.wpa_bat_neg).label("wpa_bat_neg"), func.sum(db.BatStats.re24_bat).label("re24_bat"), db.BatStats.season_id.label("season_id"), ]).select_from( join(db.BatStats, db.Season, db.BatStats.season_id == db.Season.id)).group_by( db.BatStats.season_id).group_by( db.BatStats.player_team_id).group_by( db.BatStats.bat_order).order_by( db.BatStats.bat_order), metadata=db.Base.metadata, cascade_on_drop=False, ) @classmethod def get_bat_stats_by_lineup_spot_for_team(cls, db_engine, team_id_bbref, year): s = select([cls]).where( and_(cls.team_id_bbref == team_id_bbref, cls.year == year)) results = db_engine.execute(s).fetchall() return BatStatsMetrics.from_query_results(results) if results else [] @classmethod def get_bat_stats_for_lineup_spot_by_year_for_team(cls, db_engine, bat_order_list, team_id_bbref): s = select([cls]).where( and_(cls.bat_order.in_(bat_order_list), cls.team_id_bbref == team_id_bbref)) results = db_engine.execute(s).fetchall() return BatStatsMetrics.from_query_results(results) if results else [] @classmethod def get_bat_stats_for_lineup_spot_for_season_for_all_teams( cls, db_engine, bat_order_list, year): s = select([cls]).where( and_(cls.bat_order.in_(bat_order_list), cls.year == year)) results = db_engine.execute(s).fetchall() return BatStatsMetrics.from_query_results(results) if results else []
def add_view(): from .queries import joined_mat_query from .tables import metadata, tables tables["joined"] = create_view("joined", joined_mat_query(), metadata)