Как найти матч с максимальным количеством голов в SQL

В задачах анализа спортивной статистики часто требуется определить встречу, в которой было забито больше всего мячей. На примере таблиц Games и Lineups разберём корректный SQL-запрос, который учитывает все нюансы: NULL-значения, автоголы и агрегацию данных. Это руководство поможет избежать типичных ошибок и подготовить решение, проходящее верификацию.

Структура таблиц и условие задачи

Таблица Games содержит информацию о матчах: game_id (номер тура), team (команда-соперник), goals (голы соперника, включая автоголы, может быть NULL), own (автоголы соперника). Таблица Lineups хранит данные об игроках: game_id, goals (голы игрока). Необходимо найти матч (или матчи), в котором суммарно забито больше всего голов, и вывести команду-соперника и общее количество голов.

Основные ошибки при решении

Главная проблема - некорректная обработка NULL-значений в столбце goals. Если игрок не забивал, его значение может быть NULL, и прямое суммирование (SUM()) без учёта этого даст неверный результат. Также важно правильно объединять таблицы через LEFT JOIN, чтобы не потерять матчи, где ни один игрок не забил. Другая частая ошибка - использование GROUP BY по лишним полям, что нарушает логику агрегации.

Правильный SQL-запрос с учётом NULL

Корректное решение включает следующие шаги:

• Объединяем таблицы Games и Lineups по game_id с помощью LEFT JOIN.
• Для каждого матча считаем сумму голов из Lineups, заменяя NULL на 0 через COALESCE() или ISNULL().
• Добавляем к этой сумме голы из Games (также обрабатываем NULL).
• Находим максимальное значение общего числа голов через подзапрос или TOP 1 WITH TIES.

WITH match_goals AS (
  SELECT g.team, 
         COALESCE(SUM(l.goals), 0) + COALESCE(g.goals, 0) AS total_goals
  FROM Games g
  LEFT JOIN Lineups l ON g.game_id = l.game_id
  GROUP BY g.game_id, g.team, g.goals
)
SELECT team, total_goals
FROM match_goals
WHERE total_goals = (SELECT MAX(total_goals) FROM match_goals);

Альтернативное решение с TOP

В СУБД, поддерживающих TOP (например, MS SQL Server), можно использовать:

WITH match_goals AS (
  SELECT g.team, 
         COALESCE(SUM(l.goals), 0) + COALESCE(g.goals, 0) AS total_goals
  FROM Games g
  LEFT JOIN Lineups l ON g.game_id = l.game_id
  GROUP BY g.game_id, g.team, g.goals
)
SELECT TOP 1 WITH TIES team, total_goals
FROM match_goals
ORDER BY total_goals DESC;

Ключевое слово WITH TIES гарантирует, что если несколько матчей имеют одинаковое максимальное число голов, все они попадут в результат.

Проверка и верификация

Чтобы решение прошло проверку на тестовой базе, обязательно:

• Используйте COALESCE для столбцов goals в обеих таблицах.
• Группируйте по game_id, team и goals из Games, чтобы корректно суммировать голы игроков.
• Учитывайте, что goals в Games уже включает автоголы соперника (столбец own), поэтому добавлять их отдельно не нужно.

Заключение

Поиск матча с максимальным числом голов в SQL требует внимания к NULL-значениям и правильной агрегации. Предложенные запросы универсальны и подходят для большинства реляционных баз данных. Используйте их как шаблон для аналогичных задач.