Упражнение 54

Решение 3.10.1

SELECT ROUND(SUM(ng)/SUM(cnt), 2) res
FROM (SELECT SUM(numGuns) ng, COUNT(*) cnt
    FROM Classes c,Ships s
    where c.class = s.class 
        AND c.type = 'bb'
    UNION ALL
    SELECT SUM(numGuns) ng, count(*) cnt
    FROM Classes c, Outcomes o
    WHERE c.class = o.ship 
        AND c.type = 'BB'
        AND NOT EXISTS (SELECT name 
                        FROM Ships s
                        WHERE s.name = o.ship)
    ) x;

В этом решении сделана попытка вручную посчитать среднее как сумму значений, деленную на их количество. Однако специфика арифметических операций в SQL Server состоит в том, что результат всегда приводится к типу аргумента. Поскольку число орудий — целое число (тип INTEGER для столбца numGuns), то дробная часть числа, полученного при делении, будет попросту отбрасываться, что заведомо даст неправильный результат.

Это легко проверить, если выполнить запрос

SELECT AVG(a) from (values(1),(2))X(a); -- 3/2

Если вы будете выполнять аналогичные запросы на сайте sql-ex.ru, поставьте флажок «Без проверки» на странице с упражнениями, чтобы система не выполняла бесполезного сравнения результата с эталонным решением соответствующего упражнения.

Для получения «точного» результата деления целых чисел нужно привести операнд (хотя бы один) к вещественному типу. Это можно сделать с помощью функции приведения типа CAST или простым умножением на вещественную единицу, как мы и поступим:

SELECT SUM(numGuns*1.0) ng

Теперь поговорим об округлении, которое использует функцию T-SQL ROUND. Опять обратимся к простому примеру (округление до двух цифр после десятичной точки):

SELECT ROUND(AVG(5.0/3),2);

Решение 3.10.2

SELECT CAST(AVG(numGuns*1.0) AS NUMERIC(10,2))
FROM (SELECT numguns
    FROM Classes c 
        JOIN Ships s ON c.class = s.class
    WHERE type = 'bb'
    UNION ALL
    SELECT numguns
    FROM Classes] c 
        JOIN Outcomes o ON c.class = o.ship
    WHERE type='bb' 
        AND o.ship NOT IN(SELECT name
                        FROM Ships
                        )
    ) t;

Обратите внимание на приведение типа к числу с фиксированной точкой, которое и выполняет требуемое округление результата.

В подзапросе объединяются (UNION ALL) два запроса. Первый определяет число орудий для кораблей в таблице Ships, принадлежащих классам линейных кораблей (тип bb). Второй учитывает головные корабли соответствующих классов при условии, что их нет в таблице Ships.

Таким образом, сделана попытка учесть каждый корабль в БД только один раз. Поскольку для объединения используется UNION ALL, то дубликаты устраняться не будут. Это совершенно справедливо, так как многие корабли будут иметь одинаковое число орудий, а в предложении SELECT подзапроса выводится только этот столбец.

И все же ошибка связана именно с использованием UNION ALL. Поступим формально, то есть не будем домысливать предметную область, а обратимся к схеме. В таблице Ships первичным ключом является имя корабля, поэтому первый запрос в объединении даст нам по одной строке на каждый корабль известного класса. В таблице же Outcomes ключом является пара {ship, battle}, то есть уникальность обеспечивается для комбинации имени корабля и сражения, в котором он принимал участие. Отсюда следует, что один и тот же корабль может несколько раз упоминаться в таблице Outcomes, что соответствует участию данного корабля в нескольких сражениях.

В результате второй запрос в объединении даст дубликаты кораблей, если головной корабль участвовал в нескольких сражениях. Это и делает ошибочным представленное решение.

С другой стороны, и UNION вместо UNION ALL мы написать не можем по указанной выше причине.

ПиР

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