Player Batting Span Stats Finder - Baseball

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

main

Page Setup Time: 0.00213 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(b_hr) OVER w AS b_hr
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 b_ab + IFNULL(b_sf,0) > 0
  AND year_id<=?
  AND year_id>=?
  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 b_hr 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, IF(SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), SUM(b_h + IFNULL(b_bb,0) + IFNULL(b_hbp,0)) / SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), NULL) + IF(SUM(b_ab), SUM(b_h + b_doubles + (2 * b_triples) + (3 * b_hr)) / SUM(b_ab), NULL) as b_onbase_plus_slugging, IF(SUM(b_ab), SUM(b_h + b_doubles + (2 * b_triples) + (3 * b_hr)) / SUM(b_ab), NULL) as b_slugging_perc, IF(SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), SUM(b_h + IFNULL(b_bb,0) + IFNULL(b_hbp,0)) / SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), NULL) as b_onbase_perc, IF(SUM(b_ab), SUM(b_h) / SUM(b_ab), NULL) as b_batting_avg, SUM(b_pa IS NULL) as b_pa_incomplete, SUM(b_pa) AS b_pa, SUM(b_ab IS NULL) as b_ab_incomplete, SUM(b_ab) AS b_ab, SUM(b_r IS NULL) as b_r_incomplete, SUM(b_r) AS b_r, SUM(b_h IS NULL) as b_h_incomplete, SUM(b_h) AS b_h, SUM(b_hr IS NULL)+SUM(b_doubles IS NULL)+SUM(b_triples IS NULL)+SUM(b_h IS NULL) as b_singles_incomplete, SUM(b_h - (b_doubles + b_triples + b_hr)) AS b_singles, SUM(b_doubles IS NULL) as b_doubles_incomplete, SUM(b_doubles) AS b_doubles, SUM(b_triples IS NULL) as b_triples_incomplete, SUM(b_triples) AS b_triples, SUM(b_hr IS NULL) as b_hr_incomplete, SUM(b_hr) AS b_hr, SUM(b_rbi IS NULL) as b_rbi_incomplete, SUM(b_rbi) AS b_rbi, SUM(b_sb IS NULL) as b_sb_incomplete, SUM(b_sb) AS b_sb, SUM(b_cs IS NULL) as b_cs_incomplete, SUM(b_cs) AS b_cs, SUM(b_bb IS NULL) as b_bb_incomplete, SUM(b_bb) AS b_bb, SUM(b_so IS NULL) as b_so_incomplete, SUM(b_so) AS b_so, IF(SUM(b_ab), SUM(b_h) / SUM(b_ab), NULL) as b_batting_avg_csk, SUM(b_ab IS NULL)+SUM(b_h IS NULL) as b_batting_avg_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(b_ab), SUM(b_h) / SUM(b_ab), NULL),3)) AS b_batting_avg, IF(SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), SUM(b_h + IFNULL(b_bb,0) + IFNULL(b_hbp,0)) / SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), NULL) as b_onbase_perc_csk, 0 as b_onbase_perc_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), SUM(b_h + IFNULL(b_bb,0) + IFNULL(b_hbp,0)) / SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), NULL),3)) AS b_onbase_perc, IF(SUM(b_ab), SUM(b_h + b_doubles + (2 * b_triples) + (3 * b_hr)) / SUM(b_ab), NULL) as b_slugging_perc_csk, SUM(b_h IS NULL)+SUM(b_ab IS NULL)+SUM(b_triples IS NULL)+SUM(b_doubles IS NULL)+SUM(b_hr IS NULL) as b_slugging_perc_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(b_ab), SUM(b_h + b_doubles + (2 * b_triples) + (3 * b_hr)) / SUM(b_ab), NULL),3)) AS b_slugging_perc, IF(SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), SUM(b_h + IFNULL(b_bb,0) + IFNULL(b_hbp,0)) / SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), NULL) + IF(SUM(b_ab), SUM(b_h + b_doubles + (2 * b_triples) + (3 * b_hr)) / SUM(b_ab), NULL) as b_onbase_plus_slugging_csk, SUM(b_hbp IS NULL)+SUM(b_h IS NULL)+SUM(b_doubles IS NULL)+SUM(b_triples IS NULL)+SUM(b_bb IS NULL)+SUM(b_ab IS NULL)+SUM(b_sf IS NULL)+SUM(b_hr IS NULL) as b_onbase_plus_slugging_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), SUM(b_h + IFNULL(b_bb,0) + IFNULL(b_hbp,0)) / SUM(b_ab + IFNULL(b_bb,0) + IFNULL(b_hbp,0) + IFNULL(b_sf,0)), NULL) + IF(SUM(b_ab), SUM(b_h + b_doubles + (2 * b_triples) + (3 * b_hr)) / SUM(b_ab), NULL),3)) AS b_onbase_plus_slugging, SUM(b_doubles IS NULL)+SUM(b_triples IS NULL)+SUM(b_h IS NULL)+SUM(b_hr IS NULL) as b_tb_incomplete, SUM(b_h + b_doubles + 2 * b_triples + 3 * b_hr) AS b_tb, SUM(b_gidp IS NULL) as b_gidp_incomplete, SUM(b_gidp) AS b_gidp, SUM(b_hbp IS NULL) as b_hbp_incomplete, SUM(b_hbp) AS b_hbp, SUM(b_sh IS NULL) as b_sh_incomplete, SUM(b_sh) AS b_sh, SUM(b_sf IS NULL) as b_sf_incomplete, SUM(b_sf) AS b_sf, SUM(b_ibb IS NULL) as b_ibb_incomplete, SUM(b_ibb) AS b_ibb
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 b_ab + IFNULL(b_sf,0) > 0
  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 b_hr DESC, span_start_date_csk DESC, person_id 

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

Query Time: 1.21 seconds
Table Build Time: 0.05 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'comp_type' => 'reg',
          'max_temperature' => 120,
          'timeframe' => 'seasons',
          'playerapp' => 'oneab',
          'year_max' => 2025,
          'GF' => 'anyGF',
          'span_length' => 5,
          'max_wind_speed' => 90,
          'year_min' => 2025,
          'order_by' => 'b_hr',
          'exactness' => 'anymarked',
          'match' => 'player_span_game'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'year_min' => 2025,
          'year_max' => 2025
        };