Упражнение 55

Видимо, ошибки вызваны наличием ловушек в предыдущих задачах. При решении задач со сложностью 1 делаются попытки учесть все и вся. Сам по себе учет излишних фактов не делает решение неверным, разве что увеличивает стоимость выполнения запроса, но дело в том, что при этом допускались столь характерные ошибки, что автор решил рассмотреть несколько подобных решений.

Что же лишнего пытаются учесть при решении этой задачи? Это — головные корабли из таблицы Outcomes. Таблица Outcomes вообще не нужна для решения этой задачи. Ведь нам нужно определить год, который является атрибутом таблицы Ships. Поэтому даже если в таблице Outcomes есть головной корабль, отсутствующий в таблице Ships, то мы все равно не знаем года его спуска на воду. В случае же отсутствия в БД других кораблей этого класса наличие такого корабля все равно ничего не дает, так как результат должен выглядеть следующим образом:

поскольку в задании сказано «для каждого класса». Тем самым утверждается, что для данного класса год спуска первого корабля неизвестен. Но такую строку в результирующем наборе мы можем получить и без таблицы Outcomes, выполнив внешнее соединение таблицы Classes с таблицей Ships. Перейдем к анализу решений, в которых были допущены ошибки при учете кораблей из таблицы Outcomes.

Решение 3.11.1

SELECT C.class , launched
FROM Classes C ,
    (SELECT name , class , launched
    FROM Ships
    UNION
    SELECT ship , ship , NULL
    FROM Outcomes O
    WHERE NOT EXISTS (SELECT *
                    FROM Ships S
                    WHERE S.class = O.ship
                    )
    UNION
    SELECT ship , ship , MIN(launched)
    FROM Ships S , Outcomes O
    WHERE S.class = O.ship
    GROUP BY ship
    ) S
WHERE C.class = S.name;

Рассмотрим подзапрос S, в котором объединяются три запроса, результирующий набор каждого из которых содержит три столбца {имя корабля, класс, год спуска на воду}. В первом из них выбираются все корабли из таблицы Ships. Во втором выбираются корабли из Outcomes, имя которых не совпадает ни с одним классом кораблей из таблицы Ships. При этом в качестве года спуска на воду используется NULL, что правильно, а имя корабля ассоциируется с именем класса (SELECT ship, ship, NULL). Последнее не является здесь ошибкой, так как в последующем будут отбираться только головные корабли:

WHERE C.class = S.name

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

Принципиальная ошибка заключается в наличии ситуации, когда головной корабль из Outcomes присутствует также и в таблице Ships с неизвестным годом спуска на воду. Кроме того, имеются и другие корабли того же класса с известным годом спуска на воду. Тогда первый запрос даст строку с годом NULL, а третий — с минимальным годом по этому классу. В итоге получим две строки, которые не являются дубликатами и, следовательно, не будут исключены использованием UNION.

Наконец, условие отбора только по головным кораблям исключит из рассмотрения классы, вообще не имеющие головных кораблей.

Решение 3.11.2

SELECT DISTINCT class, MIN(launched)
FROM Ships GROUP BY Class
UNION
SELECT DISTINCT Ship AS class, NULL
FROM Outcomes
WHERE ship IN (Select class
                FROM Classes
                ) 
AND ship NOT IN (SELECT name
                FROM Ships
                );

Первый запрос в объединении считает минимальный год спуска на воду по классам кораблей из таблицы Ships. Во втором запросе выбираются те головные корабли из таблицы Outcomes (предикат IN), которых нет в таблице Ships (предикат NOT IN). Здесь, как и в решении 3.11.1, такой корабль (класс) учитывается с NULL в качестве года спуска на воду. Однако ошибка здесь уже другая. Суть ее заключается в ситуации, когда в Outcomes есть головной корабль, которого нет в таблице Ships (пусть это будет корабль «Бисмарк»), но в Ships есть другой корабль класса «Бисмарк» с известным годом спуска на воду. В результате мы опять получаем две строки на класс, с известным и неизвестным годом спуска на воду.

По поводу решения 3.11.2 следует отметить совершенно излишнее, и даже вредное с точки зрения производительности, задействование DISTINCT. В первом из объединяемых запросов группировка делает появление дубликатов невозможным. Во втором запросе, если и будут дубликаты (участие корабля в нескольких сражениях), они устраняются использованием при объединении предложения UNION.

Теперь рассмотрим решения, где не применяется таблица Outcomes, но, тем не менее, допускается логическая ошибка.

Решение 3.11.3 (с комментариями автора решения)

/*
Год спуска на воду головных кораблей
*/
SELECT class, launched AS year
FROM Ships
WHERE name = class
UNION
/*
Минимальный год спуска на воду кораблей по классам,
у которых нет данных по головным кораблям в таблице Ships
*/
SELECT class, MIN(launched)
FROM Ships
WHERE class NOT IN (SELECT class
                    FROM Ships
                    WHERE name = class
                    )
GROUP BY class
UNION
/*
Выводим NULL в качестве года спуска на воду для классов,
кораблей которых нет в Ships
*/
SELECT class, NULL
FROM classes
WHERE class NOT IN(SELECT class
                    FROM ships
                    );

В этом решении в соответствии с условием задачи учтены все классы, включая те, которые не имеют кораблей в БД (последний запрос в объединении). По-видимому, допущенная здесь ошибка связана с попыткой отдельного учета головных кораблей (которые являются первыми кораблями в классе и, следовательно, имеют наименьший год спуска на воду) и классов, не имеющих головных кораблей в базе данных.

Представим ситуацию, когда год спуска головного корабля неизвестен (NULL), но при этом он имеется в таблице Ships. Там же находится другой корабль аналогичного класса с известным годом спуска на воду. Тогда именно этот год должен по условию задачи фигурировать в выходном наборе.

Данное же решение даст (первый запрос в объединении) NULL, в то время как корабль с нужным годом будет проигнорирован во втором запросе объединения в силу следующей фильтрации

WHERE class NOT IN (SELECT class
                    FROM Ships
                    WHERE name = class
                    )

Аналогичная ошибка содержится и в нижеследующем варианте, который вам предлагается проанализировать самостоятельно.

Решение 3.11.4

SELECT class, (SELECT launched
               FROM (SELECT launched
                    FROM Ships sh
                    WHERE cl.class = sh.name
                    UNION
                    SELECT launched
                    FROM Ships sh
                    WHERE launched = (SELECT MIN(launched)
                                    FROM ships sh2
                                    WHERE class = cl.class 
                                        AND NOT EXISTS(SELECT launched
                                                        FROM ships sh
                                                        WHERE cl.class = sh.name
                                                        )
                                    )
                    ) tab
                ) year
FROM classes cl;

ПиР

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