Neutralized Pitching Stats — George Perez

To League: AL, To Year: 2020
$VAR1 = {
          'lgRPG' => '4.59',
          'lgG' => '60',
          'lgERR' => '0.929',
          'lgTBpH' => '1.273',
          'lg_ID' => 'AL'
        };


To Team: neutral, To Year: 2020
$VAR1 = {
          'name' => 'neutral',
          'PPF' => 100,
          'BPF' => 100,
          'team_ID' => 'neutral'
        };


Query:
insert ignore _calcs_neutral_pitch select "2021-02-27 18:58:43.945" as id,
       player_ID,
       year_ID,
       mpn.G * ifnull(params.games_multiplier, 0) as G,
       round(ifnull(mpn.RC,0) * (params.to_park_factor / params.from_park_factor) * (params.to_runs_per_game / params.from_runs_per_game)) as RC,
       (params.to_park_factor / 100 ) * params.to_runs_per_game as lg_runs_per_game,
       params.to_tb_per_hit           as k_coeff,
       mpn.H / ( mpn.H + mpn.BB + ifnull(mpn.HBP,0) ) as t_coeff,
       mpn.HR / mpn.H                 as h_coeff,
       0.000                          as a_coeff,
       0.000                          as b_coeff,
       0.000                          as c_coeff,
       0.000                          as TB,
       0.000                          as IPouts,
       0.000                          as R,
       0.000                          as TOB,
       0.000                          as ER,
       0.000                          as pitcher_allows_runs_per_game,
       0.000                          as adj_pitcher_allows_runs_per_game,
       0.000                          as H,
       0.000                          as exponent,
       0.000                          as HBP,
       0.000                          as BB,
       0.000                          as pyth,
       0.000                          as decision
  from majors_pitching_neutral mpn

cross join (select ?   as to_park_factor,
                   ?   as to_dh_factor,
                   ?   as to_runs_per_game,
                   ?   as to_earned_run_rate,
                   ?   as to_tb_per_hit,
                   ?   as from_park_factor,
                   ?   as from_dh_factor,
                   ?   as from_runs_per_game,
                   ?/? as games_multiplier) as params

 where player_ID=?
and year_ID between ? and ?
group by player_ID, year_ID

100, 0.9375, 4.59, 0.929, 1.273, 100, 1, 4.25, 60, 162, perezge01, 1871, 2020

update _calcs_neutral_pitch calcs
  join majors_pitching_neutral mpn using (player_ID, year_ID)
 cross join (select ?   as to_park_factor,
                   ?   as to_dh_factor,
                   ?   as to_runs_per_game,
                   ?   as to_earned_run_rate,
                   ?   as to_tb_per_hit,
                   ?   as from_park_factor,
                   ?   as from_dh_factor,
                   ?   as from_runs_per_game,
                   ?/? as games_multiplier) as params

   set a_coeff = (k_coeff * t_coeff) - (k_coeff * h_coeff * t_coeff) + (4 * h_coeff * t_coeff),
       b_coeff = -calcs.RC,
       c_coeff = -(calcs.RC * 3 * ifnull(mpn.IP,0)),
       TB      = (k_coeff * (mpn.H - mpn.HR)) + (4 * mpn.HR),
       IPouts  = 3 * mpn.IP * params.games_multiplier,
       calcs.R = if(mpn.RC>0, mpn.R * (calcs.RC / mpn.RC) * params.games_multiplier, 0)
  WHERE calcs.id="2021-02-27 18:58:43.945"


update _calcs_neutral_pitch calcs
cross join (select ?   as to_park_factor,
                   ?   as to_dh_factor,
                   ?   as to_runs_per_game,
                   ?   as to_earned_run_rate,
                   ?   as to_tb_per_hit,
                   ?   as from_park_factor,
                   ?   as from_dh_factor,
                   ?   as from_runs_per_game,
                   ?/? as games_multiplier) as params

   set TOB                              = (-b_coeff + sqrt(b_coeff * b_coeff - 4 * a_coeff * c_coeff)) / (2 * a_coeff) * params.games_multiplier,
       ER                               = R * params.to_earned_run_rate,
       pitcher_allows_runs_per_game     = 27 * calcs.R / calcs.IPouts,
       adj_pitcher_allows_runs_per_game = (27 - calcs.IPouts / calcs.G) * calcs.lg_runs_per_game / 27 + calcs.R / calcs.G
  WHERE calcs.id="2021-02-27 18:58:43.945"


update _calcs_neutral_pitch calcs
   set adj_pitcher_allows_runs_per_game = if(adj_pitcher_allows_runs_per_game < 0, 0, adj_pitcher_allows_runs_per_game)
  WHERE calcs.id="2021-02-27 18:58:43.945"


update _calcs_neutral_pitch calcs
  join majors_pitching_neutral mpn using (player_ID, year_ID)
cross join (select ?   as to_park_factor,
                   ?   as to_dh_factor,
                   ?   as to_runs_per_game,
                   ?   as to_earned_run_rate,
                   ?   as to_tb_per_hit,
                   ?   as from_park_factor,
                   ?   as from_dh_factor,
                   ?   as from_runs_per_game,
                   ?/? as games_multiplier) as params

   set calcs.H  = if(calcs.RC>0 and mpn.H>0 and mpn.BB>0, t_coeff * TOB, 0),
       exponent = power(calcs.lg_runs_per_game + calcs.adj_pitcher_allows_runs_per_game, .287)
  WHERE calcs.id="2021-02-27 18:58:43.945"


   select     player_ID,
    year_ID,
    age,
    ActG                                                                                                                                   as G_actual,
    round(ifnull(mpn.G,0) * params.games_multiplier)                                                                                       as G,
    round(calcs.RC * params.games_multiplier)                                                                                              as RC,
    calcs.TOB,
    round(calcs.H)                                                                                                                         as H,
    if(calcs.RC>0 and mpn.H>0 and mpn.BB>0, round((mpn.HR/mpn.H) * calcs.H), 0)                                                            as HR,
    round(calcs.BB)                                                                                                                        as BB,
    if(calcs.RC>0 and mpn.H>0 and mpn.BB>0, (calcs.TB/mpn.H) * calcs.H, 0)                                                                 as TB,
    round(calcs.HBP)                                                                                                                       as HBP,
    round(calcs.R)                                                                                                                         as R,
    round(calcs.ER)                                                                                                                        as ER,
    if(calcs.IPouts>0, round(mpn.SO * calcs.IPouts / (mpn.IP * 3)), round(mpn.SO * params.games_multiplier))                               as SO,
    if(calcs.IPouts>0, round(calcs.IPouts / 3), 0)                                                                                         as IP,
    if(calcs.IPouts>0, round(27 * (calcs.R * params.to_earned_run_rate) / calcs.IPouts, 2), null)                                          as earned_run_avg,
    if(calcs.IPouts>0, round(3 * (calcs.BB + calcs.H) / calcs.IPouts, 3), null)                                                            as whip,
    if(calcs.IPouts>0, round(calcs.decision * calcs.pyth), null)                                                                           as W,
    if(calcs.IPouts>0, round(calcs.decision - (calcs.decision * calcs.pyth)), null)                                                        as L,
    if(calcs.IPouts>0 and calcs.decision>0,
       round(calcs.pyth,3),
       null)                                                                                                                               as win_loss_perc,
    ROUND(calcs.IPouts + calcs.H + calcs.BB + ifnull(calcs.HBP,0))                                                                         as BFP

     from majors_pitching_neutral mpn
     join _calcs_neutral_pitch calcs using (year_ID, player_ID)
cross join (select ?   as to_park_factor,
                   ?   as to_dh_factor,
                   ?   as to_runs_per_game,
                   ?   as to_earned_run_rate,
                   ?   as to_tb_per_hit,
                   ?   as from_park_factor,
                   ?   as from_dh_factor,
                   ?   as from_runs_per_game,
                   ?/? as games_multiplier) as params

    where calcs.id="2021-02-27 18:58:43.945"
          
          and  calcs.player_ID=?


order by year_ID asc


Parameters: 100, 0.9375, 4.59, 0.929, 1.273, 100, 1, 4.25, 60, 162, perezge01