Упражнение 56

Решение 3.12.1

SELECT aa.class, SUM(aa.sunks) Sunks   
FROM (  
-- 1  
      SELECT c.class, COUNT(a.ship) sunks   
      FROM Outcomes a 
          INNER JOIN Ships b ON a.ship = b.name 
          INNER JOIN Classes c on b.class = c.class  
      WHERE a.result = 'sunk'  
      GROUP BY c.class  
      UNION  
-- 2  
       SELECT c.class, COUNT(a.ship)  
       FROM Outcomes a 
           INNER JOIN Classes c ON a.ship = c.class  
       WHERE a.result = 'sunk'  
       GROUP by c.class  
       UNION  
-- 3  
       SELECT c.class, 0   
       FROM Classes c  
) aa  
GROUP BY aa.class;

В подзапросе предложения FROM объединяются три таблицы:

1. Класс и число потопленных кораблей, которые есть в таблице Ships.

2. Класс и число потопленных головных кораблей класса. Здесь уже есть «излишество», а именно: нет необходимости использовать группировку и соответственно функцию COUNT, так как у класса может быть только один головной корабль, да и потоплен корабль может быть только однажды.

3. Каждый класс с нулевым количеством потопленных кораблей. Это позволяет учесть те классы, которые не имеют потопленных кораблей и, следовательно, не попадают в предыдущие два набора записей.

Объединение с использованием UNION устраняет дубликаты, что, по мнению автора решения, позволяет корректно обработать ситуацию, когда потопленный головной корабль присутствует в таблице Ships. Наконец, выполняется группировка по классам с суммированием. При этом последний набор не дает вклада в окончательный результат, если в классе имеются потопленные корабли, что правильно.

Однако ошибка кроется в том, что объединяются двухатрибутные кортежи {класс, число потопленных кораблей}. Поэтому если в некотором классе (опять «Бисмарк») имеется два потопленных корабля, причем головной корабль отсутствует в Ships, то объединяться будут два одинаковых кортежа

Тогда после устранения дубликатов мы получаем один потопленный корабль вместо двух.

Но это еще не все. Даже головной корабль мы можем посчитать дважды, если он присутствует в Ships. Это справедливо для случая, когда есть и другие корабли этого класса, потопленные в сражениях. Давайте опять возьмем для примера «Бисмарк», только теперь он присутствует также в таблице Ships. Пусть есть и еще один потопленный корабль (естественно, не головной) этого класса. Тогда первый набор даст:

а второй:

В результате мы получим

хотя на самом деле корабля всего два.

Вот еще одно решение задачи, в котором не используется объединение, но содержится другая ошибка:

Решение 3.12.2

SELECT classes.class, COUNT(ship) sunked
FROM Classes 
     FULL JOIN Ships ON classes.class = ships.class 
     LEFT JOIN (SELECT ship
                FROM Outcomes
                WHERE result = 'sunk'
                ) s ON s.ship = ships.name 
                    OR s.ship = classes.class
GROUP BY classes.class;

Первое (полное) соединение:

Classes FULL JOIN Ships ON classes.class = ships.class

будет содержать все возможные классы кораблей. Заметим, что здесь можно было ограничиться левым (LEFT) соединением, так как согласно связи между таблицами в Ships не может быть корабля, класс которого отсутствует в таблице Classes.

Далее выполняется левое соединение с потопленными кораблями из таблицы Outcomes по следующему предикату (множество s содержит все потопленные корабли):

ON s.ship = ships.name OR s.ship = classes.class

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

В чем же здесь ошибка? Ошибка заключается как раз в предикате последнего соединения. Пусть в таблице Ships имеются корабли некоторого класса (например, два корабля с именами А и В класса Class_1), которые не были потоплены. И пусть в Outcomes имеется потопленный головной корабль этого же класса. Тогда соединяться будут следующие два отношения (приводим здесь только важные для анализа атрибуты):

и

Ship (отношение s) Class_1

по предикату

s.ship = classes.class

В результате будет получено отношение, содержащее корабли, которые не были потоплены, но учитываются этим решением:

Можно сказать иначе, а именно, потопленный головной корабль учтен здесь столько раз, сколько кораблей этого класса имеется в таблице Ships (как потопленных, так и нет). Так или иначе, но COUNT(ship) = 2, что неверно, так как потоплен был всего один корабль.

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

Решение 3.12.3

SELECT class, 
      SUM(CASE WHEN result = 'sunk' THEN 1 ELSE 0 END)
FROM (SELECT c.class, sh.name, o.ship, o.result
      FROM Classes c 
           LEFT JOIN Ships sh ON c.class = sh.class 
           LEFT JOIN Outcomes o ON ISNULL(sh.name, c.class) = o.ship
      ) t
GROUP BY class;

Оставим без внимания подсчет количества потопленных кораблей. Ошибка не в этом, а в том, как формировался набор записей для этого подсчета.

Итак, левое соединение таблицы Classes с таблицей Ships по столбцу class позволяет нам учесть также классы, которые не имеют кораблей в таблице Ships. Это правильно, так как нам следует выводить данный класс со значением 0 в качестве количества потопленных кораблей, если таковые отсутствуют.

Далее выполняется левое соединение с таблицей Outcomes, которая и содержит информацию о результатах сражений. Предикат соединения использует специфическую для SQL Server функцию ISNULL, которая возвращает первый аргумент, если он не является NULL-значением, и второй — в противном случае:

ISNULL(sh.name, c.class) = o.ship

То есть имя корабля в таблице Outcomes сравнивается с именем корабля, полученным из таблицы Ships или именем класса, если имя корабля содержит NULL-значение. Это значение возникает в предыдущем соединении тогда, когда класс не имеет кораблей в Ships; и только в этом случае!

Опять рассмотрим случай, когда в Ships имеется корабль A некоторого класса Class_1, а в таблице Outcomes содержится как этот корабль, так и головной корабль класса Class_1 (имя совпадает с именем класса). Пусть оба эти корабля были потоплены. Тогда первое соединение даст:

Второе же соединение будет искать в таблице Outcomes строки, удовлетворяющие вышеприведенному предикату. Такой строкой будет всего одна:

так как будет выполнено сравнение только по имени корабля (А), но не по классу!

Решение 3.12.4

SELECT class, SUM(sunks) sunks
FROM (SELECT cl.class, 1 sunks
      FROM Classes cl 
          LEFT JOIN Ships sh ON cl.class = sh.class 
          INNER JOIN Outcomes ou ON ou.ship = sh.name 
                                 OR ou.ship = cl.class
      WHERE result='sunk'
      UNION
      SELECT DISTINCT class, 0 sunks
      FROM classes
      ) tab
GROUP BY class;

В первом из объединяемых запросов предложения FROM для каждого потопленного корабля в результирующий набор попадает строка:

Класс           1

В основном запросе эти «единички» суммируются. Но поскольку UNION устраняет дубликаты, то для любого количества потопленных кораблей в классе мы в конечном итоге получим либо 1 (из этого первого запроса), либо 0 из второго, который учитывает классы, не имеющие потопленных кораблей.

Если же выполнить объединение при помощи UNION ALL, то придем к решению 3.12.2, которое содержит аналогичную ошибку.

Решение 3.12.5

SELECT t1.class, COUNT(*) AS cnt   
FROM (SELECT a.class, b.name   
      FROM Classes a  
           LEFT JOIN -- соединение с Ships без головных кораблей:  
           Ships b ON a.class = b.class 
                  AND a.class <> b.name 
           ) AS t1 
    JOIN -- соединение либо по классу для головных кораблей, либо по имени  
    Outcomes t2 ON t1.class = t2.ship 
                OR t1.name = t2.ship  
WHERE result = 'sunk'  
GROUP BY t1.class  
-- Выбираются классы кораблей, которые не попали в первый запрос.  
-- Это классы, не имеющие потопленных кораблей.  
UNION  
SELECT class, '0'   
FROM Classes   
WHERE class NOT IN (SELECT DISTINCT t1.class   
FROM (SELECT a.class, b.name   
      FROM Classes a 
          LEFT JOIN Ships b ON a.class = b.class 
                           AND a.class <> b.name  
      ) AS t1 
    JOIN Outcomes t2 ON t1.class = t2.ship 
                     OR t1.name = t2.ship  
WHERE result = 'sunk'  
);

Решение 3.12.6

SELECT d.class class, (SELECT COUNT(f.result)  
                       FROM (SELECT c.result  
                       FROM Ships b 
                           LEFT OUTER JOIN Outcomes c ON (b.name = c.ship)  
                       WHERE c.result = 'sunk' 
                         AND d.class = b.class  
                       UNION ALL  
                       SELECT c.result  
                       FROM Outcomes c  
                       WHERE c.result = 'sunk' 
                          AND d.class = c.ship  
                            ) f  
                       ) Sunks  
FROM Classes d;

Для анализа двух последних решений — 3.12.5 и 3.12.6 — рассмотрим следующие варианты данных. В таблице Ships (показаны только принципиальные для анализа столбцы):

В таблице Outcomes:

Тогда согласно предикату соединения в решении 3.12.5

ON t1.class = t2.ship 
   OR t1.name = t2.ship

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

Решение задачи 3.12.6 даст на этих данных правильный результат, поскольку первый запрос в объединении (соединение по имени корабля) даст только ship1_class_1, а второй — только class_1. Однако это решение тоже не верно, что будет продемонстрировано на другом варианте данных.

В таблице Ships

В таблице Outcomes:

Первый запрос в объединении даст нам оба потопленных корабля класса class_2, а второй — головной корабль этого класса. Поскольку при объединении используется UNION ALL, то головной корабль дважды будет учтен в результирующем наборе, в результате чего мы опять получаем 3 вместо 2. Косметическое исправление UNION ALL на UNION не делает решение верным, так как здесь возникает та же ошибка, что и в решении 3.12.4, когда для любого количества потопленных кораблей класса в результат попадает только 1.

Кстати, решение 3.12.5 на этих данных тоже дает значение 3, но по другой причине, описанной выше.

ПиР

Решить упражнение на SQL-EX.RU