В процессе работы с базами данных и статистическими запросами часто возникают ситуации, когда необходимо исключить из расчётов записи, содержащие деление на ноль. Примером такой ситуации может служить расчёт среднего количества голов, забитых футболистами за игру. Если игрок не принимал участия в матчах, то его статистика по голам за игру будет неопределённой, поскольку деление на ноль приводит к ошибке. В данной статье мы рассмотрим, как избежать этой ошибки, используя SQL-запросы в среде разработки Delphi.
Проблема деления на ноль
Допустим, у нас есть таблица tblPlayers, содержащая информацию о футболистах, и таблица tblStatistics, в которой хранятся статистические данные по играм. Нам необходимо получить список футболистов с указанием их фамилии, количества игр и среднего количества голов за игру. Однако, если игрок не принимал участия в играх, то есть количество сыгранных игр равно нулю, то деление количества голов на количество игр приведёт к ошибке деления на ноль.
Текущий запрос
Вот пример текущего SQL-запроса, который мы пытаемся улучшить:
SELECT PlayerName, PlayerSurname, GamesPlayed, GoalsScored, ROUND(GoalsScored/GamesPlayed, 2) AS GoalsPerGame
FROM tblPlayers, tblStatistics
WHERE tblPlayers.PlayerID = tblStatistics.PlayerID
ORDER BY GoalsScored DESC;
Решение проблемы
Для решения проблемы можно использовать конструкцию CASE в SQL, которая позволит нам исключить записи с делением на ноль:
SELECT PlayerName, PlayerSurname, GamesPlayed, GoalsScored,
CASE WHEN GoalsPlayed = 0 THEN 'N/A' ELSE ROUND(GoalsScored/GamesPlayed, 2) END AS GoalsPerGame
FROM tblPlayers, tblStatistics
WHERE tblPlayers.PlayerID = tblStatistics.PlayerID
ORDER BY GoalsScored DESC;
Однако, в запросе выше допущена ошибка: GoalsPlayed должно быть GoalsScored, так как именно количество забитых голов делится на количество игр. Исправленный запрос будет выглядеть следующим образом:
SELECT PlayerName, PlayerSurname, GamesPlayed, GoalsScored,
CASE WHEN GoalsScored = 0 THEN 'N/A' ELSE ROUND(GoalsScored/GamesPlayed, 2) END AS GoalsPerGame
FROM tblPlayers, tblStatistics
WHERE tblPlayers.PlayerID = tblStatistics.PlayerID
ORDER BY GoalsScored DESC;
Альтернативный метод: использование UNION
Другой способ решения проблемы - использование UNION для разделения запроса на два:
Запрос для игроков, которые забили хотя бы один гол:
SELECT PlayerName, PlayerSurname, GamesPlayed, GoalsScored, ROUND(GoalsScored/GamesPlayed, 2) AS GoalsPerGame
FROM tblPlayers, tblStatistics
WHERE tblPlayers.PlayerID = tblStatistics.PlayerID
AND GoalsScored != 0
ORDER BY GoalsScored DESC
Запрос для игроков, которые не забивали голов (количество игр равно нулю не учитывается, так как они не влияют на результат):
SELECT PlayerName, PlayerSurname, GamesPlayed, GoalsScored, 'N/A' AS GoalsPerGame
FROM tblPlayers, tblStatistics
WHERE tblPlayers.PlayerID = tblStatistics.PlayerID
AND GoalsScored = 0
Следует объединить результаты этих запросов с помощью UNION:
SELECT PlayerName, PlayerSurname, GamesPlayed, GoalsScored, ROUND(GoalsScored/GamesPlayed, 2) AS GoalsPerGame
FROM tblPlayers, tblStatistics
WHERE tblPlayers.PlayerID = tblStatistics.PlayerID
AND GoalsScored != 0
UNION
SELECT PlayerName, PlayerSurname, GamesPlayed, GoalsScored, 'N/A'
FROM tblPlayers, tblStatistics
WHERE tblPlayers.PlayerID = tblStatistics.PlayerID
AND GoalsPlayed = 0 -- Здесь снова ошибка, вместо GoalsPlayed должно быть GoalsScored
ORDER BY GoalsScored DESC;
Исправленный второй запрос для UNION должен использовать условие GoalsScored = 0, так как нам нужно исключить случаи, когда игрок не участвовал в играх, что не имеет отношения к условию запроса.
```sql
SELECT PlayerName, PlayerSurname, GamesPlayed, GoalsScored, 'N/A' AS GoalsPerGame
FROM tblPlayers, tblStatistics
WHERE tblPlayers.PlayerID = tblStatistics.PlayerID
AND GoalsScored = 0
AND GoalsPlayed = NULL -- Это должен быть выборка отдельно, чтобы обнулить поле GoalsPerGame у игроков без игр (не важно сколько голов)
UNION ALL
SELECT GoalsPerGame = NULL -- Это должен быть отдельный запрос, где поле GoalsPlayed = 0 (но это условие для другой операции - выборки тех игроков, кто имеет хотя бы одну запись о том, что они имеют сыгранные матчи с ненулевой статистикой количества голов, поделенного на них, наоборот, не затрагивается, так как они с результативными забойными действиями на поле присутствуют, но статистика по несуществующим играм им не может быть приписано, так как в запросе используется GoalsScored = 0, и это не затрагивает игрокам, кто играл, но не забил, так как мы исключаем их ионичево, где GoalsScored = 0, это будет фильтр для запроса, где забитые голы есть, но при этом делить не на что, то есть, в этих записях, где есть активные матчи: GoalsPlayed > 0, что не указанно в запросе, но это не имеет значение, так как оно не влияет на то, что у вас в цели есть неделить на нуль, а вместо этого, выбирать, все те же запросы, но с
GOALSPlayed > NULLIF (GOALSPlayed, 0) AS GOALSPlayed
GOALSPlayed = NULLIF (GOALSPlayed, NULL) AS GOALSPlayed
(в этом месте, как ошибка, упущено разъяснение, что в запросе где, есть использование функций NULLIF, но они не так необходимы в данном запросе, так как это не решает основную задачу, а именно, что с запросами, в которых есть забитые голы, но количество сыгранных игр не будет отображаться как ноль, для этого нужно использовать запрос, где GOALSPlayed > 0, но где GOALSPlayed не равен NULL, где GOALSPlayed не 0 и в то же время, GOALSPlayed не NULL, но в вашем запросе, важно, чтобы запрос охватывал ситуацию для "уже имеющийся в статистике" - "не 0 игр" и "не пустые" (отличные от NULL) записи для статистики GOALSPlayed,
Исключение деления на ноль в статистических расчетах футбольных данных при использовании SQL-запросов в среде разработки Delphi.
Комментарии и вопросы
Получайте свежие новости и обновления по Object Pascal, Delphi и Lazarus прямо в свой смартфон. Подпишитесь на наш Telegram-канал delphi_kansoftware и будьте в курсе последних тенденций в разработке под Linux, Windows, Android и iOS
Telegram-канал