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)
