Player Pitching Span Stats Finder - Baseball

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

main

Page Setup Time: 0.00246 seconds

WITH
  Gamelog AS (
SELECT person_id, year_id, team_id, FIRST_VALUE(IF(date_game_number,CONCAT(date," (",date_game_number,")"),date)) OVER w as span_start_date_window, LAST_VALUE(IF(date_game_number,CONCAT(date," (",date_game_number,")"),date)) OVER w as span_end_date_window, ROW_NUMBER() OVER w AS rn, COUNT(*) OVER w AS played_in_span, phase_id, SUM(p_so) OVER w AS p_so
FROM sup_player_games
  JOIN sup_games USING (game_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_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)
WHERE sup_phase_seasons.type=?
  AND year_id>=?
  AND year_id<=?
  AND is_pitcher=?
  AND date < current_date() 
WINDOW w AS (PARTITION BY person_id ORDER BY date, date_game_number ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) 
  ),
  span_list AS (
SELECT person_id, played_in_span, span_start_date_window as span_start_date, span_end_date_window as span_end_date
FROM Gamelog
WHERE played_in_span=? 
GROUP BY person_id, span_start_date
ORDER BY p_so DESC, span_start_date DESC LIMIT 0, 20
  )
SELECT name_display, name_display_csk, sup_players.link as name_display_link, person_id, MIN(age_on_day) as age_span_start, span_start_date as span_start_date_csk, span_start_date as span_start_date, span_end_date as span_end_date_csk, span_end_date as span_end_date, GROUP_CONCAT(DISTINCT UPPER(ts.name_abbr)) as teams_played_for_career, 'Games List' as details_games, YEAR(span_start_date) as year_min, SUM(p_w IS NULL) as p_w_incomplete, SUM(p_w) AS p_w, SUM(p_l IS NULL) as p_l_incomplete, SUM(p_l) AS p_l, SUM(p_s IS NULL) as p_s_incomplete, SUM(p_s) AS p_s, SUM(p_blown_saves IS NULL) as p_blown_saves_incomplete, SUM(p_blown_saves) AS p_blown_saves, SUM(p_cg IS NULL) as p_cg_incomplete, SUM(p_cg) AS p_cg, SUM(p_sho IS NULL) as p_sho_incomplete, SUM(p_sho) AS p_sho, SUM(p_gf IS NULL) as p_gf_incomplete, SUM(p_gf) AS p_gf, SUM(p_ip_outs) as p_ip_csk, SUM(p_ip_outs IS NULL) as p_ip_incomplete, SUM(p_ip_outs) DIV 3 + 0.1 * MOD(SUM(p_ip_outs),3) AS p_ip, SUM(p_w IS NULL)+SUM(p_l IS NULL) as p_decisions_incomplete, SUM(p_w + p_l) AS p_decisions, IF(SUM(p_w + p_l), SUM(p_w) / SUM(p_w + p_l), NULL) as p_win_loss_perc_csk, SUM(p_w IS NULL)+SUM(p_l IS NULL) as p_win_loss_perc_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(p_w + p_l), SUM(p_w) / SUM(p_w + p_l), NULL),3)) AS p_win_loss_perc, IF(SUM(p_ip_outs), 27 * SUM(p_er) /SUM(p_ip_outs), IF(SUM(p_er), 1e20, NULL)) as p_earned_run_avg_csk, SUM(p_er IS NULL)+SUM(p_ip_outs IS NULL) as p_earned_run_avg_incomplete, ROUND(IF(SUM(p_ip_outs), 27 * SUM(p_er)/SUM(p_ip_outs), NULL),2) AS p_earned_run_avg, SUM(p_h IS NULL) as p_h_incomplete, SUM(p_h) AS p_h, SUM(p_r IS NULL) as p_r_incomplete, SUM(p_r) AS p_r, SUM(p_er IS NULL) as p_er_incomplete, SUM(p_er) AS p_er, SUM(p_er IS NULL)+SUM(p_r IS NULL) as p_uer_incomplete, SUM(IF(p_er IS NULL, 0, p_r - p_er)) AS p_uer, SUM(p_hr IS NULL) as p_hr_incomplete, SUM(p_hr) AS p_hr, SUM(p_bb IS NULL) as p_bb_incomplete, SUM(p_bb) AS p_bb, SUM(p_ibb IS NULL) as p_ibb_incomplete, SUM(p_ibb) AS p_ibb, SUM(p_so IS NULL) as p_so_incomplete, SUM(p_so) AS p_so, SUM(p_hbp IS NULL) as p_hbp_incomplete, SUM(p_hbp) AS p_hbp, SUM(p_bk IS NULL) as p_bk_incomplete, SUM(p_bk) AS p_bk, SUM(p_wp IS NULL) as p_wp_incomplete, SUM(p_wp) AS p_wp, SUM(p_bfp IS NULL) as p_bfp_incomplete, SUM(p_bfp) AS p_bfp, SUM(p_roe IS NULL)+SUM(p_h IS NULL)+SUM(p_hbp IS NULL)+SUM(p_bb IS NULL)+SUM(p_reached_on_strikeout IS NULL) as p_baserunners_incomplete, SUM(p_h + IFNULL(p_bb,0) + IFNULL(p_hbp,0) + IFNULL(p_roe,0) + IFNULL(p_reached_on_strikeout,0)) AS p_baserunners, SUM(p_games_started IS NULL) as p_games_started_incomplete, SUM(p_games_started) AS p_games_started
FROM span_list
  JOIN sup_player_games as st USING (person_id)
  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, 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 (SELECT person_id, year_id, age as age_in_season, comp_id, phase_id, team_id FROM sup_player_team_seasons) as ps2 USING (person_id, year_id, comp_id, phase_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, comp_id, phase_id FROM sup_team_seasons) as ts USING (team_id, year_id, comp_id, phase_id)
WHERE sup_phase_seasons.type=?
  AND is_pitcher=?
  AND CONCAT(`date`,IF(date_game_number,CONCAT(" (",date_game_number,")"),""))>=span_start_date
  AND CONCAT(`date`,IF(date_game_number,CONCAT(" (",date_game_number,")"),""))<=span_end_date 
GROUP BY person_id, span_start_date, span_end_date
ORDER BY p_so DESC, span_start_date_csk DESC, person_id 

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

Query Time: 0.72 seconds
Table Build Time: 0.05 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'comp_type' => 'reg',
          'year_min' => 2025,
          'max_temperature' => 120,
          'days_rest_comp' => 'eq',
          'year_max' => 2025,
          'span_length' => 5,
          'timeframe' => 'seasons',
          'order_by' => 'p_so',
          'max_wind_speed' => 90,
          'is_pitcher' => 1,
          'match' => 'player_span_game'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'year_min' => 2025,
          'year_max' => 2025,
          'is_pitcher' => 1
        };