Упражнение 57

Эта задача в чем-то подобна задаче 56, то есть здесь можно допускать те же ошибки в подсчете потопленных кораблей. Однако ситуация усугубляется еще и определением общего числа кораблей в классе.

Решение 3.13.1

SELECT c.class, SUM(outc)
FROM Classes c 
    LEFT JOIN Ships s ON c.class = s.class 
    LEFT JOIN (SELECT ship, 1 outc
                FROM Outcomes
                WHERE result = 'sunk'
              ) o ON s.name = o.ship 
                  OR c.class = o.ship
GROUP BY c.class
HAVING COUNT(*) > 2 
    AND SUM(outc) IS NOT NULL;

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

Далее выполняется еще одно левое соединение с набором потопленных кораблей по предикату

ON s.name = o.ship OR c.class = o.ship

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

Наконец, выполняется группировка по классам с отбором по числу кораблей (строк) класса и подсчитывается сумма потопленных кораблей (единиц в столбце outs). Автор решения предлагает рациональный способ вычислить в одной группировке и общее число кораблей, и количество потопленных кораблей в классе. Предикат:

SUM(outc) IS NOT NULL

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

Те, кто внимательно читал анализ предыдущих задач, уже догадались, в чем дело. Правильно, проблема в предикате второго соединения. Однако не только в этом.

Рассмотрим следующий вариант данных. Пусть для некоторого класса class_N в таблице Ships имеется два корабля: ship_1 и ship_2. Кроме того, в таблице Outcomes есть потопленный корабль ship_1 и оставшийся на плаву головной — class_N.

Первое соединение даст:

Выполняем второе соединение:

В результате этот класс вообще не попадет в результирующий набор, так как не будет выполнено условие COUNT(*) > 2, хотя на самом деле корабля 3. Причина ошибки заключается в том, что мы выполняем соединение только по потопленным кораблям, одновременно подсчитывая общее число кораблей.

Давайте теперь немного изменим данные в примере. Пусть и головной корабль class_N тоже потоплен. Тогда результатом соединения будет:

Последние две строки будут получены в результате соединения со строкой потопленного головного корабля, так как предикат c.class = o.ship дает «истину». Таким образом, мы вместо одной строки для головного корабля получаем по строке на каждый корабль класса из таблицы Ships. Итого, вместо

имеем

Вы можете попытаться исправить это решение или использовать другой подход на базе внутреннего соединения и объединения.

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

Решение 3.13.2

SELECT class, SUM(sunk)
FROM (SELECT class, COUNT(*) AS sunk
    FROM Ships a 
        JOIN Outcomes b ON a.name = b.ship 
                        AND a.class <> b.ship
    WHERE result = 'sunk'
    GROUP BY class
    UNION ALL
    SELECT class, '1'
    FROM Classes a 
        JOIN Outcomes b ON a.class = b.ship
    WHERE result = 'sunk'
    UNION ALL
    SELECT class, '0'
    FROM classes
) t
-- где классы с числом кораблей больше 2:
WHERE class IN (SELECT t1.class
                FROM (SELECT a.class
                    FROM Classes a 
                        LEFT JOIN Ships b ON a.class = b.class
                    ) t1 
                    LEFT JOIN (SELECT DISTINCT ship
                                FROM Outcomes
                                WHERE ship NOT IN (SELECT name
                                                    FROM Ships
                                                   )
                              ) t2 ON t1.class = t2.ship
                GROUP BY t1.class
                HAVING COUNT(*) > 2
                )
GROUP BY class
HAVING SUM(sunk) > 0;

Решение 3.13.3

SELECT a.class AS cls, a.num_sunks AS sunk
FROM (SELECT c.class, COUNT (o.ship) AS num_sunks
    FROM Outcomes o 
        LEFT JOIN Ships s ON o.ship = s.name 
        LEFT JOIN Classes c ON s.class = c.class
    WHERE o.result = 'sunk'
    GROUP BY c.class
    ) a,
    (SELECT c1.class
    FROM Ships s1, Classes c1
    WHERE s1.class = c1.class
    GROUP BY c1.class
    HAVING COUNT(name) >= 3
    ) B
WHERE a.class = b.class;

Решение 3.13.4

SELECT class, COUNT(result) AS sunk
FROM (SELECT class, result, name
    FROM Ships 
        LEFT JOIN Outcomes ON ship=name 
                AND class IS NOT NULL 
                AND result = 'sunk'
     ) T
GROUP BY class
HAVING COUNT(class) > 2 
   AND COUNT(result) > 0;

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

ПиР

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