Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

База данных «Корабли»

Решение 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;

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. Поступим формально, то есть не будем домысливать предметную область, а обратимся к схеме (рис. 3.1). В таблице Ships первичным ключом является имя корабля, поэтому первый запрос в объединении даст нам по одной строке на каждый корабль известного класса. В таблице же Outcomes ключом является пара {ship, battle}, то есть уникальность обеспечивается для комбинации имени корабля и сражения, в котором он принимал участие. Отсюда следует, что один и тот же корабль может несколько раз упоминаться в таблице Outcomes, что соответствует участию данного корабля в нескольких сражениях.

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

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

ПиР