Раздел GROUP BY

Если в табличном выражении присутствует раздел GROUP BY, то следующим выполняется он.

Если обозначить через R таблицу, являющуюся результатом предыдущего раздела (FROM или WHERE), то результатом раздела GROUP BY является разбиение R на множество групп строк, состоящего из минимального числа групп таких, что для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца равны. Для обозначения результата раздела GROUP BY в стандарте используется термин “сгруппированная таблица”.

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

COUNT — вычисляет число значений в поле

SUM — вычисляет сумму значений по полю

AVERAGE — вычисляет среднее значение в поле

MAX — вычисляет наибольшее значение в поле

MIN — вычисляет наименьшее значение в поле

Агрегатные функции предназначены для того, чтобы вычислять некоторое значение для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. Для всех агрегатных функций, кроме COUNT(*), фактический порядок вычислений следующий: на основании параметров агрегатной функции из заданного множества строк производится список значений. Затем по этому списку значений производится вычисление функции. Если список оказался пустым, то значение функции COUNT для него есть 0, а значение всех остальных функций — NULL.

Пусть T обозначает тип значений из этого списка. Тогда результат вычисления функции COUNT — точное число с масштабом и точностью, определяемыми в реализации. Тип результата значений функций MAX и MIN совпадает с T. При вычислении функций SUM и AVG тип T не должен быть типом символьных строк, а тип результата функции — это тип точных чисел с определяемыми в реализации масштабом и точностью, если T — тип точных чисел, и тип приблизительных чисел с определяемой в реализации точностью, если T — тип приблизительных чисел.

Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках.

Если агрегатная функция специфицирована с ключевым словом DISTINCT, то список значений строится из значений указанного столбца (в этом случае не допускается вычисление арифметических выражений). Далее из этого списка удаляются неопределенные значения, и в нем устраняются значения-дубликаты. Затем вычисляется указанная функция.

Если агрегатная функция специфицирована без ключевого слова DISTINCT (или с ключевым словом ALL), то список значений формируется из значений арифметического выражения, вычисляемого для каждой строки заданного множества. Далее из списка удаляются неопределенные значения, и производится вычисление агрегатной функции. Вв этом случае не допускается применение функции COUNT.

Агрегатные функции можно разумно использовать в спецификации курсора, операторе выборки и подзапросе после ключевого слова SELECT и в условии выборки раздела HAVING.

Пример 16. Определение общего количества студентов:

SELECT COUNT(*) FROM student;

Пример 17. Подсчет суммарного размера сипендии:

SELECT SUM(stip) FROM student;

Пример 18. Определение студентов с баллом меньшим, чем среднее значение балла:

SELECT fam FROM student WHERE ball < (SELECT AVG(ball) FROM student);

Если утверждение SELECT содержит предложение GROUP BY, то список выбора может содержать только следующие типы выражений:

Константы

Агрегатные функции

Функции USER, UID, и SYSDATE

Выражения, соответствующие перечисленным в предложении GROUP BY

Выражения, включающие выше перечисленные выражения

Пример 19. Вычисление общего размера стипендии для каждой специальности:

SELECT kod_s, SUM(stip) FROM student GROUP BY kod_s;

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

SELECT kod_s, SUM(stip) FROM student WHERE ball <> 100 GROUP BY kod_s;

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

Чтобы достигнуть указанного результата с помощью одной команды SELECT, необходимо использовать функцию ROLLUP(), которая суммирует данные по уровням, указанным во фразе GROUP BY, и подводит общий итог:

SELECT kod_s, COUNT(*) FROM student GROUP BY ROLLUP(kod_s);

Результат:

kod_s	count(*)

Пример 22. В предыдущем примере, когда ORACLE сообщает общий итог, то остается незаполненной последняя ячейка в столбце, по которому строилась фраза GROUP BY.

Избежать этого позволяет функция GROUPING(), которая сообщает о статусе текущего уровня. Данная функция возвращает два значения – «0» указывает, что текущая строка является группой, специфицированной уровнем GROUP By, а «1» указывает, что чтрока сгруппирована на более высоком уровне. Применим функцию GROUPPING() к предыдущему примеру, чтобы в результирующей выборке в незаполненной ячейке отображался текст «ИТОГО»:

SELECT DECODE(GROUPING(kod_s),0,kod_s, 'ИТОГО') kod_s, COUNT(*) FROM student GROUP BY ROLLUP(kod_s);

Результат:

kod_s	count(*)
ИТОГО

Функция ROLLUP() может работать на нескольких столбцах, например, можно подсчитать сотрудников предприятия в одной выборке по 1) подразделениям и профессиям; 2) по отделам; 3) общим итогам. Для группировки данных по нескольким измерениям с выводом промежуточных и итоговых данных, сгруппированных также по определенному критерию, используется функция CUBE().

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

SELECT kod_s, ball, COUNT(*) FROM student GROUP BY CUBE(kod_s, ball);

Результат:

kod_s	ball	count(*)
			Промежуточный итог:количество студентовна данной специальности
			Промежуточный итог:количество студентовна данной специальности
			Промежуточный итог:количество студентовна данной специальности
			Дополнительный итог: количество студентов с одинаковыми баллами
			Общий итог: количество студентов в таблице

В начало