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

Цели
Изучить функции группировки
Научиться применять функции группировки
Группировать информацию с использованием секции GROUP BY
Ограничивать выборку сгруппированных строк секцией HAVING

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

Функции группировки
AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE

Синтаксис
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];

Функции AVG и SUM
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

Функции MIN и MAX
SELECT MIN(hire_date), MAX(hire_date) FROM employees;

Функция COUNT
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;
COUNT(*) возвращает число строк в таблице:
COUNT(expr)возвращает число строк not null в таблице:

Ключевое слово DISTINCT
COUNT(DISTINCT expr) возвращает число уникальных not null строк
Отображение количества уникальных подразделений в табблице EMPLOYEES
SELECT COUNT(DISTINCT department_id)
FROM employees;

Функции группировки и NULL значения
Функции группировки игнорируют NULL значения
Функция NVL заставляет функцию группировки использовать NULL значения
SELECT AVG(commission_pct) FROM employees;
SELECT AVG(NVL(commission_pct, 0)) FROM employees;

Группировка данных

Секция GROUP BY
Можно разделять строки в таблицах на группы используя секцию GROUP BY
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

Все колонки в секции SELECT, к которым не применяется группировка, должны быть в секции GROUP BY.
Секция GROUP BY
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

Колонки, находящиеся в секции GROUP BY, не должны находиться в секции SELECT без функции группировки.
Секция GROUP BY
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

Группировка по нескольким колонкам

Группировка по нескольким колонкам
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

Часто встречаемые ошибки
Колнка, находящаяся в секции SELECT, не обернута групповой функцией и не находится в секции GROUP BY
SELECT department_id, COUNT(last_name)
FROM employees;
ERROR at line 1:
ORA-00937: not a single-group group function

Часто встречаемые ошибки
Нельзя использовать секцию WHERE для ограничения сгруппированных строк
Используйте HAVING
Функцию группировки нельзя использовать в секции WHERE.
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
ERROR at line 3:
ORA-00934: group function is not allowed here

Ограничение результатов группировки

Секция HAVING
При использовании секции HAVING сервер ORACLE выполняет следующие шаги:
Группирует строки
Применяет функцию группировки
Сравнивает группы с ограничением в секции HAVING
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Секция HAVING
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;

Секция HAVING
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);