Player Pitching Game Stats Finder - Baseball

--------------------------------------

main

Page Setup Time: 0.00129 seconds

SELECT STRAIGHT_JOIN name_display, name_display_csk, sup_players.link as name_display_link, person_id, age_on_day, ts.name_abbr as team_name_abbr, ts.link as team_link, opp_team_link, CONCAT(`date`,IF(date_game_number,CONCAT(" (",date_game_number,")"),"")) as date, game_id, sup_games.link as game_link, year_id as year_id_csk, year_id as year_id, IF(home_away_neutral = 'a','@','') as game_location, CASE
  WHEN st.team_id = sup_games.home_team_id THEN IFNULL(home_score,0) - IFNULL(away_score,0) + IFNULL(home_score,0)/100
  WHEN st.team_id = sup_games.away_team_id THEN IFNULL(away_score,0) - IFNULL(home_score,0) + IFNULL(away_score,0)/100
  WHEN home_score = away_score THEN IFNULL(home_score,0) - IFNULL(away_score,0) + IFNULL(home_score,0)/100
  ELSE 0 END
  as game_result_csk, CONCAT(
  CASE
  WHEN st.team_id = sup_games.home_team_id AND home_score > away_score THEN CONCAT('W, ',home_score,'-',away_score)
  WHEN st.team_id = sup_games.away_team_id AND home_score < away_score THEN CONCAT('W, ',away_score,'-',home_score)
  WHEN st.team_id = sup_games.home_team_id AND home_score < away_score THEN CONCAT('L, ',home_score,'-',away_score)
  WHEN st.team_id = sup_games.away_team_id AND home_score > away_score THEN CONCAT('L, ',away_score,'-',home_score)
  WHEN home_score = away_score THEN CONCAT('T, ',home_score,'-',away_score)
  ELSE '' END,
  IF( scheduled_innings != 9 OR outs_played < 6*IFNULL(scheduled_innings,9)-3 OR outs_played > 6*IFNULL(scheduled_innings,9),
  CONCAT(" (", CEIL(outs_played / 6), ")"),
  ""
  )
  )
  as game_result, os.name_abbr as opp_name_abbr, 	SUBSTRING(CONCAT(
  IF(is_pinch_hitter,      ' PH',''),
  IF(is_pinch_runner,      ' PR',''),
  IF(is_shortstop,         ' SS',''),
  IF(is_catcher,           ' C', ''),
  IF(is_second_base,       ' 2B',''),
  IF(is_center_field,      ' CF',''),
  IF(is_third_base,        ' 3B',''),
  IF(is_right_field,       ' RF',''),
  IF(is_left_field,        ' LF',''),
  IF(is_first_base,        ' 1B',''),
  IF(is_pitcher,           ' P', ''),
  IF(is_designated_hitter, ' DH','')
  ),1)
  as pos_game,      IF(p_w=1 AND p_blown_saves=1,  3,
  IF(p_l=1 AND p_blown_saves=1, -3,
  IF(p_w=1,                5,
  IF(p_l=1,               -2,
  IF(p_s=1,                4,
  IF(p_holds=1,             1,
  IF(p_blown_saves=1,             -1, 0
  ))))))
  )
  as p_game_result_csk, CONCAT(
  IF(p_sho=1,
  CONCAT('SHO',IF(p_inning_first is null OR floor(1 + (p_inning_last - (home_away_neutral='a'))/6)=9,
  '',
  CONCAT('(',floor(1 + (p_inning_last - (home_away_neutral='a'))/6),')'))),
  IF(p_cg=1,
  CONCAT('CG',IF(p_inning_first is null OR floor(1 + (p_inning_last - (home_away_neutral='a'))/6)=9,
  '',
  CONCAT('(',floor(1 + (p_inning_last - (home_away_neutral='a'))/6),')'))),
  CONCAT(IF(p_games_started=1,
  CONCAT('GS-',IF(p_inning_first is null,'   ',floor(1 + (p_inning_last - (home_away_neutral='a'))/6))),
  IF(p_inning_first is null,'',
  CONCAT(floor(1 + p_inning_first/6),'-',
  IF(p_gf=1,CONCAT('GF',
  IF(p_inning_first is null OR floor(1 + (p_inning_last - (home_away_neutral='a'))/6)=9,
  '',
  CONCAT('(',floor(1 + (p_inning_last - (home_away_neutral='a'))/6),')'))),
  floor(1 + (p_inning_last - (home_away_neutral='a'))/6)))))))),
  IF(p_w=1 AND p_blown_saves=1,', BW',
  IF(p_l=1 AND p_blown_saves=1,', BL',
  IF(p_w=1 AND p_holds=1,', HW',
  IF(p_l=1 AND p_holds=1,', HL',
  IF(p_w=1,', W',
  IF(p_l=1,', L',
  IF(p_s=1,', S',
  IF(p_holds=1,', H', IF(p_blown_saves=1,',BS',''))))))))
  )
  )
  as p_game_result, p_ip_outs as p_ip_csk, p_ip_outs DIV 3 + 0.1 * MOD(p_ip_outs,3) AS p_ip, p_h, p_r, p_er, p_r - p_er AS p_uer, p_hr, p_bb, p_ibb, p_so, p_hbp, p_bk, p_wp, p_bfp, p_h + IFNULL(p_bb,0) + IFNULL(p_hbp,0) + IFNULL(p_roe,0) + IFNULL(p_reached_on_strikeout,0) AS p_baserunners
FROM sup_player_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_id, part_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral, win, loss, tie FROM sup_team_games) as tg USING (game_id, team_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
  JOIN (SELECT team_id, year_id, name_abbr, name, link FROM sup_team_seasons GROUP BY team_id, year_id ) as ts USING (team_id, year_id)
  LEFT JOIN (SELECT team_id as opp_team_id, year_id, name_abbr, link as opp_team_link FROM sup_team_seasons GROUP BY team_id, year_id ) as os USING (opp_team_id, year_id)
WHERE year_id<=?
  AND sup_phase_seasons.type=?
  AND year_id>=?
  AND is_pitcher=? 
ORDER BY p_so DESC, person_id LIMIT 0, 20

SQL PARAMS -- $VAR1 = [
          2025,
          'reg',
          2025,
          '1'
        ];

Query Time: 0.07 seconds
Table Build Time: 0.05 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'days_rest_comp' => 'eq',
          'team_game_max' => 165,
          'max_wind_speed' => 90,
          'year_max' => 2025,
          'order_by' => 'p_so',
          'player_game_min' => 1,
          'comp_type' => 'reg',
          'match' => 'player_game',
          'timeframe' => 'seasons',
          'team_game_min' => 1,
          'year_min' => 2025,
          'player_game_max' => 9999,
          'max_temperature' => 120,
          'is_pitcher' => '1'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'year_min' => 2025,
          'year_max' => 2025,
          'is_pitcher' => '1'
        };