Упражнение (-2)

Решение 3.6.1. Вот характерное решение для начинающих:

SELECT country, MAX(x), MIN(launched)
FROM (SELECT country, COUNT(*) x , launched
    FROM Ships b, Classes a
    WHERE a.class = b.class
    GROUP BY country, launched
    ) s
WHERE launched = ANY(SELECT MIN(launched)
                    FROM Ships bb, Classes aa
                    WHERE bb.class = aa.class
                    GROUP BY country, launched
                    )
GROUP BY country;

Подзапрос в предложении FROM определяет количество строк для каждой уникальной пары значений {страна, год спуска на воду}. На языке предметной области это означает, что определяется число кораблей, спущенных на воду каждой страной в каждом году. Пусть результатом выполнения подзапроса s будет следующая таблица:

Далее (в предложении WHERE) отбираются только те строки, у которых год спуска на воду совпадает хотя бы с одним годом, определяемым следующим подзапросом:

SELECT MIN(launched)
FROM Ships bb, Classes aa
WHERE bb.class = aa.class
GROUP BY country, launched;

Что же дает этот подзапрос? Все годы из каждой строки приведенной выше таблицы, так как группировка опять таки выполняется по стране и году. В результате используемый подзапрос никак не ограничивает выборку, и, следовательно, является ненужным. Видимо, подразумевалось удовлетворение условия задачи по минимальному году. Однако речь идет о минимальном годе из тех лет, когда на воду было спущено максимальное количество кораблей данной страны.

Чтобы исправить эту ошибку, недостаточно убрать из группировки столбец launched:

SELECT MIN(launched)
FROM Ships aa, Classes bb
WHERE bb.class = aa.class
GROUP BY country;

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

Тогда строки, удовлетворяющие предикату, будут выглядеть так:

Таким образом, мы уже потеряли правильные строки для Японии и США. Следует обратить внимание еще и на то, что мы получили строку для Японии:

SELECT country, MAX(x), MIN(launched)
…
GROUP BY country

Тут выполняется группировка по стране с определением двух агрегатных показателей — максимума по количеству кораблей и минимума по году. Использование в данном случае группировки ошибочно. Действительно, нужные нам строки уже находятся в таблице, получаемой в предложении FROM (например, строка {Gt.Britain, 6, 1916}). Зачем же здесь группировка, когда требуется лишь критерий, который поставит фильтр, отсекающий лишние строки. В результате же группировки образуется одна строка, содержащая статистические показатели для всей группы. При этом максимум и минимум в общем случае достигаются в разных строках группы. Это хорошо видно из таблицы на примере кораблей США, когда минимальному году отвечает далеко не максимальное значение (x=1), а максимальное значение (x=3) достигается совсем в другом году (1941). Поэтому такая группировка может дать правильный результат (в смысле условия задачи) только в том случае, если все значения x для страны совпадают.

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

Чтобы все же связать год со страной, можно использовать коррелирующий подзапрос в предложении WHERE (AND aa.country = s.country):

WHERE launched = ANY(SELECT MIN(launched)
                    FROM Ships bb, Classes aa
                    WHERE aa.class = bb.class
                        AND aa.country = s.country
                    GROUP BY country
                    )

Это правильно, но пока ничего не меняет, разве что исключит неправильную строку:

Чтобы двигаться дальше, нужно вычислять минимальный год среди лет с максимальным количеством кораблей для каждой страны. Здесь первичным является максимальное количество кораблей. Ведь выбирая лишь минимальный год, мы можем потерять правильные строки. Поэтому в предикате нужно оценивать не год, а количество кораблей:

WHERE x >= ALL(SELECT count(launched)
                FROM Ships bb, Classes aa
                WHERE bb.class = aa.class
                     AND s.country=aa.country
                GROUP BY country, launched
                )

Обратите внимание на предикат >= ALL — (больше или равно), который дает нам максимальное значение x. Перепишем весь запрос с учетом сказанного о группировке в основном запросе:

Решение 3.6.2

SELECT country, x, launched
FROM (SELECT country, COUNT(*) x , launched
        FROM Ships b, Classes a
        WHERE a.class = b.class
        GROUP BY country, launched
      ) s
WHERE x >= ALL(SELECT COUNT(launched)
                FROM Ships bb, Classes aa
                WHERE bb.class = aa.class 
                    AND s.country=aa.country
                GROUP BY country, launched
                );

Все? Не совсем. Если максимум для какой-нибудь страны достигается в разные годы, то мы получим по строке на каждый год. Нам же по условиям задачи требуется в таком случае вывести минимальный год. Как было отмечено выше, это как раз тот самый случай, когда группировка приемлема по смыслу (все значения x для страны одинаковы — максимальны):

Решение 3.6.3. Использование для решения задачи соединения вместо коррелирующего подзапроса.

SELECT a.country, a.numShips AS Qty, MIN(launched) AS Year
FROM (SELECT country, COUNT(*) AS numShips, launched
        FROM Classes INNER JOIN
        Ships ON Classes.class = Ships.class
        GROUP BY country, launched
        ) AS a 
    INNER JOIN
        (SELECT a.country, MAX(a.numShips) AS Qty
         FROM (SELECT country, COUNT(*) AS numShips
                FROM Classes INNER JOIN
                Ships ON Classes.class = Ships.class
                GROUP BY country, launched
                ) AS a
         GROUP BY country) AS b
    ON a.country = b.country 
        AND a.numShips = b.Qty
GROUP BY a.country, a.numShips;

В предложении FROM выполняется внутреннее эквисоединение по стране и числу кораблей двух подзапросов. В первом подзапросе определяется страна и число кораблей, спущенных на воду в этой стране в каждом году. Второй подзапрос содержит аналогичный запрос в предложении FROM, выбирая из него только ту пару {страна, число кораблей}, которая содержит максимальное число кораблей, спущенных на воду в течение одного года.

В результате этого соединения пара {страна, максимальное число кораблей} дополняется годом, в котором такое число кораблей было спущено на воду. Наконец, выполняется аналогичная решению 3.6.2 группировка, чтобы определить минимальный год, если максимум достигался несколько раз для одной и той же страны.

Решение 3.6.4.

Использование предложения HAVING.

SELECT country, QTY, MIN(launched)
FROM (SELECT country, launched, COUNT(name) QTY
    FROM Classes c JOIN
    Ships s ON c.class = s.class
    GROUP BY country,launched
    HAVING COUNT(name) = (SELECT MAX(qty)
                        FROM (SELECT country,launched,COUNT(name) qty
                        FROM Classes c1 
                            JOIN Ships s1 ON c1.class = s1.class
                        WHERE country = c.country
                        GROUP BY country,launched
                            )
    )T
GROUP BY t.qty, t.country;

В подзапросе предложения FROM сначала определяются строки {страна, год, число кораблей}. Затем в предикате предложения HAVING отбираются только те строки, у которых число кораблей совпадает с максимальным числом кораблей данной страны. Обратите внимание на то, что подзапрос в этом предикате является коррелирующим:

WHERE country = c.country

Именно поэтому MAX(qty) относится именно к стране из основного запроса, а не представляет собой глобальный максимум, что было бы в противном случае. Наконец, находится минимальный год для каждого сочетания {страна, максимальное число кораблей}.

Можно переходить к следующей задаче? Нет, еще рано. Все рассмотренные варианты решений содержат одну и ту же ошибку, которую автор предлагает вам найти самостоятельно.

Не получается? Тогда загляните в ПиР.

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