Data runs from 1871 to 2020 for regular season data, 1933-Present for the All-Star Game, and 1903-Present for the Postseason.

Display Query »

To League: NL, To Year: 2000

$VAR1 = { 'lgRPG' => '5.00', 'lgG' => '162', 'lgERR' => '0.916', 'lgTBpH' => '1.277', 'lg_ID' => 'NL' };



To Team: COL, To Year: 2000

$VAR1 = { 'name' => 'Colorado Rockies', 'PPF' => '125', 'BPF' => '125', 'team_ID' => 'COL' };



Query:

INSERT IGNORE INTO _calcs_neutral_bat select "2020-09-19 14:15:09.106" 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



125, 1, 5.00, 0.916, 1.277, 100, 1, 4.25, 162, 162, troutmi01, 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="2020-09-19 14:15:09.106"





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="2020-09-19 14:15:09.106"





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="2020-09-19 14:15:09.106"





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="2020-09-19 14:15:09.106"



and calcs.player_ID=?





order by HR asc





Parameters: 125, 1, 5.00, 0.916, 1.277, 100, 1, 4.25, 162, 162, troutmi01



