лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Вывести номер модели и цену принтера, а также максимальную и минимальную цену на принтеры того же типа. Попытаемся адаптировать для этой задачи рассмотренные выше подходы. В решении 1 подзапросы следует сделать коррелирующими : Решение 1M 1. SELECT model, price, type, 2. ( SELECT MIN ( price ) FROM Printer P1 WHERE P1.type=P.type ) min_price, 3. ( SELECT MAX ( price ) FROM Printer P1 WHERE P1.type=P.type ) max_price 4. FROM printer P; В решении 2 мы можем воспользоваться нестандартным соединением CROSS APPLY (SQL Server), использующим коррелирующий подзапрос в предложении FROM. Решение 2M 1. SELECT model, price, P.type, min_price, max_price 2. FROM Printer P CROSS APPLY 3. ( SELECT MIN ( price ) min_price, MAX ( price ) max_price 4. FROM Printer P1 5. WHERE P1.type=P.type ) X; Для наглядности в решения 1M и 2M добавлен столбец type. Сортировка и NULL- значения Если столбец, по которому выполняется сортировка, допускает NULL-значения, то при использовании SQL Server следует иметь в виду, что при сортировке по возрастанию NULL-значения будут идти в начале списка, а при сортировке по убыванию - в конце. Поскольку в доступных в учебнике базах NULL- значения отсутствуют в представленных данных (коль скоро они согласованы с данными в открытых базах, используемых на сайте sql-ex.ru), я создал копию таблицы PC с именем PC_, в которую добавил строку, содержащую NULL в столбце price: 1. INSERT INTO PC_ 2. VALUES ( 13 , 2112 , 600 , 64 , 8 , '24x' , NULL ) ; Следует отметить, что это не противоречит схеме данных Теперь вы сами можете убедиться в сказанном, выполнив пару приведенных ниже запросов. 1. SELECT * FROM PC_ ORDER BY price; 1. SELECT * FROM PC_ ORDER BY price DESC ; Почему это важно? Дело в том, что при поиске экстремальных значений часто используют метод, основанный на сортировке. Рассмотрим, например, такую задачу. Найти модели ПК, имеющих минимальную цену. Иногда эту задачу решают следующим образом: 1. SELECT TOP 1 WITH ties model 2. FROM PC_ 3. ORDER BY price; Конструкция WITH TIES используется для того, чтобы вывести все модели с наименьшей ценой, если их окажется несколько. Однако в результате мы получим модель 2112, цена которой неизвестна, в то время как должны получить модели 1232 и 1260, имеющих действительно минимальные цены. Мы их и получим, если исключим из рассмотрения модели с неизвестными ценами: 1. SELECT TOP 1 WITH ties model 2. FROM PC_ 3. WHERE price IS NOT NULL 4. ORDER BY price; Но тут появляется еще одна проблема, связанная с дубликатами. Поскольку есть два ПК модели 1232 с минимальной ценой, то обе они будут выводиться в результирующем наборе. DISTINCT без указания в списке столбцов предложения SELECT тех, по которым выполняется сортировка, использовать мы не можем, о чем и сообщает ошибка, если мы попытаемся это сделать 1. SELECT DISTINCT TOP 1 WITH ties model 2. FROM PC_ 3. WHERE price IS NOT NULL 4. ORDER BY price; (ORDER BY items must appear in the select list if SELECT DISTINCT is specified.) Чтобы получить решение в требуемом виде, мы можем добавить price в список выводимых столбцов, а потом использовать полученный запрос в качестве подзапроса. Итак, 1. SELECT model FROM ( 2. SELECT DISTINCT TOP 1 WITH ties model, price 3. FROM PC_ 4. WHERE price IS NOT NULL 5. ORDER BY price 6. ) X; Примечание: При использовании агрегатных функций проблемы с NULL-значениями не возникает, т.к. они автоматически исключаются из рассмотрения. Хотя при этом тоже придется использовать подзапрос: 1. SELECT DISTINCT model FROM PC_ 2. WHERE price = ( SELECT MIN ( price ) FROM PC_ ) ; Заметим также, что это стандартное решение будет работать под любыми СУБД, т.к. не использует специфических особенностей диалекта. А как, кстати, обстоят дела с использованием метода на основе сортировки в других СУБД? В MySQL мы можем использовать DISTINCT без обязательного указания в списке SELECT столбцов, по которым выполняется сортировка. Однако здесь нет аналога конструкции WITH TIES , чтобы решить задачу максимально просто. Поэтому в методе, основанном на сортировке, нам придется использовать подзапрос, чтобы вывести все модели с минимальной ценой: 1. SELECT DISTINCT model FROM PC_ 2. WHERE price = ( SELECT price FROM PC_ WHERE price IS NOT NULL ORDER BY price LIMIT 1 ) ; Такое же решение будет работать и в PostgreSQL, однако он имеет одну особенность, о которой полезно знать. А именно, при сортировке можно указать, где будут выводиться NULL-значения - в начале или в конце результирующего набора. Нам для решения задачи требуется, чтобы NULL выводились в конце отсортированного списка. Тогда не придется выполнять лишнюю операцию по отфильтровыванию NULL-значений: 1. SELECT DISTINCT model FROM PC_ 2. WHERE price = ( SELECT price FROM PC_ ORDER BY price nulls last LIMIT 1 ) ; Кстати, при сортировке по возрастанию NULL-значения в PostgreSQL идут в конце результирующего рабора. Поэтому конструкция NULLS LAST, которую мы использовали выше, можно опустить при решении нашей задачи: 1. SELECT DISTINCT model FROM PC_ 2. WHERE price = ( SELECT price FROM PC_ ORDER BY price LIMIT 1 ) ; Для того чтобы NULL-значения шли в начале результирующего набора при выполнении сортировки, нужно написать NULLS FIRST. К слову, мы можем смоделировать в MySQL использование конструкций NULLS FIRST/LAST. Для этого воспользуемся тем фактом, что значения логического типа в этой СУБД представляют собой TINYINT(1). Конкретно это означает, что 0 соответствует истинностному значению FALSE (ложь), а ненулевое значение эквивалентно TRUE (истина). При этом логическое выражение, оцениваемое как TRUE будет представлено единицей, т.е. 1. SELECT a IS NULL AS a, b IS NULL AS b FROM ( SELECT NULL AS a, 1 AS b ) x; даст нам a b 1 0 Учитывая то, что 0 при сортировке по возрастанию идет раньше, чем 1, мы можем решение для PostgreSQL адаптировать для MySQL: 1. SELECT DISTINCT model FROM PC_ 2. WHERE price = ( SELECT price FROM PC_ ORDER BY price IS NULL , price LIMIT 1 ) ; Oracle, как и PostgreSQL, при сортировке по возрастанию помещает NULL- значения в конец результирующего набора. Здесь также имеют место конструкции NULLS FIRST/LAST, но отсутствует аналог LIMIT/TOP N для ограничения количества выводимых строк. Чтобы смоделировать в Oracle использованный выше подход к решению задачи, можно воспользоваться встроенной функцией ROWNUM. Эта функция нумерует строки, но делает это она после выполнения предложений FROM и WHERE, т.е. перед предложениями SELECT и ORDER BY. Такое поведение иллюстрирует результат следующего запроса: 1. SELECT code, model,price, ROWNUM rn FROM PC_ ORDER BY price; CODE MODEL PRICE RN 10 1260 350 10 9 1232 350 9 8 1232 350 8 7 1232 400 7 3 1233 600 3 1 1232 600 1 5 1121 850 5 2 1121 850 2 4 1121 850 4 6 1233 950 6 12 1233 970 12 11 1233 980 11 13 2112 NULL 13 Как видно, номер строки не соответствует порядку сортировки. Нетрудно убедиться в том, что нумерация выполнена в соответствии со столбцом code. Это объясняется тем, что оптимизатор использует индекс по этому столбцу при выполнении запроса. Итак, чтобы найти минимальную цену на основе сортировки, придется использовать подзапрос: 1. SELECT price FROM ( 2. SELECT model,price FROM PC_ ORDER BY price 3. ) X 4. WHERE ROWNUM = 1 ; Теперь, как и в случае MySQL и PostgreSQL, будем использовать этот запрос для получения моделей, которые продаются по цене, найденной с его помощью: 1. SELECT DISTINCT model FROM PC_ WHERE price = 2. ( SELECT price FROM ( 3. SELECT model,price FROM PC_ ORDER BY price 4. ) X 5. WHERE ROWNUM = 1 6. ) ; Как говорил Соломон, от многой мудрости много скорби, и умножающий знание умножает печаль. Используйте стандартные решения, сказал бы я. :-) В заключение не могу не сказать о способе, использующем ранжирующие функции Идея решения cостоит в ранжировании (функция RANK) строк по возрастанию цены и выборке (уникальных) строк, для которых ранг равен 1. Чтобы запрос работал под всеми СУБД, которые поддерживают оконные функции, этот алгоритм можно записать следующим образом: 1. SELECT DISTINCT model FROM ( 2. SELECT model, Rank () OVER ( ORDER BY price ) rn FROM PC_ 3. WHERE price IS NOT NULL 4. ) X WHERE rn = 1 ; Тот факт, что при сортировке по возрастанию NULL-значения идут в начале (SQL Server) можно использовать в "полезных целях". Пусть нам требуется вывести список рейсов, в котором рейсы из Ростова должны идти первыми, а затем остальные в алфавитном порядке города отправления. Здесь весьма кстати пригодится функция NULLIF(town_from,'Rostov') , которая будет возвращать NULL, если городом отправления является 'Rostov'. Задачу решает следующий запрос: 1. SELECT trip_no, town_from, town_to 2. FROM Trip 3. ORDER BY NULLIF ( town_from, 'Rostov' ) , trip_no; Агрегатная функция от агрегатной функции Давайте рассмотрим такую задачу: Найти максимальное значение среди средних цен ПК, посчитанных для каждого производителя отдельно. Посчитать средние значения стоимости по производителям труда не составляет: 1. SELECT AVG ( price ) avg_price 2. FROM Product P JOIN PC ON P.model = PC.model 3. GROUP BY maker; Однако стандарт запрещает использовать подзапрос в качестве аргумента агрегатной функции, т.е. нельзя решить задачу следующим способом: 1. SELECT MAX ( 2. SELECT AVG ( price ) avg_price 3. FROM Product P JOIN PC ON P.model = PC.model 4. GROUP BY maker 5. ) ; В подобных случаях используется подзапрос в предложении FROM: 1. SELECT MAX ( avg_price ) 2. FROM ( SELECT AVG ( price ) avg_price 3. FROM Product P JOIN PC ON P.model = PC.model 4. GROUP BY maker 5. ) X; С помощью новых возможностей языка – оконных функций - эту задачу можно решить без подзапроса: 1. SELECT DISTINCT MAX ( AVG ( price )) OVER () max_avg_price 2. FROM Product P JOIN PC ON P.model = PC.model 3. GROUP BY maker; Обратите внимание, что оконные функции допускают использование агрегатной функции в качестве аргумента. Ключевое слово DISTINCT необходимо здесь, поскольку максимальное значение, подсчитанное по всему набору средних значений, будет «приписано» каждому производителю. Стандарт также запрещает использовать агрегатную функцию как аргумент другой агрегатной функции. Т.е. мы не можем решить нашу задачу следующим образом: 1. SELECT MAX ( AVG ( price )) max_avg_price 2. FROM Product P JOIN PC ON P.model = PC.model 3. GROUP BY maker; Но не бывает правил без исключений. Как ни странно, но в Oracle подобные конструкции работают, и вышеприведенный запрос даст результат: MAX_AVG_PRICE 850 Чтобы убедиться в этом, зайдите на страницу задач обучающего этапа на сайте sql-ex.ru, выберите Oracle в списке СУБД и выполните запрос с флажком "Без проверки". Кстати говоря, решение с использованием оконной функции также будет работать в Oracle. Могу предположить, что решение без оконной функции фактически её и использует, неявно подразумевая предложение OVER(). Наверняка, вам встретятся решения подобных задач на основе сортировки с ограничением на число строк результирующего набора. Однако такие решения не являются легитимными с точки зрения стандарта языка и, как следствие, имеют различный синтаксис в разных реализациях. В качестве примера приведу решения нашей задачи в диалектах SQL Server и MySQL. SQL Server 1. SELECT TOP 1 AVG ( price ) avg_price 2. FROM Product P JOIN PC ON P.model = PC.model 3. GROUP BY maker 4. ORDER BY avg_price DESC ; MySQL 1. SELECT AVG ( price ) avg_price 2. FROM Product P JOIN PC ON P.model = PC.model 3. GROUP BY maker 4. ORDER BY avg_price DESC 5. LIMIT 1 ; Оба этих решения берут только первую строку из отсортированного по убыванию набора средних цен. У начинающих изучать SQL зачастую вызывает проблему определение производителя, для которого достигается искомый максимум/минимум. Другими словами, требуется найти максимальную среднюю цену и производителя, средняя цена ПК которого совпадает с этой максимальной средней ценой. Нестандартными средствами эта задача решается фактически рассмотренным выше запросом: 1. SELECT TOP 1 maker, AVG ( price ) avg_price 2. FROM Product P JOIN PC ON P.model = PC.model 3. GROUP BY maker 4. ORDER BY avg_price DESC ; Использование maker в списке столбцов предложения SELECT вполне допустимо, т.к. по этому столбцу выполняется группировка. Однако тут имеется одна «ловушка». Она связана с тем, что максимум может достигаться для нескольких производителей, и в данной постановке задачи их нужно выводить всех, в то время как мы ограничиваем выборку только одной (первой) строкой. На этот случай диалект T-SQL имеет дополнительную конструкцию WITH TIES . Логически правильное решение будет иметь вид: 1. SELECT TOP 1 WITH TIES maker, AVG ( price ) avg_price 2. FROM Product P JOIN PC ON P.model = PC.model 3. GROUP BY maker 4. ORDER BY avg_price DESC ; Однако, если иметь в виду проблему переносимости кода, то следует предпочесть решение, использующее стандартные средства. Примечание: На сайте SQL-EX.RU проблема переносимости кода возникла в связи с нашим намерением реализовать упражнения для различных СУБД. Реализация потребовала бы минимальных средств, если бы тестовые решения, используемые для проверки, работали бы на всех предполагаемых СУБД без изменения своего кода. Поэтому следование стандарту может являться одним из требований тех. задания на проект. Приведем ниже несколько стандартных решений рассматриваемой задачи. 1. Использование предиката ALL в предложении WHERE 1. SELECT maker, avg_price 2. FROM ( SELECT maker, AVG ( price ) avg_price 3. FROM Product P JOIN PC ON P.model=PC.model 4. GROUP BY maker 5. ) X 6. WHERE avg_price >= ALL ( SELECT AVG ( price ) avg_price 7. FROM Product P JOIN PC ON P.model=PC.model 8. GROUP BY maker 9. ) ; На естественном языке этот запрос звучит следующим образом: «Найти производителей, средняя цена на ПК у которых не меньше, чем средние цены у КАЖДОГО из производителей ПК». 2. Использование внутреннего соединения 1. SELECT maker, avg_price 2. FROM ( SELECT maker, AVG ( price ) avg_price 3. FROM Product P JOIN PC ON P.model=PC.model 4. GROUP BY maker 5. ) X JOIN 6. ( SELECT MAX ( avg_price ) max_price 7. FROM ( SELECT maker, AVG ( price ) avg_price 8. FROM Product P JOIN PC ON P.model=PC.model 9. GROUP BY maker 10. ) X 11. ) Y ON avg_price = max_price; Здесь мы соединяем подзапрос, определяющий производителей и средние цены на их ПК, с подзапросом, в котором определяется максимальная средняя цена. Соединение выполняется по условию равенства средней цены из первого подзапроса с максимальной ценой из второго. 3. Использование предиката ALL в предложении HAVING 1. SELECT maker, AVG ( price ) avg_price 2. FROM Product P JOIN PC ON P.model=PC.model 3. GROUP BY maker 4. HAVING AVG ( price ) >= ALL ( SELECT AVG ( price ) 5. FROM Product P JOIN PC ON P.model=PC.model 6. GROUP BY maker 7. ) ; Это решение отличается от первого варианта отсутствием «лишнего» запроса, который пришлось написать лишь затем, чтобы была возможность использовать алиас avg_price в предложении WHERE (смотри порядок обработки предложений оператора SELECT ); с другой стороны, использование в предложении WHERE агрегатной функции также запрещено правилами языка. Все приведенные стандартные решения выглядят тяжеловесными, хотя и будут работать практически во всех СУБД. Эта громоздкость объясняется повторением в коде фактически одного и того же запроса. Однако общие табличные выражения – CTE , которые были введены в последних версиях стандарта, позволяют многократно ссылаться на один раз сформулированный запрос. Например, решения 1, 3 с помощью |