Одно из крупных преимуществ расширенного предложения From заключается в том, что оно дает единый стандарт для определения как внутренних и внешних объединений, так и произведений и запросов на объединение. Другим, даже еще более важным преимуществом этого предложения является то, что оно обеспечивает очень ясную и четкую спецификацию объединений трех и четырех таблиц, а также произведений и запросов на объединение. Для построения этих сложных объединений любые выражения объединения могут быть заключены в круглые скобки. Результирующее выражение, в свою очередь, можно использовать для создания других выражений объединения, как если бы оно было простой таблицей. Точно так же, как SQL позволяет с помощью круглых скобок комбинировать различные арифметические операции (+, - , * и /) и строить сложные выражения, стандарт SQL2 дает возможность создавать сложные выражения для объединений.
Чтобы привести пример многотабличного объединения, предположим, что к таблицам girls и boys добавлена новая таблица parents, которая имеет три столбца:
child Соответствует столбцу name в таблицах girls и boys type Принимает значение "father "(отец) или "mother "(мать) pname Имя родителя
Строка в таблице girls или boys может иметь две связанные строки в таблице parent, одна из которых определяет мать, а другая — отца, или может иметь только одну из этих строк, или может совсем не иметь связанных строк, если отсутствуют данные о родителях ребенка. В таблицах girls, boys и parents в совокупности содержится достаточно богатый набор данных, чтобы обеспечить несколько примеров многотабличных запросов.
Предположим, например, что вы хотите составить список всех детей и их родителей. Вот запрос, создающий такой список:
Так как это внутреннее объединение, то девочки и мальчики, не имеющие связанных строк в таблице parents, в таблицу результатов запроса не попадут. Модифицируя этот запрос в левое внешнее объединение, можно включить соответствующие этим детям строки (с расширением null) в результаты запроса:
Если по каким-либо причинам вы хотите включить в результаты запроса только несвязанные строки девочек, то для этого можно переделать данный запрос в запрос на объединение двух объединений, одного внутреннего и одного внешнего:
В качестве еще одного примера предположим, что вы опять, как в предыдущих примерах, хотите найти пары мальчик/девочка из одного и того же города, но на этот раз вы хотите еще и включить в таблицу результатов имя отца мальчика и имя матери девочки. То, что в объединениях данного примера могут присутствовать несвязанные строки, означает несколько возможных "правильных" ответов на запрос. Например, предположим, что вы хотите включить в результаты запроса все пары мальчик/девочка из одних и тех городов, даже те, в которых либо мальчик, либо девочка имеют строки, не связанные с таблицей parents. В этом запросе вам придется использовать два внешних объединения — (boys join parents) и (girls join parents) — и одно внутреннее: (boys join girls). В результате этот запрос в стандарте SQL2 будет выглядеть следующим образом:
Как видно из примера, даже запрос с тремя объединениями в соответствии со стандартом SQL2 может иметь весьма сложный вид. Однако, несмотря на эту сложность, запрос по стандарту SQL2 точно и однозначно определяет то, что должна выполнить СУБД. Нет никакой неясности в отношении порядка объединения таблиц или в отношении того, какие объединения являются внешними, а какие — внутренними. Возможность произвольного сочетания операций union и join обеспечивает гибкость предложения from. В общем, новые возможности стоят дополнительных сложностей расширенного предложения from стандарта SQL2.
33. ИТОГОВЫЕ ЗАПРОСЫ НА ЧТЕНИЕ
Многие запросы к базе данных не нуждаются в той степени детализации, которую обеспечивают SQL-запросы, рассмотренные в двух предыдущих главах. Например, во всех запросах, перечисленных ниже, требуется узнать всего одно или несколько значений, которые подытоживают информацию, содержащуюся в базе данных:
• Какова общая сумма плановых объемов продаж для всех служащих?
• Каковы наибольший и наименьший плановые объемы продаж?
• Сколько служащих перевыполнили свой план?
• Какова средняя стоимость заказа?
• Какова средняя стоимость заказа в каждом офисе?
• Сколько служащих закреплено за каждым офисом?
В SQL запросы такого типа можно создавать с помощью агрегатных функций и предложений group by и having оператора select, описанных в настоящей главе.
34. АГРЕГАТНЫЕ ФУНКЦИИ
Для подведения итогов по информации, содержащейся в базе данных, в SQL предусмотрены агрегатные (статистические) функции. Агрегатная функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец. Например, агрегатная функция avg() принимает в качестве аргумента столбец чисел и вычисляет их среднее значение. Ниже приведен запрос, в котором функция avg () используется для вычисления среднего значения в двух столбцах таблицы salesreps:
Первая агрегатная функция принимает в качестве аргументов все значения, содержащиеся в столбце quota, и вычисляет их среднее значение; вторая агрегатная функция подсчитывает среднее значение столбца sales. Результатом запроса является одна строка, представляющая итоги по информации, содержащейся в таблице salesreps.
• функция sum () вычисляет сумму всех значений, содержащихся в столбце;
• функция avg () вычисляет среднее всех значений, содержащихся в столбце;
• функция min () находит наименьшее среди всех значений, содержащихся в столбце;
• функция мах () находит наибольшее среди всех значений, содержащихся в столбце;
• функция count () подсчитывает количество значений, содержащихся в столбце;
• функция count (*) подсчитывает количество строк в таблице результатов запроса.
Аргументом агрегатной функции может быть простое имя столбца, как в предыдущем примере, или выражение, как в следующем запросе:
При выполнении этого запроса SQL создает временный столбец, содержащий значения (100 * (sales/quota)) для каждой строки таблицы salesreps, а затем вычисляет среднее значение временного столбца.
Вычисление суммы столбца (SUM)
Агрегатная функция sum() вычисляет сумму всех значений, содержащихся в столбце. При этом столбец должен иметь числовой тип данных (содержать целые числа, десятичные числа, числа с плавающей запятой или денежные величины). Результат, возвращаемый этой функцией, имеет тот же тип данных, что и столбец, однако точность результата может быть выше. Например, если применить функцию sum () к столбцу, содержащему 16-разрядные целые числа, она может вернуть в качестве результата 32-разрядное целое число.
Ниже приведен ряд примеров, в которых используется агрегатная функция SUM () :
Вычисление среднего значения столбца (AVg)
Агрегатная функция avg ()вычисляет среднее всех значений, содержащихся в столбце. Как и в случае с функцией sum (), данные, содержащиеся в столбце, должны иметь числовой тип. Поскольку функция avg () вначале суммирует все значения, содержащиеся в столбце, а затем делит сумму на число этих значений, возвращаемый ею результат может иметь не такой тип данных, как столбец. Например, если применить функцию avg () к столбцу целых чисел, результат будет либо десятичным числом, либо числом с плавающей запятой — в зависимости от используемой СУБД.
Ниже приведено несколько примеров использования агрегатной функции AVG () :
Вычисление экстремумов (MIN и МАХ)
Агрегатные функции min() и мах() позволяют найти соответственно наименьшее и наибольшее значения в столбце. При этом столбец может содержать числовые или строковые значения либо значения даты/времени. Результат, возвращаемый этими функциями, имеет точно такой же тип данных, что и сам столбец.
В случае применения агрегатных функций min () и мах() к числовым данным числа сравниваются по арифметическим правилам (большие отрицательные числа меньше, чем маленькие отрицательные числа, которые меньше нуля; нуль меньше любого положительного числа). Сравнение дат происходит последовательно (более ранние значения дат считаются меньшими, чем более поздние). Сравнение интервалов времени выполняется на основании их продолжительности (более короткие интервалы времени меньше, чем более длинные).
При использовании функций min() и мах() со строковыми данными результат сравнения двух строк зависит от используемой таблицы кодировки. На персональных компьютерах и мини-компьютерах, где используется таблица кодировки ASCII, в последовательности сортировки цифры идут перед буквами, а все прописные буквы идут перед строчными. На мэйнфреймах компании IBM, где используется таблица кодировки EBCDIC, строчные символы расположены перед прописными, а цифры следуют за буквами.
Отличия в последовательностях сортировки приводят к тому, что один и тот же запрос, содержащий предложение order by, на различных системах может привести к различным результатам.
Использование символов национальных алфавитов (например, кириллицы) может вызвать дополнительные проблемы. В некоторых СУБД для каждого языка используется свой алгоритм сортировки таких символов. В других СУБД такие символы сортируются в соответствии с кодом символа. Для решения этой проблемы в стандарт SQL2 включено условие поддержки национальных наборов символов, пользовательских наборов символов и альтернативных последовательностей сортировки. К сожалению, в настоящее время эти возможности поддерживаются не во всех СУБД. Если в приложении используются символы национальных алфавитов, необходимо проверить, как конкретная СУБД обрабатывает их.
ВЫЧИСЛЕНИЕ КОЛИЧЕСТВА ЗНАЧЕНИЙ В СТОЛБЦЕ (COUNT)
Агрегатная функция count () подсчитывает количество значений в столбце. При этом тип данных столбца может быть любым. Функция count () всегда возвращает целое число независимо от типа данных столбца. Ниже приведен ряд запросов, в которых используется эта функция:
Мысленно трудно представить запрос вроде "подсчитать, сколько стоимостей заказов" или "подсчитать, сколько номеров заказов"; гораздо проще представить запрос "подсчитать, сколько заказов". Поэтому в SQL была введена специальная агрегатная функция count (*), которая подсчитывает строки, а не значения данных. Ниже приведен предыдущий запрос, переписанный с использованием этой функции:
Если использовать count (*) в качестве функции подсчета строк, то запрос становится более удобочитаемым. На практике для подсчета строк всегда применяется функция count (*), а не count ().
АГРЕГАТНЫЕ ФУНКЦИИ В СПИСКЕ ВОЗВРАЩАЕМЫХ СТОЛБЦОВ
Назначение простого запроса с использованием агрегатной функции можно понять достаточно легко. Однако если в список возвращаемых столбцов входит несколько агрегатных функций или если аргументом агрегатной функции является сложное выражение, понять запрос становится значительно сложнее.
