Агрегатные функции системы компоновки данных. Применение агрегатных функций и вложенных запросов в операторе выбора Внешние объединения SQL

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

Например, сгруппируем отношение R1 по значению столбца Дисциплина. Мы получим 4 группы, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Оценка.

Это делается с помощью агрегатных функций. Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в таблице 5.7.

Таблица 5.7. Агрегатные функции


R1
	ФИО	Дисциплина	Оценка
Группа 1	Петров Ф. И.	Базы данных
	Сидоров К. А.	Базы данных
	Миронов А. В.	Базы данных
	Степанова К. Е.	Базы данных
	Крылова Т. С.	Базы данных
	Владимиров В. А.	Базы данных
Группа 2	Сидоров К. А.	Теория информации
	Степанова К. Е.	Теория информации
	Крылова Т. С.	Теория информации
	Миронов А. В.	Теория информации	Null
Группа 3	Трофимов П. А.	Сети и телекоммуникации
	Иванова Е. А.	Сети и телекоммуникации
	Уткина Н. В.	Сети и телекоммуникации
Группа 4	Владимиров В. А.	Английский язык
	Трофимов П. А.	Английский язык
	Иванова Е. А.	Английский язык
	Петров Ф. И.	Английский язык	i

Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.

Например, можно вычислить количество студентов, сдававших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю «Дисциплина» и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.

SELECT R1.Дисциплина. СОUNТ(*)

GROUP BY R1 Дисциплина

Результат:

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

SELECT R1.Дисциплина. COUNT(*)

FROM R1 WHERE R1.

Оценка IS NOT NULL

GROUP BY Rl.Дисциплина

Получим результат:

В этом случае строка со студентом


Миронов А, В.	Теория информации	Null

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

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

Обратившись снова к базе данных «Сессия» (таблицы Rl, R2, R3), найдем количество успешно сданных экзаменов:

WHERE Оценка> 2:

Это, конечно, отличается от выбора поля, поскольку всегда возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Аргументом агрегатных функций могут быть отдельные столбцы таблиц. Но для того, чтобы вычислить, например, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с именем столбца. Вычислим количество различных оценок, полученных по каждой дисциплине:

SELECT Rl.Дисциплина.

COUNT(DISTINCT R1.Оценка)

WHERE R1.Оценка IS NOT NULL

GROUP BY Rl.Дисциплина

Результат:

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

SELECT R2.Группа. R1.Дисциплина. COUNT(*), АVР(Оценка)

WHERE Rl.ФИО = R2.ФИО AND

Rl.Оценка IS NOT NULL AND

Rl.Оценка> 2

GROUP BY R2.Группа. Rl.Дисциплина

Результат:

Мы не можем использовать агрегатные функции в предложении WHERE, потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции - в терминах групп строк.

Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении условия обработки сформированных групп HAVING. В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп.

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

SELECT R2.Группа

WHERE Rl.ФИО = R2.ФИО AND

Rl.Оценка = 2

GROUP BY R2.Группа. R1.Дисциплина

HAVING count(*)> 1

В дальнейшем в качестве примера будем работать не с БД «Сессия», а с БД «Банк», состоящей из одной таблицы F, в которой хранится отношение F, содержащее информацию о счетах в филиалах некоторого банка:

F = ;

Q = (Филиал, Город);

поскольку на этой базе можно ярче проиллюстрировать работу с агрегатными функциями и группировкой.

Например, предположим, что мы хотим найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY, однако, позволит поместить их все в одну команду:

SELECT Филиал, SUM

GROUP BY Филиал:

GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и функция SUM применяется отдельно для каждой такой группы, то есть суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, как и результат работы агрегатной функции. Поэтому мы можем совместить в одном запросе агрегат и поле. Вы можете также использовать GROUP BY с несколькими полями.

Предположим, что мы хотели бы увидеть только те суммарные значения остатков на счетах, которые превышают $5000. Чтобы увидеть суммарные остатки свыше $5000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же как предложение WHERE делает это для индивидуальных строк.

Правильной командой будет следующая:

SELECT Филиал, SUM(Остаток)

GROUP BY Филиал

HAVING SUM(Остаток) > 5000;

Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT, где используется GROUP BY. Они должны иметь одно значение на группу вывода.

Следующая команда будет запрещена:

SELECT Филиал.SUM(Остаток)

FROM F GROUP BY Филиал

HAVING ДатаОткрытия = 27/12/1999;

Поле ДатаОткрытия не может быть использовано в предложении HAVING, потому что оно может иметь больше чем одно значение на группу вывода. Чтобы избежать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:

SELECT Филиал,SUM(Остаток)

WHERE ДатаОткрытия = "27/12/1999"

GROUP BY Филиал;

Смысл данного запроса следующий: найти сумму остатков по каждому филиалу счетов, открытых 27 декабря 1999 года.

Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции - наиболее общие, но и поля, выбранные с помощью GROUP BY, также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Урюпинске:

SELECT Филиал.SUМ(Остаток)

WHERE F.Филиал = Q.Филиал

GROUP BY Филиал

HAVING Филиал IN ("Санкт-Петербург". "Псков". "Урюпинск");

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть TRUE. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.

Вложенные запросы SQL

Теперь вернемся к БД «Сессия» и рассмотрим на ее примере использование вложенных запросов.

С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст.

В сочетании с другими возможностями оператора выбора, такими как группировка, подзапрос представляет собой мощное средство для достижения нужного результата. В части FROM оператора SELECT допустимо применять синонимы к именам таблицы, если при формировании запроса нам требуется более чем один экземпляр некоторого отношения. Синонимы задаются с использованием ключевого слова AS, которое может быть вообще опущено. Поэтому часть FROM может выглядеть следующим образом:

FROM Rl AS A, Rl AS В

FROM Rl A. Rl В:

оба выражения эквивалентны и рассматриваются как применения оператора SELECT к двум экземплярам таблицы R1.

Например, покажем, как выглядят на SQL некоторые запросы к БД «Сессия»:

Список тех, кто сдал все положенные экзамены.

WHERE Оценка> 2

HAVING COUNT(*) = (SELECT COUNT(*)

WHERE R2.Группа=R3.Группа AND ФИОа.ФИО)

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

Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SЕLЕСТФИО

WHERE R2.Fpynna=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS

(SELECT ФИО FROM Rl WHERE ФИО=а.ФИО AND Дисциплина = "БД")

Предикат EXISTS (SubQuery) истинен, когда подзапрос SubQuery не пуст, то есть содержит хотя бы один кортеж, в противном случае предикат EXISTS ложен.

Предикат NOT EXISTS обратно - истинен только тогда, когда подзапрос SubQuery пуст.

Обратите внимание, каким образом NOT EXISTS с вложенным запросом позволяет обойтись без операции разности отношений. Например, формулировка запроса со словом «все» может быть выполнена как бы с двойным отрицанием. Рассмотрим пример базы, которая моделирует поставку отдельных деталей отдельными поставщиками, она представлена одним отношением SP «Поставщики-детали» со схемой

SP (Номер_поставщика. номер_детали) Р (номер_детали. наименование)

Вот каким образом формулируется ответ на запрос: «Найти поставщиков, которые поставляют все детали».

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА FROM SP SP1 WHERE NOT EXISTS

(SELECT номер_детали

FROM P WHERE NOT EXISTS

(SELECT * FROM SP SP2

WHERE SР2.номер_поставщика=SР1.номер_поставщика AND

sр2.номер_детали = Р.номер_детали)):

Фактически мы переформулировали этот запрос так: «Найти поставщиков таких, что не существует детали, которую бы они не поставляли». Следует отметить, что этот запрос может быть реализован и через агрегатные функции с подзапросом:

SELECT DISTINCT Номер_поставщика

GROUP BY Номер_поставщика

HAVING CounKDISTINCT номер_детали) =

(SELECT Count(номер_детали)

В стандарте SQL92 операторы сравнения расширены до многократных сравнений с использованием ключевых слов ANY и ALL. Это расширение используется при сравнении значения определенного столбца со столбцом данных, возвращаемым вложенным запросом.

Ключевое слово ANY, поставленное в любом предикате сравнения, означает, что предикат будет истинен, если хотя бы для одного значения из подзапроса предикат сравнения истинен. Ключевое слово ALL требует, чтобы предикат сравнения был бы истинен при сравнении со всеми строками подзапроса.

Например, найдем студентов, которые сдали все экзамены на оценку не ниже чем «хорошо». Работаем с той же базой «Сессия», но добавим к ней еще одно отношение R4, которое характеризует сдачу лабораторных работ в течение семестра:

R 1 = (ФИО, Дисциплина, Оценка);

R 2 = (ФИО, Группа);

R 3 = (Группы, Дисциплина)

R 4 = (ФИО, Дисциплина, Номер_лаб_раб, Оценка);

Select R1.ФИО From R1 Where 4 > = All (Select Rl.Оценка

Where R1.Фио = R11.Фио)

Рассмотрим еще один пример:

Выбрать студентов, у которых оценка по экзамену не меньше, чем хотя бы одна оценка по сданным им лабораторным работам по данной дисциплины:

Select R1.Фио

From R1 Where R1.Оценка>= ANY (Select R4.Оценка

Where Rl.Дисциплина = R4. Дисциплина AND R1.Фио = R4.Фио)

Внешние объединения SQL

Стандарт SQL2 расширил понятие условного объединения. В стандарте SQL1 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними.

В общем случае синтаксис части FROM в стандарте SQL2 выглядит следующим образом:

FROM <список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц>::= <имя_таблицы_1>

[ имя синонима таблицы_1] [ ...]

[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]

<выражение естественного объединениям:: =

<имя_таблицы_1> NATURAL { INNER | FULL | LEFT | RIGHT } JOIN <имя_таблицы_2>

<выражение перекрестного объединениям: = <имя_таблицы_1> CROSS JOIN <имя_таблицы_2>

<выражение запроса на объединением:=

<имя_таблицы_1> UNION JOIN <имя_таблицы_2>

<выражение объединениям:= <имя_таблицы_1> { INNER |

FULL | LEFT | RIGHT } JOIN {ON условие } <имя_таблицы_2>

В этих определениях INNER - означает внутреннее объединение, LEFT - левое объединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено). Ключевое слово RIGHT означает правое внешнее объединение, и в отличие от левого объединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями, Ключевое слово FULL определяет полное внешнее объединение: и левое и правое. При полном внешнем объединении выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределёнными значениями.

Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.

Рассмотрим примеры выполнения внешних объединений. Снова вернемся к БД «Сессия». Создадим отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее объединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним естественным объединением с таблицей R1, используя столбцы ФИО и Дисциплина. При этом в стандарте разрешено использовать скобочную структуру, так как результат объединения может быть одним из аргументов в части FROM оператора SELECT.

SELECT Rl.ФИО, R1.Дисциплина. Rl.Оценка

FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN Rl USING (ФИО. Дисциплина)

Результат:


ФИО	Дисциплина	Оценка
Петров Ф. И.	Базы данных
Сидоров К. А.	Базы данных	4
Миронов Л. В.	Базы данных
Степанова К. Е.	Базы данных
Крылова Т. С.	Базы данных
Владимиров В. А.	Базы данных
Петров Ф. И.	Теория информации	Null
Сидоров К. А.	Теория информации
Миронов А. В.	Теория информации	Null
Степанова К. Е.	Теория информации
Крылова Т. С.	Теория информации
Владимиров В. А.	Теория информации	Null
Петров Ф. И.	Английский язык
Сидоров К. А.	Английский язык	Null
Миронов А. В.	Английский язык	Null
Степанова К. Е.	Английский язык	Null
Крылова Т. С.	Английский язык	Null
Владимиров В. А.	Английский язык
Трофимов П. А.	Сети и телекоммуникации
Иванова Е. А.	Сети и телекоммуникации

Рассмотрим еще один пример, для этого возьмем БД «Библиотека». Она состоит из трех отношений, имена атрибутов здесь набраны латинскими буквами, что является необходимым в большинстве коммерческих СУБД.

BOOKS(ISBN, TITL. AUTOR. COAUTOR. YEARJZD, PAGES)

READER(NUM_READER. NAME_READER, ADRESS. HOOM_PHONE. WORK_PHONE. BIRTH_DAY)

EXEMPLARE (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)

Здесь таблица BOOKS описывает все книги, присутствующие в библиотеке, она имеет следующие атрибуты:

ISBN - уникальный шифр книги;
TITL - название книги;
AUTOR - фамилия автора;
COAUTOR - фамилия соавтора;
YEARIZD - год издания;
PAGES - число страниц.

Таблица READER хранит сведения обо всех читателях библиотеки, и она содержит следующие атрибуты:

NUM_READER - уникальный номер читательского билета;
NAME_READER - фамилию и инициалы читателя;
ADRESS - адрес читателя;
HOOM_PHONE - номер домашнего телефона;
WORK_PHONE - номер рабочего телефона;
BIRTH_DAY - дату рождения читателя.

Таблица EXEMPLARE содержит сведения о текущем состоянии всех экземпляров всех книг. Она включает в себя следующие столбцы:

INV - уникальный инвентарный номер экземпляра книги;
ISBN - шифр книги, который определяет, какая это книга, и ссылается на сведения из первой таблицы;
YES_NO - признак наличия или отсутствия в библиотеке данного экземпляра в текущий момент;
NUM_READER - номер читательского билета, если книга выдана читателю, и Null в противном случае;
DATE_IN - если книга у читателя, то это дата, когда она выдана читателю; a DATE_OUT - дата, когда читатель должен вернуть книгу в библиотеку.

Определим перечень книг у каждого читателя; если у читателя нет книг, то номер экземпляра книги равен NULL. Для выполнения этого поиска нам надо использовать левое внешнее объединение, то есть мы берем все строки из таблицы READER и соединяем со строками из таблицы EXEMPLARE, если во второй таблице нет строки с соответствующим номером читательского билета, то в строке результирующего отношения атрибут EXEMPLARE.INV будет иметь неопределенное значение NULL:

SELECT READER.NAME_READER, EXEMPLARE.INV

FROM READER RIGHT JOIN EXEMPLARE ON READER.NUM_READER=EXEMPLARE.NUM_READER

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

FROM (BOOKS LEFT JOIN EXEMPLARE)

LEFT JOIN (READER NATURAL JOIN EXEMPLARE)

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

Перекрестное объединение в трактовке стандарта SQL2 соответствует операции расширенного декартова произведения, то есть операции соединения двух таблиц, при которой каждая строка первой таблицы соединяется с каждой строкой второй таблицы.

Операция запроса па объединение эквивалентна операции теоретико-множественного объединения в алгебре. При этом требование эквивалентности схем исходных отношений сохраняется. Запрос на объединение выполняется по следующей схеме:

SELECT - запрос

UNION SELECT - запрос

Все запросы, участвующие в операции объединения, не должны, содержать выражений, то есть вычисляемых полей.

Например, нужно вывести список читателей, которые держат на руках книгу «Идиот» или книгу «Преступление и наказание». Воткакбудетвыглядетьзапрос:

SELECT READER. NAME_READER

FROM READER, EXEMPLARE.BOOKS

BOOKS.TITLE = "Идиот"

SELECT READER.NAME_READER

FROM READER, EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Преступлениеинаказание"

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

Запрос на объединение может объединять любое число исходных запросов.

Так, к предыдущему запросу можно добавить еще читателей, которые держат на руках книгу «Замок»:

SELECT READER. NAME_READER

FROM READER. EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND .

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Замок"

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

SELECT - запрос

Однако тот же результат можно получить простым изменением фразы WHERE первой части исходного запроса, соединив локальные условия логической операцией ИЛИ и исключив дубликаты кортежей.

SELECT DISTINCT READER.NAME_READER

FROM READER. EXEMPLARE.BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Идиот" OR

BOOKS.TITLE = "Преступление и наказание" OR

BOOKS.TITLE = "Замок"

Ни один из исходных запросов в операции UNION не должен содержать предложения упорядочения результата ORDER BY, однако результат объединения может быть упорядочен, для этого предложение ORDER BY с указанием списка столбцов упорядочения записывается после текста последнего исходного SELECT-запроса.

Важно! Если параметр функции имеет тип Строка и в нем указывается имя поля, которое содержит пробелы, то такое имя поля должно быть заключено в квадратные скобки.
Например: "[Количество Оборот]".

1. Сумма (Total) - рассчитывает сумму значений выражений, переданных ей в качестве аргумента для всех детальных записей. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.

Пример :
Сумма(Продажи.СуммаОборот)

2. Количество (Count) - рассчитывает количество значений отличных от значения NULL. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.

Синтаксис :
Количество([Различные] Параметр)

В указания получения различных значений следует перед параметром метода Количество указать Различные (Distinct).

Пример :
Количество(Продажи.Контрагент)
Количество(Различные Продажи.Контрагент)

3. Максимум (Maximum) - получает максимальное значение. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.

Пример :
Максимум(Остатки.Количество)

4. Минимум (Minimum) - получает минимальное значение. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.

Пример :
Минимум(Остатки.Количество)

5. Среднее (Average) - получает среднее значение для значений, отличных от NULL. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.

Пример :
Среднее(Остатки.Количество)

6. Массив (Array) - формирует массив, содержащий для каждой детальной записи значение параметра.

Синтаксис :
Массив([Различные] Выражение)

В качестве параметра можно использовать таблицу значений. При этом результатом работы функции будет массив, содержащий значения первой колонки таблицы значений, переданной в качестве параметра. Если выражение содержит функцию Массив, то считается, что данное выражение является агрегатным. Если указано ключевое слово Различные, то получаемый массив не будет содержать дублирующихся значений.

Пример :
Массив(Контрагент)

7. ТаблицаЗначений (ValueTable) - формирует таблицу значений, содержащую столько колонок, сколько параметров у функции. Детальные записи получаются из наборов данных, которые нужны для получения всех полей, участвующих в выражениях параметров функции.

Синтаксис :
ТаблицаЗначений([Различные] Выражение1 [КАК ИмяКолонки1][, Выражение2 [КАК ИмяКолонки2],...])

Если параметрами функции выступают поля–остатки, то в результирующую таблицу значений попадут значения для записей по уникальным комбинациям измерений из других периодов. При этом значения получаются только для полей-остатков, измерений, счетов, полей периодов и их реквизитов. Значениями остальных полей в записях из других периодов считаются равными NULL. Если выражение содержит функцию ТаблицаЗначений, то считается, что данное выражение является агрегатным. Если указано ключевое слово Различные, то в получаемой таблице значений не будет строк, содержащих одинаковые данные. После каждого параметра может располагаться необязательное ключевое слово КАК и имя, которое будет назначено колонке таблицы значений.

Пример :
ТаблицаЗначений(Различные Номенклатура, ХарактеристикаНоменклатуры КАК Характеристика)

8. Свернуть (GroupBy) - предназначена для удаления дубликатов из массива.

Синтаксис :
Свернуть(Выражение, НомераКолонок)

Параметры :

Выражение - выражение типа Массив или ТаблицаЗначений, значения элементов которого нужно свернуть;
НомераКолонок - (если выражение имеет тип ТаблицаЗначений) тип Строка. Номера или имена (через запятую) колонок таблицы значений, среди которых нужно искать дубликаты. По умолчанию – все колонки.

Пример :
Свернуть(ТаблицаЗначений(НомерТелефона, Адрес) ,"НомерТелефона");

9. ПолучитьЧасть (GetPart) - получает таблицу значений, содержащую определенные колонки из исходной таблицы значений.

Синтаксис :
ПолучитьЧасть(Выражение, НомераКолонок)

Параметры :

Выражение - тип ТаблицаЗначений. Таблица значений, из которой нужно получить колонки;
НомераКолонок - тип Строка. Номера или имена (через запятую) колонок таблицы значений, которые нужно получить.

Возвращаемое значение: ТаблицаЗначений, в которой имеются только колонки, которые указаные в параметре.

Пример :
ПолучитьЧасть(Свернуть(ТаблицаЗначений(НомерТелефона, Адрес) ,"НомерТелефона"),"НомерТелефона");

10. Упорядочить (Order) - предназначена для упорядочивания элементов массива и таблицы значений.

Синтаксис :
Упорядочить(Выражение, НомераКолонок)

Параметры :

Выражение - Массив или ТаблицаЗначений, из которой нужно получить колонки;
НомераКолонок - (если выражение имеет тип ТаблицаЗначений) номера или имена (через запятую) колонок таблицы значений, по которым нужно упорядочить. Может содержать направление упорядочивания и необходимость автоупорядочивания: Убыв/Возр + Автоупорядочивание.

Возвращаемое значение: Массив или ТаблицаЗначений, с упорядоченными элементами.

Пример :
Упорядочить(ТаблицаЗначений(НомерТелефона, Адрес, ДатаЗвонка),"ДатаЗвонка Убыв");

11. СоединитьСтроки (JoinStrings) - предназначена для объединения строк в одну строку.

Синтаксис :
СоединитьСтроки (Значение, РазделительЭлементов, РазделителиКолонок)

Параметры :

Значение - выражения, которые нужно объединить в одну строку. Если является Массивом, то в строку будут объединяться элементы массива. Если является ТаблицаЗначений, то в строку будут объединяться все колонки и строки таблицы;
РазделительЭлементов - строка, содержащая текст, который нужно использовать в качестве разделителя между элементами массива и строками таблицы значений. По умолчанию – символ перевода строк;
РазделителиКолонок - строка, содержащая текст, который нужно использовать в качестве разделителя между колонками таблицы значений. По умолчанию "; ".

Пример :
СоединитьСтроки(ТаблицаЗначений(НомерТелефона, Адрес));

12. ГрупповаяОбработка (GroupProcessing) - возвращает объект ДанныеГрупповойОбработкиКомпоновкиДанных. В объект в свойство Данные помещается в виде таблицы значений значения группировок для каждого выражения, указанного в параметре функции Выражения. В случае использования иерархической группировки каждый уровень иерархии обрабатывается отдельно. Значения для иерархических записей также помещаются в данные. В свойство ТекущийЭлемент объекта помещается строка таблицы значений, для которой в настоящий момент вычисляется функция.

Синтаксис :
ГрупповаяОбработка(Выражения, ВыраженияИерархии, ИмяГруппировки)

Параметры :

Выражения . Выражения, которые нужно вычислить. Строка, в которой через запятую перечислены выражения, которые нужно вычислить. После каждого выражение возможно наличие необязательного ключевого слова КАК и имени колонки результирующей таблицы значений. Каждое выражение образует колонку таблицы значений свойства Данные объекта ДанныеГрупповойОбработкиКомпоновкиДанных.
ВыраженияИерархии . Выражения, которые нужно вычислить для иерархических записей. Аналогично параметру Выражения с тем отличием, что параметр ВыраженияИерархии используется для иерархических записей. Если параметр не указан, то для вычисления значений для иерархических записей используется выражения, указанные в параметре Выражение.
ИмяГруппировки . Имя группировки, в которой нужно вычислять группировку обработки. Строка. Если не указано, то вычисление происходит в текущей группировке. Если вычисление идет в таблице и параметр содержит пустую строку, или не указан, то значение вычисляется для группировки – строки. Компоновщик макета при генерации макета компоновки данных заменяет данное имя на имя группировки в результирующем макете. Если группировка не доступна, то функция будет заменена на значение NULL.

13. Каждый (Every) - если хоть одна запись имеет значение Ложь, то результат Ложь, иначе Истина.

Синтаксис :
Каждый(Выражение)

Параметр :

Выражение - тип Булево.

Пример :
Каждый()

14. Любой (Any) - если хоть одна запись имеет значение Истина, то результат Истина, иначе Ложь

Синтаксис :
Любой(Выражение)

Параметр :

Выражение - тип Булево.

Пример :
Любой()

15. СтандартноеОтклонениеГенеральнойСовокупности (Stddev_Pop) - вычисляет стандартное отклонение совокупности. Вычисляется по формуле: SQRT(ДисперсияГенеральнойСовокупности(X)).

Синтаксис :
СтандартноеОтклонениеГенеральнойСовокупности(Выражение)

Параметр :

Выражение - тип Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ СтандартноеОтклонениеГенеральнойСовокупности(Y) ИЗ Таблица
Результат: 805.694444

16. СтандартноеОтклонениеВыборки (Stddev_Samp) - вычисляет совокупное типовое стандартное отклонение. Вычисляется по формуле: SQRT(ДисперсияВыборки(X)).

Синтаксис :
СтандартноеОтклонениеВыборки(Выражение)

Параметр :

Выражение - тип Число.

Тип возвращаемого значения Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ СтандартноеОтклонениеВыборки(Y) ИЗ Таблица
Результат: 28.3847573

17. ДисперсияВыборки (Var_Samp) - вычисляет типовое различие ряда чисел без учета значений NULL в этом наборе. Вычисляется по формуле: (Сумма(X^2) - Сумма(X)^2 / Количество(X)) / (Количество(X) - 1). Если Количество(X) = 1, то возвращается значение NULL.

Синтаксис :
ДисперсияВыборки(Выражение)

Параметр :

Выражение - тип Число.

Пример :
ВЫБРАТЬ ДисперсияГенеральнойСовокупности(Y) ИЗ Таблица
Результат: 716.17284

19. КовариацияГенеральнойСовокупности (Covar_Pop) - вычисляет ковариацию ряда числовых пар. Вычисляется по формуле: (Сумма(Y * X) - Сумма(X) * Сумма(Y) / n) / n, где n число пар (Y, X) в которых ни Y ни X не являются NULL.

Синтаксис :
КовариацияГенеральнойСовокупности(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ КовариацияГенеральнойСовокупности(Y, X) ИЗ Таблица
Результат: 59.4444444

20. КовариацияВыборки (Covar_Samp) - вычисляет типовое различие ряда чисел без учета значений NULL в этом наборе. Вычисляется по формуле: (Сумма(Y * X) - Сумма(Y) * Сумма(X) / n) / (n-1), где n число пар (Y, X) в которых ни Y ни X не являются NULL.

Синтаксис :
КовариацияВыборки(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ КовариацияВыборки(Y, X) ИЗ Таблица
Результат: 66.875

21. Корреляция (Corr) - вычисляет коэффициент корреляции ряда числовых пар. Вычисляется по формуле: КовариацияГенеральнойСовокупности(Y, X) / (СтандартноеОтклонениеГенеральнойСовокупности(Y) * СтандартноеОтклонениеГенеральнойСовокупности(X)). Не учитываются пары, в которых Y или X равны NULL.

Синтаксис :
Корреляция(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ Корреляция(X, Y) ИЗ Таблица
Результат: 0.860296149

22. РегрессияНаклон (Regr_Slope) - вычисляет наклон линии. Вычисляется по формуле: КовариацияГенеральнойСовокупности(Y, X) / ДисперсияГенеральнойСовокупности(X). Вычисляется без учета пар, содержащих NULL.

Синтаксис :
РегрессияНаклон(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ РегрессияНаклон(Y, X) ИЗ Таблица
Результат: 8.91666667

23. РегрессияОтрезок (Regr_Intercept) - вычисляет Y-точку пересечения линии регресса. Вычисляется по формуле: Среднее(Y) - РегрессияНаклон(Y, X) * Среднее(X). Вычисляется без учета пар, содержащих NULL.

Синтаксис :
РегрессияОтрезок(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Пример :
ВЫБРАТЬ РегрессияКоличество(Y, X) ИЗ Таблица
Результат: 9

25. РегрессияR2 (Regr_R2) - вычисляет коэффициент детерминации. Вычисляется без учета пар, содержащих NULL.

Синтаксис :
РегрессияR2(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Возвращаемое значение:

Null - если ДисперсияГенеральнойСовокупности(X) = 0;
1 - если ДисперсияГенеральнойСовокупности(Y)=0 И ДисперсияГенеральнойСовокупности(X)<>0;
POW(Корреляция(Y,X),2) - если ДисперсияГенеральнойСовокупности(Y)>0 И ДисперсияГенеральнойСовокупности(X)<>0.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ РегрессияR2(Y, X) ИЗ Таблица
Результат: 0.740109464

26. РегрессияСреднееX (Regr_AvgX) - вычисляет среднее число X после исключения X и Y пар, где или X или Y являются пустыми. Среднее(X) вычисляется без учета пар, содержащих NULL.

Синтаксис :
РегрессияСреднееX(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ РегрессияСреднееX(Y, X) ИЗ Таблица
Результат: 5

27. РегрессияСреднееY (Regr_AvgY) - вычисляет среднее число Y после исключения X и Y пар, где или X или Y являются пустыми. Среднее(Y) вычисляется без учета пар, содержащих NULL.

Синтаксис :
РегрессияСреднееY(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ РегрессияСреднееY(Y, X) ИЗ Таблица
Результат: 24.2222222

28. РегрессияSXX (Regr_SXX) - вычисляется по формуле: РегрессияКоличество(Y, X) * ДисперсияГенеральнойСовокупности(X). Вычисляется без учета пар, содержащих NULL.

Синтаксис :
РегрессияSXX(Y, X)

Параметры :

Y - тип Число;
X - тип Число.

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

Пример :
ВЫБРАТЬ РегрессияSYY(Y, X) ИЗ Таблица
Результат: 6445.55556

30. РегрессияSXY (Regr_SXY) - вычисляется по формуле: РегрессияКоличество(Y, X) * КовариацияГенеральнойСовокупности(Y, X). Вычисляется без учета пар, содержащих NULL.

Синтаксис :
РегрессияSXY (Y, X)

Параметры :

Y - тип Число;
X - тип Число.

Пример :

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

ВЫБРАТЬ РегрессияSXY(Y, X) ИЗ Таблица
Результат: 535

31. МестоВПорядке (Rank)

Синтаксис :
МестоВПорядке(Порядок, ПорядокИеррахии, ИмяГруппировки)

Параметры :

Порядок – тип Строка. Содержит выражения, в последовательности которых нужно расположить групповые записи, разделенные через запятую. Направление упорядочивания управляется при помощи слов Возр, Убыв. После поля также можно указать строку Автоупорядочивание, что обозначает, что при упорядочивании ссылок нужно использовать поля упорядочивания, определенные для объекта, на который ссылка. Если последовательность не указана, то значение рассчитывается в последовательности группировки;
ПорядокИеррахии – тип Строка. Содержит выражения упорядочивания для иерархических записей;
ИмяГруппировки – тип Строка. Имя группировки, в которой нужно вычислять группировку обработки. Если не указано, то вычисление происходит в текущей группировке. Если вычисление идет в таблице и параметр содержит пустую строку, или не указан, то значение вычисляется для группировки – строки. Компоновщик макета при генерации макета компоновки данных заменяет данное имя на имя группировки в результирующем макете. Если группировка не доступна, то функция будет заменена на значение NULL.

Если в последовательности имеются две или более записей с одинаковыми значениями полей упорядочивания, то для всех записей функция возвращает одинаковые значения.

Пример :
МестоВПорядке("[Количество Оборот]")

32. КлассификацияABC (ClassificationABC)

Синтаксис :
КлассификацияABC(Значение, КоличествоГрупп, ПроцентыДляГрупп, ИмяГруппировки)

Параметры :

Значение – тип Строка. по которому нужно рассчитывать классификацию. Строка, в которой указано выражение;
КоличествоГрупп - тип Число. Задает количество групп, на который нужно разбить;
ПроцентыДляГрупп - тип Строка. Столько, на сколько групп нужно разбить минус 1. Через запятую. Если не задано, то автоматически;
ИмяГруппировки - тип Строка. Имя группировки, в которой нужно вычислять группировку обработки. Если не указано, то вычисление происходит в текущей группировке. Если вычисление идет в таблице и параметр содержит пустую строку, или не указан, то значение вычисляется для группировки – строки. Компоновщик макета при генерации макета компоновки данных заменяет данное имя на имя группировки в результирующем макете. Если группировка не доступна, то функция будет заменена на значение NULL.

Результатом работы функции будет номер класса, начиная с 1, который соответствует классу A.

Пример :
КлассификацияABC("Сумма(ВаловаяПрибыль)", 3, "60, 90")

В SQL добавлены дополнительные функции, которые позволяют вычислять обобщенные групповые значения. Для применения агрегатных функций предполагается предварительная операция группировки. При группировке все множество кортежей отношения разбивается на группы, в которых собираются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки.

Это делается с помощью агрегатных функций. Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в табл. 7.

Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.

Например, можно вычислить количество студентов, сдававших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю "Дисциплина" и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.

SELECT R1.Дисциплина, COUNT(*)FROM R1GROUP BY R1.Дисциплина

Результат:

Пример. Получить список дисциплин, по которым сдали экзамен не менее 5 человек:

SELECT R1.ДисциплинаFROM R1GROUP BY R1.ДисциплинаHAVING COUNT(*) >= 5 Результат: Здесь инструкция HAVING выбирает группы, удовлетворяющие заданному условию.

Вложенные запросы

Отношение D (Детали)

Отношение PD (Поставки)

1. Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом):

WHERE P.STATYS <

(SELECT MAX(P.STATUS)

Замечание . Т.к. поле P.STATUS сравнивается с результатом подзапроса, то подзапрос должен быть сформулирован так, чтобы возвращать таблицу, состоящую ровно из одной строки и одной колонки .

Замечание

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

2. Использование предиката IN

(SELECT DISTINCT PD.PNUM

WHERE PD.DNUM = 2);

Замечание . В данном случае вложенный подзапрос может возвращать таблицу, содержащую несколько строк.

Замечание . Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:

Выполнить один раз вложенный подзапрос и получить список номеров поставщиков, поставляющих деталь номер 2.
Просканировать таблицу поставщиков P, каждый раз проверяя, содержится ли номер поставщика в результате подзапроса.

3. Использование предиката EXISTS . Получить список поставщиков, поставляющих деталь номер 2:

PD.PNUM = P.PNUM AND

Просканировать таблицу поставщиков P, каждый раз выполняя подзапрос с новым значением номера поставщика, взятым из таблицы P.
В результат запроса включить только те строки из таблицы поставщиков, для которых вложенный подзапрос вернул непустое множество строк.

Замечание . В отличие от двух предыдущих примеров, вложенный подзапрос содержит параметр (внешнюю ссылку), передаваемый из основного запроса - номер поставщика P.PNUM. Такие подзапросы называются коррелируемыми (correlated ). Внешняя ссылка может принимать различные значения для каждой строки-кандидата, оцениваемого с помощью подзапроса, поэтому подзапрос должен выполняться заново для каждой строки, отбираемой в основном запросе. Такие подзапросы характерны для предиката EXISTS, но могут быть использованы и в других подзапросах.

Замечание . Может показаться, что запросы, содержащие коррелируемые подзапросы будут выполняться медленнее, чем запросы с некоррелируемыми подзапросами. На самом деле это не так, т.к. то, как пользователь, сформулировал запрос, не определяет , как этот запрос будет выполняться. Язык SQL является непроцедурным, а декларативным. Это значит, что пользователь, формулирующий запрос, просто описывает, каким должен быть результат запроса , а как этот результат будет получен - за это отвечает сама СУБД.

4. Использование предиката NOT EXISTS . Получить список поставщиков, не поставляющих деталь номер 2:

WHERE NOT EXISTS

PD.PNUM = P.PNUM AND

Замечание . Также как и в предыдущем примере, здесь используется подзапрос. Отличие в том, что в основном запросе будут отобраны те строки из таблицы поставщиков, для которых вложенный подзапрос не выдаст ни одной строки.

5. Получить имена поставщиков, поставляющих все детали:

SELECT DISTINCT PNAME

WHERE NOT EXISTS

PD.DNUM = D.DNUM AND

PD.PNUM = P.PNUM));

Замечание . Данный запрос содержит два вложенных подзапроса и реализует реляционную операцию деления отношений .

Самый внутренний подзапрос параметризован двумя параметрами (D.DNUM, P.PNUM) и имеет следующий смысл: отобрать все строки, содержащие данные о поставках поставщика с номером PNUM детали с номером DNUM. Отрицание NOT EXISTS говорит о том, что данный поставщик не поставляет данную деталь. Внешний к нему подзапрос, сам являющийся вложенным и параметризованным параметром P.PNUM, имеет смысл: отобрать список деталей, которые не поставляются поставщиком PNUM. Отрицание NOT EXISTS говорит о том, что для поставщика с номером PNUM не должно быть деталей, которые не поставлялись бы этим поставщиком. Это в точности означает, что во внешнем запросе отбираются только поставщики, поставляющие все детали.

Внешние соединения

Часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними.

В общем случае синтаксис части FROM в стандарте SQL2 выглядит следующим образом:

FROM <список исходных таблиц>< выражение естественного соединения >< выражение соединения >< выражение перекрестного соединения >< выражение запроса на объединение ><список исходных таблиц>::= <имя_таблицы_1> [ имя синонима таблицы_1] [ …] [,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]<выражение естественного соединения>:: =<имя_таблицы_1> NATURAL { INNER | FULL LEFT | RIGHT } JOIN <имя_таблицы_2><выражение перекрестного соединения>:: = <имя_таблицы_1> CROSS JOIN <имя_таблицы_2><выражение запроса на объединение>::=<имя_таблицы_1> UNION JOIN <имя_таблицы_2><выражение соединения>::= <имя_таблицы_1> { INNERFULL | LEFT | RIGHT } JOIN {ON условие | } <имя_таблицы_2>

В этих определениях INNER - означает внутреннее (естественное) соединение, LEFT - левое соединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено). Ключевое слово RIGHT означает правое внешнее соединение, и в отличие от левого соединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями. Ключевое слово FULL определяет полное внешнее соединение: и левое и правое. При полном внешнем соединении выполняются и правое и левое внешние соединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределенными значениями.

Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то соединение всегда считается внешним.

Рассмотрим примеры выполнения внешних соединений. Снова вернемся к БД "Сессия". Создадим отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее соединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним естественным соединением с таблицей R1, используя столбцы ФИО и Дисциплина. При этом в стандарте разрешено использовать скобочную структуру, так как результат соединения может быть одним из аргументов в части FROM оператора SELECT.

SELECT R1.ФИО, R1.Дисциплина, R1.ОценкаFROM (R2 NATURAL INNER JOIN R3) LEFT JOIN R1 USING (ФИО, Дисциплина)

Результат:

ФИО	Дисциплина	Оценка
Петров Ф. И.	Базы данных
Сидоров К. А.	Базы данных
Миронов А. В.	Базы данных
Степанова К. Е.	Базы данных
Крылова Т. С	Базы данных
Владимиров В. А.	Базы данных
Петров Ф. И.	Теория информации	Null
Сидоров К. А.	Теория информации
Миронов А. В.	Теория информации	Null
Степанова К. Е.	Теория информации
Крылова Т. С	Теория информации

Многие запросы к БД не нуждаются в той степени детализации, которую обеспечивают SQL-запросы, рассмотренные в предыдущих примерах. Так, во всех запросах перечисленных далее, требуется узнать всего одно или несколько значений, которые подытоживают информацию, содержащуюся в БД:

1) какова сумма доходов у всех жителей?
2) каков наибольший и наименьший общий доход отдельного жителя?
3) каков среднедушевой доход жителя Зеленограда?
4) каков среднедушевой доход жителей каждой квартиры?
5) сколько жителей в каждой квартире?

На языке SQL запросы такого типа можно создавать с помощью агрегатных функций и предложений GROUP BY и HAVING, используемых в операторе SELECT.

Использование агрегатных функций

Для подведения итогов по информации, содержащейся в БД, в SQL предусмотрены агрегатные функции. Агрегатная функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец.

Например, агрегатная функция AVG() принимает в качестве аргумента столбец чисел и вычисляет их среднее значение.

Чтобы вычислить среднедушевой доход жителя Зеленограда, нужен такой запрос:

SELECT ‘СРЕДНЕДУШЕВОЙ ДОХОДА, AVG(SUMD) FROM PERSON

В SQL имеется шесть агрегатных функций, которые позволяют получать различные виды итоговой информации (рис. 3.16):

SUM() вычисляет сумму всех значений, содержащихся в столбце;

AVG() вычисляет среднее среди значений, содержащихся в столбце;

- MIN() находит наименьшее среди всех значений, содержащихся в столбце;
- МАХ() находит наибольшее среди всех значений, содержащихся в столбце;
- COUNT() подсчитывает количество значений, содержащихся в столбце;

COUNT(*) подсчитывает количество строк в таблице результатов запроса.

Аргументом агрегатной функции может быть простое имя столбца, как в предыдущем примере, или выражение, как в следующем запросе, задающем вычисление среднедушевого налога:

SELECT AVG(SUMD*0.13)

Рис. 3.16.

При выполнении этого запроса создается временный столбец, содержащий значения (SUMD*0.13) для каждой строки таблицы PERSON, а затем вычисляется среднее значение временного столбца.

Сумму доходов у всех жителей Зеленограда можно вычислить с помощью агрегатной функции SUM:

SELECT SUM(SUMD) FROM PERSON

Агрегатная функция может быть использована и для вычисления итогов по таблице результатов, полученной соединением нескольких исходных таблиц. Например, можно вычислить общую сумму дохода, которая получена жителями от источника с названием «Стипендия»:

SELECT SUM(MONEY)

FROM PROFIT, HAVE_D

WHERE PROFIT.ID=HAVE_D.ID

AND PROFIT.SOURCE^Стипендия’

Агрегатные функции MIN() и MAX() позволяют найти соответственно наименьшее и наибольшее значения в таблице. При этом столбец может содержать числовые или строковые значения либо значения даты или времени.

Например, можно определить:

(а) наименьший общий доход, полученный жителями, и наибольший налог, подлежащий уплате:

SELECT MIN(SUMD), MAX(SUMD*0.13)

(б) даты рождения самого старого и самого молодого жителя:

SELECT MIN(RDATE), MAX(RDATE)

(в) фамилии, имена и отчества самого первого и самого последнего жителей в списке, упорядоченном по алфавиту:

SELECT MIN(FIO), MAX(FIO)

Применяя эти агрегатные функции, нужно помнить, что числовые данные сравниваются по арифметическим правилам, сравнение дат происходит последовательно (более ранние значения дат считаются меньшими, чем более поздние), сравнение интервалов времени выполняется на основании их продолжительности.

При использовании функции MIN() и МАХ() со строковыми данными результат сравнения двух строк зависит от используемой таблицы кодировки символов.

Агрегатная функция COUNT() подсчитывает количество значений в столбце любого типа:

(а) сколько квартир в 1-м микрорайоне?

SELECT COUNT(ADR)

WHERE ADR LIKE *%, 1_

(б) сколько жителей имеют источники дохода?

SELECT C0UNT(DISTINCT NOM)

(в) сколько источников дохода используются жителями?

SELECT COUNT(DISTINCT ID)

Ключевое слово «DISTINCT» указывает, что подсчитываются неповторяющиеся значения в столбце.

Специальная агрегатная функция COUNT(*) подсчитывает строки в таблице результатов, а не значения данных:

(а) сколько квартир во 2-м микрорайоне?

WHERE ADR LIKE "%, 2_-%’

(б) сколько источников дохода у Иванова Ивана Ивановича?

FROM PERSON, HAVE_D

WHERE FIO = "Иванов Иван Иванович"

AND PERSON.NOM = HAVE_D.NOM

(в) сколько жителей проживает в квартире по определенному адресу?

SELECT COUNT(*) FROM PERSON WHERE ADR = "Зеленоград, 1001-45’

Один из способов понять, как выполняются итоговые запросы с агрегатными функциями, - это представить выполнение запроса разбитым на две части. Сначала определяется, как бы запрос работал без агрегатных функций, возвращая несколько строк результатов. Затем применяются агрегатные функции к результатам запроса, возвращая одну итоговую строку.

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

SELECT AVG(SUMD), SUM(SUMD), (100*AVG(MONEY/SUMD))

FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID

Без агрегатных функций запрос выглядел бы так:

SELECT SUMD, SUMD, M0NEY/SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID

и возвращал бы одну строку результатов для каждого жителя и конкретного источника дохода. Агрегатные функции используют столбцы таблицы результатов этого запроса для получения однострочной таблицы с итоговыми результатами.

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

SELECT МАХ(SUMD)-MIN(SUMD)

Однако агрегатная функция не может быть аргументом для другой агрегатной функции, т.е. запрещены вложенные агрегатные функции.

Кроме того, в списке возвращаемых столбцов нельзя одновременно использовать агрегатные функции и обычные имена столбцов, поскольку в этом нет смысла, например:

SELECT FIO, SUM(SUMD)

Здесь первый элемент списка указывает, чтобы СУБД создала таблицу, которая будет состоять из нескольких строк и содержать по одной строке для каждого жителя. Второй элемент списка просит СУБД получить одно результирующее значение, являющееся суммой значений столбца SUMD. Эти два указания противоречат друг другу, что приводит к ошибке.

Сказанное не относится к случаям обработки подзапросов и запросов с группировкой.

Пример 21 . Получить общее количество поставщиков (ключевое слово COUNT ):

SELECT COUNT(*) AS N

В результате получим таблицу с одним столбцом и одной строкой, содержащей количество строк из таблицы P:

Использование агрегатных функций с группировками

Пример 23 . Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY …):

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM;

Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результатирующую таблицу будет включена одна строка:

Замечание . В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки . Следующий запрос выдаст синтаксическую ошибку:

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM;

Причина ошибки в том, что в список отбираемых полей включено поле PNUM, которое не входит в раздел GROUP BY. И действительно, в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM. Из каждой группы строк будет сформировано по одной итоговой строке. При этом нет однозначного ответа на вопрос, какое значение выбрать для поля PNUM в итоговой строке.

Замечание . Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результатирующей таблице, невозможно.

Пример 24 . Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING …):

Замечание . Условие, что суммарное поставляемое количество должно быть больше 400 не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции должны быть размещены в специальном разделе HAVING:

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM

HAVING SUM(PD.VOLUME) > 400;

В результате получим следующую таблицу:

Замечание . В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY.

Использование подзапросов

Очень удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос.

Пример 25 . Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом):

WHERE P.STATYS <

(SELECT MAX(P.STATUS)

Замечание

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

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

Пример 26 . Использование предиката IN

(SELECT DISTINCT PD.PNUM

WHERE PD.DNUM = 2);

Замечание . В данном случае вложенный подзапрос может возвращать таблицу, содержащую несколько строк.

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

Просканировать таблицу поставщиков P, каждый раз проверяя, содержится ли номер поставщика в результате подзапроса.

Пример 27 . Использование предиката EXIST . Получить список поставщиков, поставляющих деталь номер 2:

PD.PNUM = P.PNUM AND

Просканировать таблицу поставщиков P, каждый раз выполняя подзапрос с новым значением номера поставщика, взятым из таблицы P.

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

Замечание . В отличие от двух предыдущих примеров, вложенный подзапрос содержит параметр (внешнюю ссылку), передаваемый из основного запроса - номер поставщика P.PNUM. Такие подзапросы называются коррелируемыми (correlated ). Внешняя ссылка может принимать различные значения для каждой строки-кандидата, оцениваемого с помощью подзапроса, поэтому подзапрос должен выполняться заново для каждой строки, отбираемой в основном запросе. Такие подзапросы характерны для предиката EXIST, но могут быть использованы и в других подзапросах.

Пример 28 . Использование предиката NOT EXIST . Получить список поставщиков, не поставляющих деталь номер 2:

PD.PNUM = P.PNUM AND

Замечание . Также как и в предыдущем примере, здесь используется коррелируемый подзапрос. Отличие в том, что в основном запросе будут отобраны те строки из таблицы поставщиков, для которых вложенный подзапрос не выдаст ни одной строки.

Пример 29 . Получить имена поставщиков, поставляющих все детали:

SELECT DISTINCT PNAME

PD.DNUM = D.DNUM AND

PD.PNUM = P.PNUM));

Самый внутренний подзапрос параметризован двумя параметрами (D.DNUM, P.PNUM) и имеет следующий смысл: отобрать все строки, содержащие данные о поставках поставщика с номером PNUM детали с номером DNUM. Отрицание NOT EXIST говорит о том, что данный поставщик не поставляет данную деталь. Внешний к нему подзапрос, сам являющийся вложенным и параметризованным параметром P.PNUM, имеет смысл: отобрать список деталей, которые не поставляются поставщиком PNUM. Отрицание NOT EXIST говорит о том, что для поставщика с номером PNUM не должно быть деталей, которые не поставлялись бы этим поставщиком. Это в точности означает, что во внешнем запросе отбираются только поставщики, поставляющие все детали.