Многострочные функции (Multiple-row Functions).

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

1. Перечислить основные функции агрегирования;

2. Описать правила использования функций агрегирования;

3. Сгруппировать данные, используя предложение GROUP BY;

4. Включать и исключать из рассмотрения группы данных, используя предложение HAVING.

1. Многострочные функции.

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

EMPLOYEES

DEPARTMENT_ID	SALARY
90	24000
90	17000
90	17000
60	9000
60	6000
60	4200
50	5800	Максимальный	MAX(SALARY)
50	3500	оклад в таблице	24000
50	3100	EMPLOYEES
50	2600
50	2500
80	10500
80	11000
80	8600
10	4400

. . .

20 rowsselected.

1.1 Типы функций агрегирования.

Функция	Описание
AVG([ DISTINCT | ALL ] expr)	Возвращает среднее значение выражения expr без учёта неопределённых значений.
COUNT({ * | [DISTINCT | ALL ] expr})	Возвращает количество строк, где результатом вычисления выражения expr является любое определённое значение. Если используется “*”, подсчитываются все выбранные строки, включая дубликаты и строки с неопределёнными значениями.
MAX([ DISTINCT | ALL ] expr)	Возвращает максимальное значение выражения expr без учёта неопределённых значений.
MIN([ DISTINCT | ALL ] expr)	Возвращает минимальное значение выражения expr без учёта неопределённых значений.
STDDEV([ DISTINCT | ALL ] expr)	Возвращает стандартное отклонение значений выражения expr без учёта неопределённых значений.
SUM([ DISTINCT | ALL ] expr)	Возвращает сумму значений выражения expr без учёта неопределённых значений.
VARIANCE([ DISTINCT | ALL ] expr)	Возвращает дисперсию значений выражения expr без учёта неопределённых значений.

Указания по использованию функций агрегирования.

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

2. Все групповые функции, кроме COUNT(*), игнорируют неопределённые значения. Для замены неопределённых значений определёнными используются функцииNVL, NVL2, COALESCE.

3. Сервер Oracleнеявно сортирует данные в порядке возрастания значений, если используется предложениеGROUP BY. Для того чтобы изменить порядок сортировки, можно использовать предложениеORDER BYс опциейDESC.

1.2 Использование функций avg и sum.

Функции AVGиSUMприменяются к столбцам с числовыми данными.

Пример

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)

FROM employees

WHERE job_id LIKE '%REP%';

Результат:

AVG(SALARY)	MAX(SALARY)	MIN(SALARY)	SUM(SALARY)
8272.72727	11500	6000	273000