Версия для печати

На главную страницу

Характерные ошибки при решении упражнений. Задача 50

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

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

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

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

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) два запроса:

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 по описанным выше причинам, но, тем не менее, исправить теперь этот запрос вам будет несложно.

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

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

Перейти к решению задачи #50

На главную страницу

  Версия для печати