лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
name a5%% abc abc 5% cde C2H5OH C25OH C54OH 8. Поиск строки, которая содержит четную цифру: name 5c2e C2H5OH C25OH C54OH 9. Поиск строки, которая содержит подряд идущие четную и нечетную цифры: name C25OH Использование значения NULL в условиях поиска Предикат: 1. IS [ NOT ] NULL позволяет проверить отсутствие (наличие) значения в полях таблицы. Использование в этих случаях обычных предикатов сравнения может привести к неверным результатам, так как сравнение со значением NULL дает результат UNKNOWN (неизвестно). Так, если требуется найти записи в таблице PC, для которых в столбце price отсутствует значение (например, при поиске ошибок ввода), можно воспользоваться следующим оператором: 1. SELECT * 2. FROM PC 3. WHERE price IS NULL ; Характерной ошибкой является написание предиката в виде: 1. WHERE price = NULL Этому предикату не соответствует ни одной строки, поэтому результирующий набор записей будет пуст, даже если имеются изделия с неизвестной ценой. Это происходит потому, что сравнение с NULL-значением согласно предикату сравнения оценивается как UNKNOWN. А строка попадает в результирующий набор только в том случае, если предикат в предложении WHERE есть TRUE. Это же справедливо и для предиката в предложении HAVING. Аналогичной, но не такой очевидной ошибкой является сравнение с NULL в предложении CASE (см. пункт 5.10 ). Чтобы продемонстрировать эту ошибку, рассмотрим такую задачу: «Определить год спуска на воду кораблей из таблицы Outcomes. Если последний неизвестен, указать 1900». Поскольку год спуска на воду (launched) находится в таблице Ships, нужно выполнить левое соединение (см. пункт 5.6 ): 1. SELECT ship, launched 2. FROM Outcomes o LEFT JOIN 3. Ships s ON o.ship = s.name; Для кораблей, отсутствующих в Ships, столбец launched будет содержать NULL-значение. Теперь попробуем заменить это значение значением 1900 с помощью оператора CASE (см. пункт 5.10 ): 1. SELECT ship, CASE launched 2. WHEN NULL 3. THEN 1900 4. ELSE launched 5. END 'year' 6. FROM Outcomes o LEFT JOIN 7. Ships s ON o.ship=s.name; Однако ничего не изменилось. Почему? Потому что использованный оператор CASE эквивалентен следующему: 1. CASE 2. WHEN launched = NULL 3. THEN 1900 4. ELSE launched 5. END 'year' А здесь мы получаем сравнение с NULL-значением, и в результате — UNKNOWN, что приводит к использованию ветви ELSE, и все остается, как и было. Правильным будет следующее написание: 1. CASE 2. WHEN launched IS NULL THEN 1900 3. ELSE launched 4. END 'year' то есть проверка именно на присутствие NULL-значения. Получение итоговых значений Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций. Стандартом предусмотрены следующие агрегатные функции: название описание COUNT(*) Возвращает количество строк источника записей COUNT Возвращает количество значений в указанном столбце SUM Возвращает сумму значений в указанном столбце AVG Возвращает среднее значение в указанном столбце MIN Возвращает минимальное значение в указанном столбце MAX Возвращает максимальное значение в указанном столбце Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к данным любого типа, в то время как SUM и AVG используются только для данных числового типа. Разница между функцией COUNT(*) и COUNT(имя столбца | выражение) состоит в том, что вторая (как и остальные агрегатные функции) при подсчете не учитывает NULL-значения. Пример 5.5.1 Найти минимальную и максимальную цену на персональные компьютеры: 1. SELECT MIN ( price ) AS Min_price, 2. MAX ( price ) AS Max_price 3. FROM PC; Результатом будет единственная строка, содержащая агрегатные значения: Min_price Max_price 350.0 980.0 Пример 5.5.2 Найти имеющееся в наличии количество компьютеров, выпущенных производителем А 1. SELECT COUNT ( * ) AS Qty 2. FROM PC 3. WHERE model IN ( SELECT model 4. FROM Product 5. WHERE maker = 'A' 6. ) ; В результате получим Qty 8 Пример 5.5.3 Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product номер модели - столбец model - является первичным ключом и, следовательно, не допускает повторений): 1. SELECT COUNT ( model ) AS Qty_model 2. FROM Product 3. WHERE maker = 'A' ; Qty_model 7 Пример 5.5.4 Найти количество имеющихся различных моделей ПК, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице РС (то есть имеющихся в продаже). Для того чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно применить параметр DISTINCT. Другой параметр - ALL - задействуется по умолчанию и предполагает подсчет всех возвращаемых (не NULL) значений в столбце. Оператор 1. SELECT COUNT ( DISTINCT model ) AS Qty 2. FROM PC 3. WHERE model IN ( SELECT model 4. FROM Product 5. WHERE maker = 'A' 6. ) ; даст следующий результат Qty 2 Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY, синтаксически следующего после предложения WHERE. Предложение GROUP BY Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM). Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT, должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY. В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После чего к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, то есть при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу. Если при наличии предложения GROUP BY, в предложении SELECT отсутствуют агрегатные функции, то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе. Рассмотрим простой пример: 1. SELECT model, COUNT ( model ) AS Qty_model, 2. AVG ( price ) AS Avg_price 3. FROM PC 4. GROUP BY model; В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средняя цена для каждой группы. Результатом выполнения запроса будет следующая таблица model Qty_model Avg_price 1121 3 850 1232 4 425 1233 3 843,333333333333 1260 1 350 Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений {модель, дата}. Существует несколько определенных правил выполнения агрегатных функций. Если в результате выполнения запроса не получено ни одной строки (или ни одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других функций — NULL. Данное свойство может дать не всегда очевидный результат. Рассмотрим, например, такой запрос: 1. SELECT 1 a WHERE 2. EXISTS ( SELECT MAX ( price ) 3. FROM PC 4. WHERE price< 0 ) ; Подзапрос в предикате EXISTS возвращает одну строку с NULL в качестве значения столбца. Поэтому, несмотря на то, что ПК с отрицательными ценами нет в базе данных, запрос в примере вернет 1. Аргумент агрегатной функции не может сам содержать агрегатные функции (функция от функции). То есть в простом запросе (без подзапросов) нельзя, скажем, получить максимум средних значений Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений. Если при выполнении функции SUM будет получен результат, превышающий максимально возможное значение для используемого типа данных, возникает ошибка. Итак, агрегатные функции, включенные в предложение SELECT запроса, не содержащего предложения GROUP BY, исполняются над всеми результирующими строками этого запроса. Если же запрос содержит предложение GROUP BY, каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY, составляют группу, и агрегатные функции выполняются для каждой группы отдельно. Предложение HAVING Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций. Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM, а из групп таких строк. Поэтому такая проверка не может содержаться в предложении WHERE. Пример 5.5.5 Получить количество ПК и среднюю цену для каждой модели, средняя цена которой менее $800 1. SELECT model, COUNT ( model ) AS Qty_model, 2. AVG ( price ) AS Avg_price 3. FROM PC 4. GROUP BY model 5. HAVING AVG ( price ) < 800 ; В результате выполнения запроса получим: model Qty_model Avg_price 1232 4 425 1260 1 350 Заметим, что в предложении HAVING нельзя использовать псевдоним (Avg_price), используемый для именования значений агрегатной функции в предложении SELECT. Дело в том, что предложение SELECT, формирующее выходной набор запроса, выполняется предпоследним перед предложением ORDER BY. Ниже приведен порядок обработки предложений в операторе SELECT: 1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. ORDER BY Этот порядок не соответствует синтаксическому порядку общего представления оператора SELECT, который ближе к естественному языку: 1. SELECT [ DISTINCT | ALL ]{ * 2. | [ <выражение для столбца> [[ AS ] <псевдоним> ]] [ ,… ]} 3. FROM <имя таблицы> [[ AS ] <псевдоним> ] [ ,… ] 4. [ WHERE <предикат> ] 5. [[ GROUP BY <список столбцов> ] 6. [ HAVING <условие на агрегатные значения> ] ] 7. [ ORDER BY <список столбцов> ] Следует отметить, что предложение HAVING может использоваться и без предложения GROUP BY. При отсутствии предложения GROUP BY агрегатные функции применяются ко всему выходному набору строк запроса, т.е. в результате мы получим всего одну строку, если выходной набор не пуст. Таким образом, если условие на агрегатные значения в предложение HAVING будет истинным, то эта строка будет выводиться, в противном случае мы не получим ни одной строки. Рассмотрим такой пример. Пример 5.5.6 Найти максимальную, минимальную и среднюю цену на персональные компьютеры. Решение этой задачи дает следующий запрос: 1. SELECT MIN ( price ) AS min_price, 2. MAX ( price ) AS max_price, AVG ( price ) avg_price 3. FROM PC; результатам которого будет min_price max_price avg_price 350.00 980.00 675.00 Если же мы добавим в условие ограничение, скажем, на среднюю цену: Найти максимальную, минимальную и среднюю цену на персональные компьютеры при условии, что средняя цена не превышает $600: 1. SELECT MIN ( price ) AS min_price, 2. MAX ( price ) AS max_price, AVG ( price ) avg_price 3. FROM PC 4. HAVING AVG ( price ) <= 600 ; то в результате получим пустой результирующий набор, т.к. 675.00 > 600. Получение итоговых данных с помощью оператора ROLLUP Посчитаем сумму прихода на каждый из пунктов по таблице Income. Это несложно сделать при помощи запроса 1. SELECT point, SUM ( inc ) Qty 2. FROM Income GROUP BY point; Пусть наряду с этим нам требуется вывести сумму по всем пунктам, т.е. результат должен выглядеть так: point Qty 1 66500.00 2 13000.00 3 3100.00 ALL 82600.00 Для решения подобной задачи в операторе SELECT имеется спецификация ROLLUP. С её помощью достичь требуемого результата не составляет труда: 1. SELECT CASE WHEN point IS NULL THEN 'ALL' ELSE CAST ( point AS varchar ) END point, 2. SUM ( inc ) Qty 3. FROM Income GROUP BY point WITH ROLLUP; Поскольку значения столбца должны быть одного типа, номер пункта приёма приводится к символьному типу. Последний запрос можно переписать в иной (стандартной) синтаксической форме: 1. SELECT CASE WHEN point IS NULL THEN 'ALL' ELSE CAST ( point AS varchar ) END point, 2. SUM ( inc ) Qty 3. FROM Income GROUP BY ROLLUP ( point ) ; Вместо ROLLUP в нашем запросе можно также использовать оператор CUBE: 1. SELECT CASE WHEN point IS NULL THEN 'ALL' ELSE CAST ( point AS varchar ) END point, 2. SUM ( inc ) Qty 3. FROM Income 4. GROUP BY point WITH CUBE; Подробно о различиях между этими двумя операторами вы можете почитать в статье Бена Ричардсона Если СУБД не поддерживает конструкцию ROLLUP, можно использовать либо UNION , либо внешнее соединение ( FULL JOIN ), что позволяет объединить два запроса в один. Ниже приводятся эти решения. UNION 1. SELECT CAST ( point AS varchar ) point, SUM ( inc ) Qty 2. FROM Income GROUP BY point 3. UNION ALL 4. SELECT 'ALL' , SUM ( inc ) 5. FROM Income; FULL JOIN 1. SELECT coalesce ( X.point,Y.point ) point, coalesce ( X.Qty,Y.Qty ) Qty FROM 2. ( SELECT CAST ( point AS varchar ) point, SUM ( inc ) Qty 3. FROM Income GROUP BY point ) X 4. FULL JOIN 5. ( SELECT 'ALL' point, SUM ( inc ) Qty 6. FROM Income ) Y ON 1 = 2 ; В последнем решении следует обратить внимание на то, что соединение выполняется по заведомо ложному предикату, т.к. нам нужны строки из обеих таблиц, которые бы не конкатенировались друг с другом. Комбинация детализированных и агрегированных данных Пусть, помимо модели и цены принтера, требуется еще вывести максимальную и минимальную цену по всему множеству принтеров. Для новичка подобная задача зачастую представляет определенную сложность. Эта сложность состоит в дилемме: группировка или агрегация по всему множеству. Если использовать группировку, то максимум и минимум будут получены не по всей выборке, а для каждого подмножества, определяемого группировкой (в данном примере для каждой группы с одинаковой комбинацией значений {модель, цена}). Поскольку эта комбинация уникальна в таблице Printer учебной базы данных, то мы получим 3 совпадающих значения цены: 1. SELECT model, price, MIN ( price ) min_price, MAX ( price ) max_price 2. FROM printer 3. GROUP BY model, price; Если же не использовать группировку, то мы можем получить только минимальное и максимальное значение, поскольку стандартный синтаксис запрещает (ввиду неоднозначности трактовки результата) использовать наряду с агрегатами детализированные данные, по которым не выполняется группировка: 1. SELECT MIN ( price ) min_price, MAX ( price ) max_price 2. FROM printer; Проблема разрешается довольно просто, причем не единственным способом. Так можно использовать подзапросы в предложении SELECT для каждого агрегатного значения. Это возможно, поскольку подзапрос вернет одно значение, а не набор: Решение 1 1. SELECT model, price, 2. ( SELECT MIN ( price ) FROM Printer ) min_price, 3. ( SELECT MAX ( price ) FROM Printer ) max_price 4. FROM printer; Более эффективным приемом будет использование подзапроса для вычисления агрегатов в предложении FROM, наряду с декартовым произведением . Бояться декартового произведения в этом случае не нужно, т.к. подзапрос вернет только одну строку, которая и будет соединяться с каждой строкой детализированных данных. Решение 2 1. SELECT model, price, min_price, max_price 2. FROM printer CROSS JOIN 3. ( SELECT MIN ( price ) min_price, MAX ( price ) max_price 4. FROM printer ) X; Почему мы утверждаем, что второй запрос будет эффективней? Дело в том, что в решении 1 подзапрос будет вычисляться дважды, а не один раз, как это делается во втором решении. Кроме того, если оптимизатор недостаточно "умный", подзапросы в первом решении будут вычисляться для каждой строки детализированных данных. Проверьте планы выполнения для своей СУБД. Рассмотрим теперь задачу, когда агрегат зависит от текущей строки детализированных данных, например, такую. |