Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

База данных «Корабли»

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

Автор полагал, что в этой задаче лишь одна проблема — округление. Однако как-то поступило следующее решение:

Консоль

Выполнить

SELECT SUM(sum_g)/SUM(count_g)
FROM (SELECT SUM(numGuns) AS sum_g, COUNT(*) AS count_g
 FROM Classes INNER JOIN 
 Ships ON Classes.class = Ships.class
 WHERE type = 'bb'
 UNION
 SELECT SUM(numGuns) AS sum_g, COUNT(*) AS count_g
 FROM Classes INNER JOIN 
 Outcomes ON Classes.class = Outcomes.ship
 WHERE type='bb'
 ) AS a;

SELECT SUM(sum_g)/SUM(count_g) FROM (SELECT SUM(numGuns) AS sum_g, COUNT(*) AS count_g FROM Classes INNER JOIN Ships ON Classes.class = Ships.class WHERE type = 'bb' UNION SELECT SUM(numGuns) AS sum_g, COUNT(*) AS count_g FROM Classes INNER JOIN Outcomes ON Classes.class = Outcomes.ship WHERE type='bb' ) AS a;

    Схема

Богатое для анализа ошибок решение. Начнем с округления. Число орудий — целое число (по типу столбца, а не по смыслу!). Поэтому и сумма будет числом целым. При делении целых чисел в  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server всегда получается целое число. Причем результат достигается не округлением, а отбрасыванием дробной части. Выполните, например, следующий запрос

Консоль

Выполнить

SELECT 2/3;

SELECT 2/3;

    Схема

Нет схемы

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

SELECT SUM(sum_g)*1.0/SUM(count_g)

Теперь, поскольку требуется определить среднее по классам, то, во-первых, не нужно учитывать корабли, а, во-вторых, не нужно учитывать корабли из таблицы Outcomes.

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

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

Рассмотрим пример. Пусть в Ships есть 2 корабля с 11 и 8 орудиями, а в Outcomes — один корабль с 11 орудиями. Итого получаем 3 корабля и 30 орудий. Среднее 30/3 = 10. Правильно? Нет, то есть правильно, но не во всех случаях. Нам же нужно написать запрос, который будет верен на любых данных. Я вижу здесь несколько контрпримеров.

Первый контрпример. А если в Outcomes не будет головного корабля, отвечающего условиям задачи? Тогда второй подзапрос даст: кораблей — 0, число орудий — NULL. В результате вычисление среднего в рассматриваемом запросе даст

(19 + NULL)/(2+0) = NULL

Второй контрпример. Пусть головной корабль класса bb есть как в таблице Ships, так и в таблице Outcomes, то есть это один и тот же корабль. Тогда в результате мы должны получить не 30/3, что нам дает представленное решение, а 19/2.

Третий контрпример. А если в предыдущей ситуации по кораблям головной корабль дважды принимал участие в сражениях? Тогда мы получим вместо тех же 19/2 — (19 + 22)/(2+2) = 41/4.

Четвертый контрпример… Придумайте сами. Вот так и формируется проверочная база сайта. :-)

ПиР