Упражнение 70
Решение 3.7.1
SELECT AA.name AS bat
FROM (SELECT O.battle AS name, C.country, COUNT(O.ship) AS cnt
FROM Outcomes O, Ships S, Classes C
WHERE O.ship = S.name
AND C.class = S.class
GROUP BY O.battle, C.country
) AA
WHERE AA.cnt >= 3;
[[ column ]] |
---|
[[ value ]] |
Можно назвать этот запрос «первым приближением» к решению. Соединяются все необходимые таблицы через предложение WHERE, в результате чего определяется битва и страна (из таблицы Classes) для кораблей из таблицы Outcomes. Далее выполняется группировка по стране и сражению с последующим отбором по числу кораблей.
Ошибочным здесь является то, что мы никак не учитываем корабли, отсутствующие в таблице Ships, так как используются внутренние соединения. Читатель уже, наверное, вник в используемую схему и понимает, что здесь не учитываются головные корабли, класс которых может быть определен не только через таблицу Ships, но и непосредственно с помощью таблицы Classes, а, следовательно, может быть определена и владеющая кораблем страна. Теперь рассмотрим решения, в которых была сделана попытка учесть эту особенность схемы данных.
Решение 3.7.2
SELECT bat
FROM (SELECT DISTINCT d.battle AS bat, a.country, count(d.ship) AS s
FROM Outcomes d, Ships b, Classes a
WHERE d.ship = b.name
AND b.class=a.class
GROUP BY d.battle, a.country
UNION
SELECT DISTINCT d.battle as bat, a.country, count(d.ship) AS s
FROM Outcomes d, Classes a
WHERE d.ship = a.class
AND d.ship NOT IN (SELECT name
FROM Ships
)
GROUP BY d.battle, a.country
) AS t1
WHERE s > 2;
[[ column ]] |
---|
[[ value ]] |
Ошибка, характерная для начинающих, состоит в том, что сначала выполняется группировка, а потом объединение. И хотя здесь отсутствует ошибка решения 3.7.1 (во втором запросе объединения учтены головные корабли, которых нет в Ships), решение не даст нам страну, у которой в сражении участвовало 3 корабля, два из которых присутствуют в таблице Ships, а один (головной) — только в таблице Outcomes.
Одно время на сайте системой проверки принималось заведомо неправильное решение:
Решение 3.7.3
SELECT battle
FROM Classes c
LEFT JOIN Ships s ON c.class = s.class
INNER JOIN Outcomes o ON o.ship = s.name
OR c.class = o.ship
GROUP BY battle, country
HAVING COUNT(ship) > 3;
[[ column ]] |
---|
[[ value ]] |
Обратите внимание на HAVING COUNT(ship) > 3. Использование правильного предиката с условием >=3 делало запрос неверным, каким он и является. Подгонка решения позволила обнаружить огрех в проверке, который и был устранен.
Итак, запрос соединяет классы с кораблями из таблицы Ships, чтобы определить страну корабля. Левое соединение (LEFT JOIN) используется для того, чтобы не потерять класс, если кораблей этого класса нет в таблице Ships. Такой (и не только) класс понадобится для того, чтобы учесть головные корабли из таблицы Outcomes, что и делается в следующем (внутреннем) соединении. Предикат этого соединения
ON o.ship = s.name OR c.class = o.ship
сформирует строку, в столбце ship которой будет находиться имя корабля, принимавшего участие в сражениях, если его имя совпадает с именем корабля известного класса в таблице Ships или если его имя совпадает с именем класса (головной корабль). Если корабль не принимал участия в сражении, то значением в столбце ship будет NULL. Затем выполняется группировка по паре атрибутов {battle, country} с предложением HAVING COUNT(ship) >= 3, что позволяет отобрать только те страны, которые участвовали в битве более чем двумя кораблями. Заметим, что функция COUNT корректно обработает NULL-значения в столбце ship.
Важно
О разнице в использовании COUNT(*) и COUNT(имя столбца) можно почитать в главе “Получение итоговых значений”.
В этом «или» предиката (1) и заключается основная ошибка этого запроса. Если один и тот же головной корабль имеется и в таблице Outcomes, и в таблице Ships, то он будет учтен дважды для одной и той же битвы. Это можно увидеть из следующего запроса:
SELECT battle, country, ship, COUNT(*) qty
FROM Classes c
LEFT JOIN Ships s ON c.class = s.class
INNER JOIN Outcomes o ON o.ship = s.name
OR c.class = o.ship
GROUP BY battle, country, ship;
[[ column ]] |
---|
[[ value ]] |
Приведем здесь только одну неправильную строку результата:
Battle | country | ship | qty |
---|---|---|---|
Surigao Strait | USA | Tennessee | 2 |
Явная ошибка, так как один и тот же корабль не может дважды упоминаться для одной и той же битвы (Surigao Strait), что запрещено первичным ключом на таблице Outcomes.
Отметим, что рассматриваемый запрос, как и решения 3.7.2 и 3.7.1, содержит еще одну ошибку, встречающуюся настолько часто, что она даже описана в FAQ на сайте sql-ex.ru. Эта ошибка заключается в том, что поскольку группировка выполняется по паре атрибутов {battle, country}, то битва будет выводиться неоднократно, если в ней принимало участие минимум по 3 корабля от каждой участвовавшей в битве страны.
Остается один вопрос. Почему же при трех отмеченных ошибках (>3 вместо >=3, ошибочное соединение и возможное появление дубликатов) запрос принимался системой?
Попробуем разобраться. В основной базе не было ни одной битвы, для которой бы выполнялось условие задачи. Правильное решение показывало пустой набор записей. Поэтому ошибочное увеличение числа кораблей не работало с правильным критерием (>=3), так как запрос выдавал битву Surigao Strait, хотя в ней реально принимало участие 2 корабля из USA. А вот условие >3 опять давало пустой набор.
В проверочной базе для блокировки решения с неисключенными дубликатами для одной битвы было два набора по 3 и более корабля разных стран. При этом в одном наборе головной корабль присутствовал в обеих таблицах (Outcomes и Ships). Для этого набора рассматриваемым запросом ошибочно считалось 4 корабля, а для второго правильно — 3. Поэтому условие в предикате HAVING – > 3 и давало только одну битву, разрешая самым неожиданным образом проблему с дубликатами.
Мир полон неожиданностей; чем больше делается ошибок, тем больше вероятность совпадения результатов.
Пустой набор результата решения этой задачи на основной базе неоднократно вызывал нарекания. Поэтому автор, попутно блокируя рассмотренное неверное решение, добавил данных и в основную базу.
Решение 3.7.4
SELECT DISTINCT battle
FROM (SELECT battle, country
FROM (SELECT battle, country
FROM Outcomes
INNER JOIN Classes ON ship = class
UNION
SELECT battle, country
FROM Outcomes o
INNER JOIN Ships s ON o.ship = s.name
INNER JOIN Classes c ON s.class = c.class
) x
GROUP BY battle, country
HAVING COUNT(*) > 2
) y;
[[ column ]] |
---|
[[ value ]] |
Во внутреннем подзапросе объединяются два запроса. В первом из них
SELECT battle, country
FROM Outcomes
INNER JOIN Classes ON ship = class;
[[ column ]] |
---|
[[ value ]] |
SELECT battle, country
FROM Outcomes o
INNER JOIN Ships s ON o.ship = s.name
INNER JOIN Classes c ON s.class = c.class;
[[ column ]] |
---|
[[ value ]] |
Использование для объединения предложения UNION устраняет дубликаты. С одной стороны, это кажется правильным, так как головной корабль может находиться как в таблице Outcomes, так и в таблице Ships. С другой стороны, после удаления дубликатов в результирующем наборе останется только одна уникальная пара {сражение, страна}, а это означает, что для любого числа кораблей из одной страны, останется лишь одна строка для каждого из сражений. В результате последующая группировка оказывается попросту излишней, как и предложение HAVING.
Первое, что приходит в голову, — написать UNION ALL вместо UNION, то есть учесть все дубликаты. Но, как уже понятно из предыдущего обсуждения, тогда для одного головного корабля, участвующего в некотором сражении, мы получим две строки, если этот корабль присутствует еще и в таблице Ships.
Как поступить? Автор предлагает два подхода. При первом подходе мы оставляем UNION, но подсчитываем не страны, а корабли. Тогда устранение дубликатов будет правильным. При втором подходе автор предполагает использование UNION ALL, но тогда нужно в одном из объединяемых запросов проверять, чтобы учитываемый корабль не присутствовал в другой таблице, тем самым подсчитывая его один раз.
Какой из способов предпочесть, зависит не только от наших предпочтений, но и от эффективности плана выполнения запроса. Предлагаем вам самостоятельно оценить планы, предварительно решив задачу двумя описанными способами.