loading..
Русский    English
16:38
листать

Упражнение 70 стр. 3

Решение 3.7.3

Консоль
Выполнить
  1. SELECT battle
  2. FROM Classes c LEFT JOIN
  3. Ships s ON c.class = s.class INNER JOIN
  4. Outcomes o ON o.ship = s.name OR
  5. c.class = o.ship
  6. GROUP BY battle, country
  7. HAVING COUNT(ship) > 3;

Обратите внимание на HAVING COUNT(ship) > 3. Использование правильного предиката с условием >=3 делало запрос неверным, каким он и является. Подгонка решения позволила обнаружить огрех в проверке, который и был устранен.

Итак, запрос соединяет классы с кораблями из таблицы Ships, чтобы определить страну корабля. Левое соединение (LEFT JOIN) используется для того, чтобы не потерять класс, если кораблей этого класса нет в таблице Ships. Такой (и не только) класс понадобится для того, чтобы учесть головные корабли из таблицы Outcomes, что и делается в следующем (внутреннем) соединении. Предикат этого соединения

  1. 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(имя столбца) можно почитать в пункте 5.5.

В этом «или» предиката (1) и заключается основная ошибка этого запроса. Если один и тот же головной корабль имеется и в таблице Outcomes, и в таблице Ships, то он будет учтен дважды для одной и той же битвы. Это можно увидеть из следующего запроса:

Консоль
Выполнить
  1. SELECT battle, country, ship, COUNT(*) qty
  2. FROM Classes c LEFT JOIN
  3. Ships s ON c.class = s.class INNER JOIN
  4. Outcomes o ON o.ship = s.name OR
  5. c.class = o.ship
  6. GROUP BY battle, country, ship;

Приведем здесь только одну неправильную строку результата:

Battle country ship qty
Surigao Strait USA Tennessee 2

Явная ошибка, так как один и тот же корабль не может дважды упоминаться для одной и той же битвы (Surigao Strait), что запрещено первичным ключом на таблице Outcomes.

Отметим, что рассматриваемый запрос, как и решения 3.7.2 и 3.7.1, содержит еще одну ошибку, встречающуюся настолько часто, что она даже описана в FAQ на сайте. Эта ошибка заключается в том, что поскольку группировка выполняется по паре атрибутов {battle, country}, то битва будет выводиться неоднократно, если в ней принимало участие минимум по 3 корабля от каждой участвовавшей в битве страны.

Остается один вопрос. Почему же при трех отмеченных ошибках (>3 вместо >=3, ошибочное соединение и возможное появление дубликатов) запрос принимался системой?

Попробуем разобраться. В основной базе не было ни одной битвы, для которой бы выполнялось условие задачи. Правильное решение показывало пустой набор записей. Поэтому ошибочное увеличение числа кораблей не работало с правильным критерием (>=3), так как запрос выдавал битву Surigao Strait, хотя в ней реально принимало участие 2 корабля из USA. А вот условие >3 опять давало пустой набор.

В проверочной базе для блокировки решения с неисключенными дубликатами для одной битвы было два набора по 3 и более корабля разных стран. При этом в одном наборе головной корабль присутствовал в обеих таблицах (Outcomes и Ships). Для этого набора рассматриваемым запросом ошибочно считалось 4 корабля, а для второго правильно — 3. Поэтому условие в предикате HAVING – > 3 и давало только одну битву, разрешая самым неожиданным образом проблему с дубликатами.

Мир полон неожиданностей; чем больше делается ошибок, тем больше вероятность совпадения результатов.

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

Bookmark and Share
Страницы: 1 2 3 4
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
https://exchangesumo.com/obmen/WEXRUB-SBRFRUB/
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100