loading..
Русский    English
23:29
листать

Упражнение 53

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

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

Консоль
Выполнить
  1. SELECT SUM(sum_g)/SUM(count_g)
  2. FROM (SELECT SUM(numGuns) AS sum_g, COUNT(*) AS count_g
  3. FROM Classes INNER JOIN
  4. Ships ON Classes.class = Ships.class
  5. WHERE type = 'bb'
  6. UNION
  7. SELECT SUM(numGuns) AS sum_g, COUNT(*) AS count_g
  8. FROM Classes INNER JOIN
  9. Outcomes ON Classes.class = Outcomes.ship
  10. WHERE type='bb'
  11. ) AS a;

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

Консоль
Выполнить
  1. SELECT 2/3;

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

  1. 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. В результате вычисление среднего в рассматриваемом запросе даст

  1. (19 + NULL)/(2+0) = NULL
вместо 19/2.

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

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

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

ПиР

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.