Упражнение (-2) (подсказки и решения)

При решении этой задачи часто допускается весьма характерная и, на наш взгляд, принципиальная ошибка, которая имеется и в решении 3.6.2. Однако, чтобы не приводить здесь окончательный вариант и позволить читателю самому его построить, объясним суть ошибки, упростив формулировку задачи:

Определить год, когда на воду было спущено максимальное количество кораблей. Вывод: количество кораблей, год

Определить распределение количества кораблей по годам можно так:

SELECT launched [year], COUNT(*) cnt
FROM Ships
GROUP BY launched;

В нашем случае ([year]) квадратные скобки применяются во избежание путаницы с функцией year(), которая возвращает год из аргумента, представленного типом дата-время.

Теперь нам нужно оставить из всех строк, возвращаемых этим запросом, только те, у которых количество (cnt) максимально, то есть:

cnt >= ALL(SELECT COUNT(*) cnt
           FROM Ships
           GROUP BY launched
          )

Окончательно получим:

Решение 4.19.1

SELECT *
FROM (SELECT launched [year], COUNT(*) cnt
      FROM Ships
      GROUP BY launched
     ) x
WHERE cnt >= ALL(SELECT COUNT(*) cnt
                 FROM Ships
                 GROUP BY launched
                );

Тем не менее, здесь кроется ошибка. Эта ошибка не связана с формальным построением решения. Оно не вызывает сомнения. Как это обычно происходит при решении задач на сайте, ошибка заключается в неточном учете особенностей модели предметной области, а именно, ее ограничений. В данном случае допускается, что в базе данных могут быть корабли с неизвестным годом спуска на воду, так как, во-первых, столбец launched допускает NULL-значения и, во-вторых, для головного корабля, который присутствует только в таблице Outcomes, год спуска на воду неизвестен.

Строить корабли — это вам не кроликов разводить. Корабли строятся годами. Поэтому, если для ряда кораблей год спуска на воду неизвестен (NULL), то велика вероятность того, что число таких кораблей будет больше, чем количество кораблей, спущенных на воду в любом реальном году. Особенность группировки заключается в том (и это оговорено в стандарте), что NULL-значения трактуются как равные. Следовательно, все корабли с неизвестным годом спуска на воду, будут просуммированы с годом NULL. Полагаем, что результат не должен включать такую строку по той причине, что неизвестный год не означает один и тот же. С этим можно, конечно, поспорить. Однако все споры сведутся к допустимости использования специфического значения NULL в реляционной модели. Дискуссии по этому поводу ведутся со времен создания этой модели Коддом Е.Ф., которому и принадлежит идея NULL-значения. Однако, насколько нам известно, достойной альтернативы предложено не было.

Возвращаясь к нашей задаче, мы, в знак безграничного уважения к Кодду, внесем в решение следующее изменение:

Решение 4.19.2

SELECT * FROM (SELECT launched [year], COUNT(*) cnt
               FROM  Ships
               WHERE launched IS NOT NULL
               GROUP BY launched
               ) x
WHERE cnt >= ALL(SELECT COUNT(*) cnt
                 FROM  Ships
                 WHERE launched IS NOT NULL
                 GROUP BY launched
                 );

В подзапросе предложения WHERE проверку на NULL-значения можно не выполнять, если использовать вместо функции COUNT(*) функцию COUNT(launched), поскольку в этом случае будут подсчитаны только корабли с известным годом спуска на воду:

WHERE cnt >= ALL(SELECT COUNT(launched) cnt
                 FROM Ships
                 GROUP BY launched
                 )

Для всех же кораблей с неизвестным годом спуска на воду будет получена строка со значением 0, так как если в наборе нет ни одной записи, то функция COUNT возвращает именно это значение. Последнее не должно нас смущать, поскольку количество кораблей в основном запросе больше нуля, если есть хотя бы один корабль с известным годом спуска на воду. Аналогичным образом можно поступить и в основном запросе, что позволит получить более краткую форму решения:

Решение 4.19.3

SELECT * FROM (SELECT launched [year], COUNT(launched) cnt
               FROM Ships
               GROUP BY launched
               ) x
WHERE cnt >= ALL(SELECT COUNT(launched) cnt
                 FROM Ships
                 GROUP BY launched
                 );

Справедливости ради следует отметить, что стоимость решения 4.19.3 по его плану выполнения в SQL Server 2000 будет незначительно уступать (в третьей значащей цифре) стоимости решения 4.19.2.

Вернуться к обсуждению упражнения (-2)

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