Блог

У меня есть запрос, который выбирает 3 лучших баскетболиста по их средним очкам, набранным во время их игр. Баскетболист может быть включен в этот список top3, если он сыграл более 50% игр своей команды:

 SELECT games_stats.player, AVG(games_stats.points) AS points_avg
FROM games_stats
WHERE EXISTS (
SELECT *
FROM players
WHERE games_stats.player = players.id AND status = 'active') AND season = 28293
GROUP BY games_stats.player
HAVING COUNT(games_stats.game) >= ((
SELECT COUNT(*)
FROM games
WHERE home IN (
SELECT team
FROM teams_players
WHERE season='28293' AND player=games_stats.player) AND season='28293' AND (STATUS='finished' OR STATUS='complete'))   (
SELECT COUNT(*)
FROM games
WHERE away IN (
SELECT team
FROM teams_players
WHERE season='28293' AND player=games_stats.player) AND season='28293' AND (STATUS='finished' OR STATUS='complete'))) / 2
ORDER BY points_avg DESC
LIMIT 3
 

Проблема в том, что этот запрос очень дорогой в ресурсах сервера и времени выполнения: для его выполнения требуется даже 0,54 секунды, и сервер моего баскетбольного сайта часто перегружается из-за этого, иногда даже происходит сбой. Кэширование результатов запроса недостаточно и помогает лишь немного, потому что баскетбольные матчи играются, а статистика обновляется почти ежедневно.

У меня была одна идея, надеясь, что это сократит время выполнения: я попытался сократить этот повторяющийся подзапрос:

 SELECT team
FROM teams_players
WHERE season='28293' AND player=games_stats.player
 

Это означает, что я хотел превратить свой запрос во что-то подобное:

 SELECT games_stats.player, AVG(games_stats.points) AS points_avg, CONCAT(SELECT team FROM teams_players WHERE season=28293 AND teams_players.player=games_stats.player) AS ids_of_teams
FROM games_stats
WHERE EXISTS (
SELECT *
FROM players
WHERE games_stats.player = players.id AND status = 'active') AND season = 28293
GROUP BY games_stats.player
HAVING COUNT(games_stats.game) >= ((
SELECT COUNT(*)
FROM games
WHERE home IN ids_of_teams AND season='28293' AND (STATUS='finished' OR STATUS='complete'))   (
SELECT COUNT(*)
FROM games
WHERE away IN ids_of_teams AND season='28293' AND (STATUS='finished' OR STATUS='complete'))) / 2
ORDER BY points_avg DESC
LIMIT 3
 

К сожалению, CONCAT() возвращает строку объединенных идентификаторов команд (мне нужен массив). Итак, ГЛАВНЫЙ ВОПРОС: как я могу уменьшить / оптимизировать этот повторяющийся подзапрос? Как установить «поле», которое «хранит» массив идентификаторов, полученных при повторном подзапросе?

РЕДАКТИРОВАТЬ: теперь я вижу, что мой вопрос был неправильным — похоже, проблема была в другом месте. У меня есть вопрос, могу ли я оптимизировать предложение HAVING, в котором есть внутренние запросы SELECT.

Кстати, у вас есть другие идеи, как писать более эффективные запросы о лучших игроках и их статистике? Просто обратите внимание, что я должен «выбрать этих 3 лучших игроков, которые сыграли более 50% игр своих команд».

Объяснение структуры базы данных: Таблица «игроки» хранит данные о каждом игроке в баскетбольной лиге. Игрок может сменить команду в следующем сезоне или в текущем сезоне, поэтому сводная таблица «teams_players» описывает, в каких командах выступал игрок за свою карьеру;

Сводная таблица «teams_players» имеет внешние ключи «команда», «игрок» и «сезон», ссылающиеся на идентификаторы таблиц «команды», «игроки» и «сезоны».

Таблица «игры» хранит данные об играх; поля «home» и «away» хранят идентификаторы команд-соперников в игре;

В таблице «games_stats» хранятся статистические данные каждого игрока по игре. Он имеет внешний ключ «game», ссылающийся games.id . Он также имеет внешний ключ «player», ссылающийся players.id .

РЕДАКТИРОВАТЬ: вывод EXPLAIN:

Я думаю, что с помощью временных таблиц это должно быть проще и эффективнее. Трудно сказать с реальной скрипкой:

 SET @var_season := 28293;

DROP TEMPORARY TABLE IF EXISTS tmp_team_games_played;
CREATE TEMPORARY TABLE tmp_team_games_played
    (PRIMARY KEY (id))
SELECT t.id, COUNT(0) AS Count
FROM games g
JOIN teams t ON t.id IN (g.home, g.away)
WHERE TRUE
    AND g.season = @var_season AND (g.STATUS IN ('finished', 'complete'))
GROUP BY t.id
;

DROP TEMPORARY TABLE IF EXISTS tmp_player_team_points;
CREATE TEMPORARY TABLE tmp_player_team_points
    (PRIMARY KEY (player, team))
SELECT gs.player, gs.team, SUM(gs.points) AS points_in_team_games
FROM games_stats gs
JOIN players p ON gs.player = p.id AND p.status = 'active' -- reorder based on index
WHERE TRUE
    AND gs.season = @var_season
GROUP BY gs.player, gs.team
;

SELECT tptp.player, AVG(tptp.points_in_team_games) AS points_avg
FROM tmp_player_team_points tptp
JOIN tmp_team_games_played tgp ON tptp.team = tgp.id
GROUP BY tptp.player
-- I took the liberty to make a player play more than a half
-- as opposed to greater than or eqaul to half
HAVING COUNT(0) > (MIN(tgp.Count) * 2)
ORDER BY points_avg DESC
LIMIT 3
;
 

(Слишком сложный) Предложение HAVING заставляет меня думать, что этот запрос мог бы быть написан иначе.

Можете ли вы проверить это и прокомментировать результаты?:

 SELECT 
  games_stats.player, 
  AVG(games_stats.points) AS points_avg
FROM games_stats
INNER JOIN (
  select team
  from games
  inner join teams_players on (teams_players.team=home OR teams_players.team=away)
                           and teams_players.season=games.season
  where games.season=28293
    and (games.status='finished' or games.status='complete')
  ) x on x.team=games_stats.team
WHERE 
  season=28293
  and EXISTS (
    SELECT *
    FROM players
    WHERE games_stats.player = players.id AND status = 'active') 
GROUP BY games_stats.player
ORDER BY points_avg DESC
LIMIT 3;