Функции агрегирования

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

К функциям агрегирования относятся следующие функции языка SQL:

• COUNT - подсчет количества всех значений столбцов за исключением значения NULL и с учетом указания фраз ALL или DISTINCT;

• COUNT (*) - подсчет количества всех значений столбцов в группе;

• AVG - определение среднего значения;

• SUM - подсчет суммы всех значении группы. Если при этом получаемое значение выходит за пределы суммируемого типа данных, то инициируется ошибка выполнения SQL-оператора;

• MAX - определение максимального значения из группы

• MIN - определение минимального значения из группы.

Фраза HAVING оператора SELECT определяет предикат аналогично фразе WHERE, но применяемый к строкам, полученным в результате выполнения функций агрегирования.

Приведем пример выбора с применением групп. Столбец dno имеет всего три различных значения: 11, 22 и 33. Для каждой из трех групп находится минимальное и максимальное значение столбца f2:

SELECT dno, MIN(f2), MAX(f2)

FROM tbl1

GROUP BY dno;

Результатом выполнения этого SQL-оператора будет формирование следующих строк:

DNO MIN(f2) MAX(f2)

11 125 200

22 200 2300

33 100 150

При выборе с применением групп и с дополнительным ограничением на значение в столбце MIN(f2):

SELECT dno, MIN(f2), MAX(f2)

FROM tbl1

GROUP BY dno

HAVING MAX(f2) < 1000;

В результате выполнения этого SQL-оператора будут возвращены только две строки: первая и последняя:

DNO MIN(f2) MAX(f2)

11 125 200

33 100 150

Упорядочивание результирующего набора

Фраза ORDER BY применяется для упорядочивания результирующего набора, которое выполняется в соответствии со значениями столбцов, указанных в списке после фразы ORDER BY. Сначала производится упорядочивание по первому указанному столбцу, потом по второму и т.д. При упорядочивании можно указать опцию ASC (по возрастанию) или DESC (по убыванию).

Например:

SELECT f1,f2 FROM tbl1 ORDER BY f2;

Создание таблиц по образцу

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

Например:

CREATE TABLE tbl2 LIKE tbl1;.

Соединение таблиц

Соединение одинаковых таблиц

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

Например:

select t1.f1, t1.f2, t2.f1, t2.f2

from tbl1 t1, tbl1 t2

where t1.f1= t2.f2;

Перекрестное соединение(CROSS JOIN)

Если фраза FROM определяет более одной таблицы или подзапроса, то все эти таблицы соединяются. По умолчанию объединенная таблица представляет собой перекрестное соединение (CROSS JOIN), называемое также декартовым произведением (Cartesian product).

Следующие два оператора эквивалентны:

SELECT tbl1.f1, tbl2.f1 FROM tbl1, tbl2;

SELECT tbl1.f1, tbl2.f1

FROM tbl1 CROSS JOIN tbl2;

Рассмотрим случай, когда таблицы tbl1 и tbl2 имеют строки, отображенные операторами SELECT на рисунке 3.2.

Рис. 3.2.  Строки таблиц tbl1 и tbl2

Перекрестное соединение таблиц tbl1 и tbl2 оператором SELECT сформируют результирующий набор, отображаемый на рисунке 3.3.

Рис. 3.3.  Внутреннее соединение (cross JOIN)

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

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

Соединяемые таблицы перечисляются через запятую во фразе FROM оператора SELECT .

Во фразе FROM можно использовать следующие операторы соединений:

• CROSS JOIN - перекрестное соединение;

• NATURAL JOIN- естественное соединение. Стандарт SQL определяет это соединение как результат объединения таблиц по всем одноименным столбцам. Естественное соединение может быть следующих типов:

  • INNER JOIN - внутреннее соединение, используется по умолчанию;

  • LEFT JOIN [OUTER] - левое внешнее соединение;

  • RIGHT JOIN [OUTER] - правое внешнее соединение;

  • FULL JOIN [OUTER] - полное внешнее соединение;

• UNION JOIN - соединение объединения.