На главную страницу
Характерные ошибки при решении упражнений.
Задача 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
На главную страницу