Neutralized Batting Stats — Larry Christenson

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 INTO _calcs_neutral_bat select "2021-01-25 11:24:33.803" as id,
      player_ID,
       year_ID,
       mbn.G * ifnull(params.games_multiplier, 0)                       as G,
       round(ifnull(mbn.RC,0)
             * (params.to_park_factor / params.from_park_factor)
             * (params.to_dh_factor / params.from_dh_factor)
             * (params.to_runs_per_game / params.from_runs_per_game))   as RC,
       0.000                                                            as H,
       0.000                                                            as AB,
       0.000                                                            as 2B,
       0.000                                                            as 3B,
       0.000                                                            as HR,
       0.000                                                            as BB,
       0.000                                                            as HBP,
       0.000                                                            as SF,
       0.000                                                            as onbase_perc,
       0.000                                                            as slugging_perc
  from majors_batting_neutral mbn

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 mbn.player_ID, year_ID

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

update _calcs_neutral_bat calcs
  join majors_batting_neutral mbn 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

   # This equation provides the solution to the quadratic equation that gives the player's adjusted
   # H, which are then adjusted to a 162-game schedule
   set calcs.H = ( (calcs.RC * (1.0 + (mbn.BB/mbn.H)))
                   + sqrt( power(calcs.RC * (1.0 + (mbn.BB/mbn.H)), 2)
                           + ( 4.0 * ((mbn.TB/mbn.H) * (1.0 + (mbn.BB/mbn.H))) * (calcs.RC * (mbn.AB - mbn.H)) ) )
                 ) / ( 2.0 * (mbn.TB/mbn.H) * (1.0 + (mbn.BB/mbn.H)) )
                 * params.games_multiplier
 WHERE calcs.id="2021-01-25 11:24:33.803"       


update _calcs_neutral_bat calcs
  join majors_batting_neutral mbn 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

       # The player's outs remain constant, so get the new AB total by adding outs to adjusted H.
   set calcs.AB = if(calcs.RC>0 and mbn.H>0 and mbn.TB>0 and mbn.AB>0,
                     (mbn.AB - mbn.H) * params.games_multiplier + calcs.H,
                     mbn.AB * params.games_multiplier),
       # The increases in 2B, 3B, HR, BB, SB, and HBP are proportional to the player's increase in hits.
       calcs.2B = if(calcs.RC>0 and mbn.H>0 and mbn.TB>0 and mbn.AB>0, (mbn.2B / mbn.H) * calcs.H, 0),
       calcs.3B = if(calcs.RC>0 and mbn.H>0 and mbn.TB>0 and mbn.AB>0, (mbn.3B / mbn.H) * calcs.H, 0),
       calcs.HR = if(calcs.RC>0 and mbn.H>0 and mbn.TB>0 and mbn.AB>0, (mbn.HR / mbn.H) * calcs.H, 0),
       calcs.BB = if(calcs.RC>0 and mbn.H>0 and mbn.TB>0 and mbn.AB>0, (mbn.BB / mbn.H) * calcs.H, mbn.BB * params.games_multiplier),
       calcs.HBP = if(calcs.RC>0 and mbn.H>0 and mbn.TB>0 and mbn.AB>0, (mbn.HBP / mbn.H) * calcs.H, mbn.HBP * params.games_multiplier),
       calcs.SF = mbn.SF * params.games_multiplier
 WHERE calcs.id="2021-01-25 11:24:33.803"       


update _calcs_neutral_bat 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 onbase_perc = if(AB + BB + ifnull(HBP,0) + ifnull(SF,0) > 0, (H+BB+ifnull(HBP,0)) / (AB+BB+ifnull(HBP,0)+ifnull(SF,0)), 0),
       slugging_perc = if(calcs.AB>0, (calcs.H + calcs.2B + 2*calcs.3B + 3*calcs.HR) / calcs.AB, 0)
 WHERE calcs.id="2021-01-25 11:24:33.803"       


   select     player_ID,
    year_ID,
    age,
    ActG                                                                                                                                   as G_actual,
    round(ifnull(mbn.G,0) * params.games_multiplier)                                                                                       as G,
    round(calcs.RC * params.games_multiplier)                                                                                              as RC,
    round(calcs.H)                                                                                                                         as H,
    round(calcs.AB)                                                                                                                        as AB,
    round(calcs.AB + calcs.BB + ifnull(calcs.HBP,0) + ifnull(calcs.SF, 0))                                                                 as PA,
    round(calcs.2B)                                                                                                                        as 2B,
    round(calcs.3B)                                                                                                                        as 3B,
    round(calcs.HR)                                                                                                                        as HR,
    round(calcs.BB)                                                                                                                        as BB,
    round(if(calcs.RC>0 and mbn.H>0 and mbn.TB>0 and mbn.AB>0, (mbn.SB / mbn.H) * calcs.H, mbn.SB * params.games_multiplier))              as SB,
    round(if(calcs.RC>0 and mbn.H>0 and mbn.TB>0 and mbn.AB>0, (mbn.CS / mbn.H) * calcs.H, mbn.CS * params.games_multiplier))              as CS,
    round(calcs.HBP)                                                                                                                       as HBP,

    # The increases in R and RBI are proportional to the increase in the players RC.
    if(mbn.RC>0, round(mbn.R * (calcs.RC/mbn.RC) * params.games_multiplier), round(mbn.R * params.games_multiplier))                       as R,
    if(mbn.RC>0,
       round(ifnull(mbn.RBI, 0) * (ifnull(calcs.RC,0)/mbn.RC) * params.games_multiplier),
       round(ifnull(mbn.RBI,0) * params.games_multiplier))                                                                                 as RBI,

    # Assume that since the number of outs made does not change, then the number of SO and SF will not change.
    round(mbn.SO * params.games_multiplier)                                                                                                as SO,
    round(calcs.SF)                                                                                                                        as SF,
    round(mbn.SH * params.games_multiplier)                                                                                                as SH,

    if(calcs.AB>0, round(calcs.H / calcs.AB, 3), round(0, 3))                                                                              as batting_avg,
    round(calcs.onbase_perc, 3)                                                                                                            as onbase_perc,
    round(calcs.slugging_perc, 3)                                                                                                          as slugging_perc,
    round(ifnull(calcs.onbase_perc, 0) + ifnull(calcs.slugging_perc, 0), 3)                                                                as onbase_plus_slugging


     from majors_batting_neutral mbn
     join _calcs_neutral_bat 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-01-25 11:24:33.803"
          
          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, chrisla01