Упражнение 37

Решение 3.2.1

Вот один из запросов, которые отвергает система проверки:

SELECT class
FROM Ships
GROUP BY class
HAVING COUNT(name) = 1
UNION
SELECT class
FROM Classes c, Outcomes o
WHERE c.class = o.ship 
      AND NOT EXISTS (SELECT 'x'
                      FROM Ships s
                      WHERE o.ship = s.class
                     );

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

Рассмотрим следующий пример данных, для которых этот запрос будет давать неправильный результат.

Каждый, кто решал задачи по схеме данных «Корабли», знает, что такое «Бисмарк» (Bismarck). Это головной корабль, которого нет в таблице Ships. Теперь представим себе, что другой корабль класса «Бисмарк» имеется в таблице Ships, скажем, «Тирпиц» (Tirpitz).

Тогда первый запрос вернет класс «Бисмарк», так как в таблице Ships имеется один корабль этого класса. Второй запрос класс «Бисмарк» не вернет, так как предикат:

NOT EXISTS (SELECT 'x'
            FROM Ships s
            WHERE o.ship = s.class
            )

для корабля «Бисмарк» в таблице Outcomes будет оценен как FALSE. В результате объединения этих запросов получим класс «Бисмарк» в выходных данных всего запроса.

Всякому, кто внимательно следил за ходом рассуждений, понятно, что в базе данных имеется два корабля класса «Бисмарк». То есть этот класс не должен присутствовать в результатах выполнения запроса.

Чтобы проверить это, добавьте в основную базу данных следующую строку:

INSERT INTO Ships VALUES('Tirpitz', 'Bismark', 1940);

Решение 3.2.2

Следующее решение было построено одним из посетителей сайта после получения приведенных выше объяснений. Оно также дает правильный результат на основной базе данных.

SELECT class
FROM Ships sh
WHERE NOT EXISTS (SELECT ship
                 FROM Outcomes
                 WHERE ship = sh.class
                 )
GROUP BY class
HAVING COUNT(*) = 1
UNION
SELECT ship
FROM Outcomes s
WHERE EXISTS (SELECT class
             FROM Classes
             WHERE class = s.ship
             ) 
    AND NOT EXISTS (SELECT class
                    FROM Ships
                    WHERE class = s.ship
                    );

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

В первом же запросе выбираются все корабли из таблицы Ships кроме тех, для которых в таблице Outcomes имеется головной корабль. Далее выполняется группировка по классу и отфильтровываются (HAVING) только те классы, в которых оказался один корабль.

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

Решение 3.2.3

Посмотрите теперь, как можно более просто написать запрос, содержащий аналогичную ошибку:

SELECT class
FROM (SELECT class
    FROM Ships
    UNION ALL
    SELECT ship
    FROM Outcomes o
    WHERE o.ship IN(SELECT class
                    FROM Classes
                    )
) AS cl
GROUP BY class
HAVING COUNT(class) = 1;

Идея такая. В подзапросе выбираются классы всех кораблей из таблицы Ships и объединяются с головными кораблями из таблицы Outcomes с сохранением дубликатов (UNION ALL). При этом используется тот факт, что имя головного корабля совпадает с именем класса (SELECT Ship) (!!!). То, что дубликаты сохраняются, — это совершенно правильно, так как в противном случае мы получим на класс одну строку для любого количества кораблей в классе. Затем делается группировка по классу, и фильтруются классы, содержащие один корабль. Решение выглядит значительно короче и понятней, чем решение 3.2.2. Его и исправить будет проще, а исправлять придется, так как решение даст неверный результат, если головной корабль присутствует как в таблице Ships, так и в таблице Outcomes, в результате чего мы его дважды посчитаем.

Решение 3.2.4

Подход на основе объединения не является единственно возможным. Следующее решение использует соединения.

SELECT Classes.class
FROM Outcomes 
    RIGHT OUTER JOIN Classes ON Outcomes.ship = Classes.class 
    LEFT OUTER JOIN Ships ON Classes.class = Ships.class
GROUP BY Classes.class
HAVING (COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1);

Правое соединение таблиц Outcomes и Classes дает нам головные корабли, при этом столбец Outcomes.ship будет содержать NULL-значение, если головного корабля нет в таблице Outcomes. Затем выполняется левое соединение таблиц Classes и Ships по внешнему ключу. Столбец Ships.name будет содержать NULL-значение, если класс не имеет кораблей в таблице Ships. Полученный набор записей группируется по классу, после чего выполняется фильтрация по предикату

COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1

Остановимся подробнее на этом элегантном приеме.

Чтобы все было до конца ясно, приведем примеры четырех возможных вариантов строк (взятых из доступной базы данных кроме последнего случая), которые получаются в результате соединения. Вот они:

NULL в столбце name для класса Bismarck означает, что головной корабль имеется только в таблице Outcomes. Корабли California и Tennessee класса Tennessee имеются в таблице Ships, при этом головной корабль есть также в таблице Outcomes. В третьем случае два корабля класса Yamato присутствуют в таблице Ships, в таблице же Outcomes нет головного корабля данного класса. Для четвертого случая класс Class_1 не имеет кораблей в базе данных.

Вернемся к предикату. Функция COALESCE (Outcomes.ship, Ships.name) вернет первое не NULL значение своих аргументов или NULL, если оба аргумента есть NULL-значение. Подробнее о функции COALESCE можно почитать здесь.

Агрегатная функция COUNT, имеющая аргумент, вернет количество не NULL-значений аргумента в группе. Поэтому для класса Bismarck мы получим 1, для Tennessee и Yamato — 2 и, наконец, для Class_1 — 0. В результирующий набор из этих четырех классов попадает только Бисмарк, так как только он отвечает предикату.

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

Кроме того, в этом решении содержится та же ошибка, что и в примере 3.2.1. Если добавить в базу данных указанную там строку, то в результате соединения мы получим только одну строку на два корабля класса Бисмарк:

Если вы еще не обнаружили ошибки, упомянутой на врезке, загляните в ПиР.

ПиР

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