Упражнение 53

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

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. Эта задача рассматривается далее, но там решение содержит другую ошибку.

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

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

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

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

вместо 19/2.

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

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

Четвертый контрпример… Придумайте сами. Вот так и формируется проверочная база сайта. :-)

ПиР

Решить задачу на SQL-EX.RU