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

Подсказки и решения

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

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

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

Консоль

Выполнить

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

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
 );

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

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