Упражнение 32

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

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

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

Решение 3.14.1

SELECT DISTINCT Classes.country,
    (SELECT AVG( pen.p )
    FROM (SELECT (c1.bore*c1.bore*c1.bore)/2 AS p
        FROM Classes AS c1, Ships AS s1
        WHERE c1.class = s1.class 
            AND c1.country = Classes.country 
            AND c1.bore IS NOT NULL
        UNION ALL
        SELECT (c2.bore*c2.bore*c2.bore)/2
        FROM Classes AS c2, Outcomes
        WHERE c2.country = Classes.country 
            AND c2.class = Outcomes.ship 
            AND c2.bore IS NOT NULL 
            AND Outcomes.ship NOT IN (SELECT ss.name
                                      FROM Ships AS ss
                                     )
        ) AS pen
    WHERE pen.p IS NOT NULL
    ) AS weight
FROM Classes
WHERE Classes.country IS NOT NULL;

Запрос интересен тем, что в нем не используется группировка, а среднее значение по стране определяется с помощью коррелирующего подзапроса, выполняемого для каждой страны из таблицы Classes. Кроме того, он выполнен в полном соответствии со стандартом. Можно сразу сделать замечание относительно эффективности выполнения этого запроса, так как если у страны несколько классов кораблей (что не является для нас большой неожиданностью), то фактически подзапрос будет выполняться для каждого класса, что явно излишне. Появляющиеся при этом дубликаты записей устраняются при помощи DISTINCT, что тоже скажется на производительности. Но нас интересует другой вопрос, а именно, почему этот запрос неверен. Чтобы это понять, давайте рассмотрим его по частям.

Начнем с подзапроса, в котором объединяются (UNION ALL) два запроса:

(1)

SELECT (c1.bore*c1.bore*c1.bore)/2 AS p
FROM Classes AS c1, Ships AS s1
WHERE c1.class = s1.class 
  AND c1.country = Classes.country 
  AND c1.bore IS NOT NULL

и

(2)

SELECT (c2.bore*c2.bore*c2.bore)/2
FROM Classes AS c2, Outcomes
WHERE c2.country = Classes.country 
    AND c2.class=Outcomes.ship 
    AND c2.bore IS NOT NULL 
    AND Outcomes.ship NOT IN (SELECT ss.name
                              FROM Ships AS ss
                             )

В запросе (1) вычисляется вес снарядов кораблей из таблицы Ships для страны, передаваемой из внешнего запроса (коррелирующий подзапрос). Условие c1.bore IS NOT NULL, на наш взгляд, совершенно излишне, так как даже если и есть классы с неизвестным калибром, такие значения автоматически будут исключены при вычислении среднего значения с помощью функции AVG. Но это не ошибка в решении задачи.

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

Далее объединение с помощью UNION ALL позволяет сохранить все дубликаты веса, что необходимо, так как, по крайней мере, корабли одного класса имеют снаряды одного калибра (веса).

Во внешнем запросе вычисляется среднее значение по стране, отфильтровывая случай, когда калибр неизвестен для всех кораблей некоторой страны (WHERE pen.p IS NOT NULL). Это объясняется тем, что если AVG применяется к пустому набору записей, то результат вычисления будет NULL.

Наконец, в основном запросе выводим требуемые по условиям задачи данные.

Вы уже нашли ошибку? Если нет, то нам помогут знания предметной области. Что представляет собой таблица Outcomes? Здесь хранятся данные об участии кораблей в сражениях. А корабль, если он не был потоплен, может принимать участие в нескольких сражениях. Таким образом, мы потенциально учитываем головной корабль несколько раз. Если же рассуждать формально, то первичный ключ на этой таблице {корабль, сражение} допускает появление одного и того же корабля неоднократно.

При этом мы не можем вместо UNION ALL использовать UNION по описанным выше причинам, но, тем не менее, исправить теперь этот запрос вам будет несложно.

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

Вот еще один запрос, кстати говоря, опубликованный на форуме сайта:

Решение 3.14.2

SELECT Country, AVG(Wght) Wght, ISNULL(AVG(Wght),0)
FROM (SELECT DISTINCT Country, name, ship,
    CASE WHEN (Outcomes.Ship IS NULL 
               AND Ships.Class IS NULL)
    THEN NULL
    ELSE POWER(Bore,3)/2
    END Wght
    FROM Outcomes 
        FULL OUTER JOIN Ships ON Outcomes.Ship = Ships.Name 
        RIGHT OUTER JOIN Classes ON Outcomes.Ship = Classes.Class 
                      OR Ships.Class = Classes.Class
    ) s
GROUP BY Country;

Это решение выполнено иначе. Здесь есть группировка по странам, полное внешнее соединение и использование нестандартных функций (SQL Server):

ISNULL(var, sub) — возвращает вместо var значение sub, если var есть NULL;

POWER(var, N) — возвращает значение var в степени N (возведение в степень).

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

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

Решение 3.14.3

SELECT country, AVG(bore*bore*bore/2)
FROM Ships s 
    FULL JOIN Outcomes o ON s.name = o.ship 
    LEFT JOIN Classes c ON c.class = ISNULL(s.class, o.ship)
WHERE c.class IS NOT NULL
GROUP BY country;

Ошибка заключается в соединении

Ships s FULL JOIN Outcomes o ON s.name = o.ship

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

Решение 3.14.4

SELECT country, AVG(bore*bore*bore/2)
FROM (SELECT country, bore, name
    FROM Classes 
        LEFT JOIN Ships ON Ships.class = Classes.class
    UNION
    SELECT DISTINCT country, bore, ship
    FROM Classes C 
        LEFT JOIN Outcomes O ON O.ship = C.class
    WHERE NOT EXISTS(SELECT name
                     FROM SHips
                     WHERE name = O.ship) 
                        AND NOT (ship IS NULL)
    ) ABC
GROUP BY country;

Рассмотрим ситуацию, когда есть класс (допустим class_1, калибр орудий 12), но нет кораблей этого класса в базе данных, и есть класс (class_2, калибр орудий 14), у которого в базе данных есть только головной корабль, упомянутый в таблице Outcomes. При этом оба класса принадлежат одной стране, скажем, country_1. Тогда первый запрос в объединении, если добавить для наглядности столбец class, в предложении FROM даст:

в то время как из второго запроса получим правильный результат:

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

Решение 3.14.5

SELECT Country, AVG(bore*bore*bore)/2
From (SELECT c.country, bore
    FROM Classes C, Ships S
    WHERE S.class = C.Class 
        AND NOT bore IS NULL
    UNION ALL
    SELECT country, bore
    FROM Classes C, OutComes O
    WHERE O.Ship = C.Class 
        AND NOT EXISTS (SELECT 1
                        FROM Ships S
                        Where s.Name = O.Ship
                        ) 
        AND Not bore IS NULL
    GROUP BY country, bore
    ) AS Q1
GROUP BY country;

В подзапросе объединяются двухатрибутные отношения {страна, калибр}. Предикат второго запроса:

NOT EXISTS (SELECT 1
            FROM Ships S
            Where s.Name = O.Ship
            )

исключает возможность неоднократного учета корабля, если он присутствует в обеих таблицах — Ships и Outcomes, — что как бы оправдывает использование для объединения оператора UNION ALL. Дубликаты же в таблице Outcomes, которые могут появиться в случае участия корабля в нескольких сражениях, устраняются, по мнению автора решения, группировкой по стране и калибру.

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

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