Версия для печати

На главную страницу

Характерные ошибки при решении упражнений. Задача 53

Определите среднее число орудий для классов линейных кораблей. Получить результат с точностью до 2-х десятичных знаков

Я полагал, что в этой задаче лишь одна проблема - округление. Однако получил следующее решение:

SELECT SUM(sum_g)/SUM(count_g) FROM
(SELECT SUM(numGuns) AS sum_g, COUNT(*) AS count_g
    FROM Classes INNER JOIN Ships ON Classes.class = Ships.class
     WHERE type='bb'
UNION
SELECT SUM(numGuns) AS sum_g, COUNT(*) AS count_g
     FROM Classes INNER JOIN Outcomes ON Classes.class=Outcomes.ship
     WHERE type='bb') AS a

Богатое для анализа ошибок решение :-). Начнем с округления. Число орудий - целое число (по типу столбца, а не по смыслу!). Поэтому и сумма будет числом целым. При делении целых чисел в SQL Server всегда получается целое число. Причем результат достигается не округлением, а ОТБРАСЫВАНИЕМ дробной части. Выполните, например, следующий запрос

SELECT 2/3

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

SELECT SUM(sum_g)*1.0/SUM(count_g)

т.е. при умножении на вещественную единицу числитель становится вещественным числом.

Теперь, поскольку требуется определить среднее по классам, то, во-первых, не нужно учитывать корабли, а, во-вторых, не нужно учитывать корабли из таблицы Outcomes.

Однако чтобы проанализировать допущенные ошибки, давайте рассмотрим решение в трактовке автора этого запроса, т.е. определим среднее значение по всем линейным кораблям из базы данных, а это не что иное, как задача 54. Я уже рассматривал эту задачу в выпуске 30, но с другой ошибкой.

Итак, в подзапросе подсчитывается число орудий и количество отдельно по кораблям из таблицы Ships и головным кораблям из таблицы Outcomes. Затем в основном запросе суммируем число орудий и количество кораблей, полученных по каждой таблице, и делим одно на другое, чтобы получить среднее значение.

Рассмотрим пример. Пусть в Ships есть 2 корабля с 11 и 8 орудиями, а в Outcomes - один корабль с 11-ю орудиями. Итого получаем 3 корабля и 30 орудий. Среднее 30/3=10. Правильно? Нет, т.е. правильно только в ряде случаев. Нам же нужно написать запрос, который будет верен на любых данных. Я вижу несколько контрпримеров.

Первый контрпример. А если в Outcomes не будет головного корабля, отвечающего условиям задачи? Тогда второй подзапрос даст: кораблей 0, число орудий NULL. В результате вычисление среднего в запросе даст

(19 + NULL)/(2+0) = NULL

вместо 10.

Второй контрпример. Пусть головной корабль класса 'bb' есть как в таблице Ships, так и в таблице Outcomes, т.е. это один и тот же корабль. Тогда в результате мы должны получить не 30/3, что нам дает представленное решение, а 19/2.

Третий контрпример. А если в предыдущей ситуации по кораблям головной корабль дважды принимал участие в сражениях? Тогда мы получим вместо тех же 19/2 - (19 + 22)/(2+2) = 41/4.

Четвертый контрпример… Придумайте сами.

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

Перейти к решению задачи #53

На главную страницу

  Версия для печати