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

1. Отчетность по агрегированным данным с использованием групповых функций

© Oracle, 2007. Все права защищены.

2. Цели

Изучив материал этого занятия, вы сможете:
• Определять доступные групповые функции
• Описывать использование групповых функций
• Группировать данные при помощи предложения
GROUP BY
• Включать или исключать сгруппированные строки
при помощи предложения HAVING
5-2
© Oracle, 2007. Все права защищены.

3. План занятия

• Групповые функции:
– Типы и синтаксис
– Использование AVG, SUM, MIN, MAX, COUNT
– Использование в групповых функциях ключевого слова
DISTINCT
– Значения NULL в групповых функциях
• Группирование строк:
– Предложение GROUP BY
– Предложение HAVING
• Вложенные групповые функции
5-3
© Oracle, 2007. Все права защищены.

4. Что такое групповые функции?

Групповые функции оперируют с наборами строк и выдают
по одному результату на группу.
EMPLOYEES
Максимальный
оклад в таблице
EMPLOYEES
…
5-4
© Oracle, 2007. Все права защищены.

5. Типы групповых функций

5-5
AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE
Групповые
функции
© Oracle, 2007. Все права защищены.

6. Групповые функции: синтаксис

SELECT
FROM
[WHERE
[ORDER BY
5-6
групповая_функция(столбец), ...
таблица
условие]
столбец];
© Oracle, 2007. Все права защищены.

7. Использование функций AVG и SUM

Функции AVG и SUM можно использовать для числовых
данных.
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM
employees
WHERE job_id LIKE '%REP%';
5-7
© Oracle, 2007. Все права защищены.

8. Использование функций MIN и MAX

Функции MIN и MAX можно использовать с числовыми
и символьными типами данных, а также с датами.
SELECT MIN(hire_date), MAX(hire_date)
FROM
employees
5-8
© Oracle, 2007. Все права защищены.

9. Использование функции COUNT

COUNT(*) возвращает число строк в таблице:
SELECT COUNT(*)
FROM
employees
WHERE department_id = 50;
1
COUNT(expr) возвращает число строк, для которых
значение expr не равно NULL:
SELECT COUNT(commission_pct)
FROM
employees
WHERE department_id = 80;
2
5-9
© Oracle, 2007. Все права защищены.

10. Использование ключевого слова DISTINCT

• COUNT(DISTINCT expr) возвращает число уникальных
и не равных NULL значений expr.
• Чтобы показать число различных отделов, представленных
в таблице EMPLOYEES:
SELECT COUNT(DISTINCT department_id)
FROM
employees
5 - 10
© Oracle, 2007. Все права защищены.

11. Групповые функции и значения Null

Групповые функции игнорируют значения NULL в столбцах:
SELECT AVG(commission_pct)
FROM
employees
1
Функция NVL заставляет групповые функции учитывать
значения NULL:
SELECT AVG(NVL(commission_pct, 0))
FROM
employees
2
5 - 11
© Oracle, 2007. Все права защищены.

12. План занятия

• Групповые функции:
– Типы и синтаксис
– Использование AVG, SUM, MIN, MAX, COUNT
– Использование в групповых функциях ключевого слова
DISTINCT
– Значения NULL в групповых функциях
• Группирование строк:
– Предложение GROUP BY
– Предложение HAVING
• Вложенные групповые функции
5 - 12
© Oracle, 2007. Все права защищены.

13. Создание групп данных

EMPLOYEES
4400
9500
Средний оклад в таблице
EMPLOYEES для
каждого из отделов
3500
6400
10033
…
5 - 13
© Oracle, 2007. Все права защищены.

14. Создание групп данных: синтаксис предложения GROUP BY

SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
столбец, групповая_функция(столбец)
таблица
условие]
выражение_группировки]
столбец];
Строки таблицы можно объединить в более компактные
группы при помощи предложения GROUP.
5 - 14
© Oracle, 2007. Все права защищены.

15. Использование предложения GROUP BY

Все столбцы из списка SELECT, не используемые
групповыми функциями, должны быть перечислены
в предложении GROUP BY.
SELECT
department_id, AVG(salary)
FROM
employees
GROUP BY department_id ;
5 - 15
© Oracle, 2007. Все права защищены.

16. Использование предложения GROUP BY

Столбец, перечисленный в предложении GROUP BY, не
обязательно должен присутствовать в списке SELECT.
SELECT
AVG(salary)
FROM
employees
GROUP BY department_id ;
5 - 16
© Oracle, 2007. Все права защищены.

17. Группирование по нескольким столбцам

EMPLOYEES
Суммирование окладов
в таблице EMPLOYEES
для всех должностей,
с группировкой по отделам.
…
5 - 17
© Oracle, 2007. Все права защищены.

18. Использование предложения GROUP BY с несколькими столбцами

SELECT
FROM
GROUP BY
ORDER BY
5 - 18
department_id dept_id, job_id, SUM(salary)
employees
department_id, job_id
department_id;
© Oracle, 2007. Все права защищены.

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

Любой столбец или выражение из списка SELECT, которые
не являются агрегатной функцией, должны быть
перечислены в предложении GROUP BY:
SELECT department_id, COUNT(last_name)
FROM
employees
Чтобы для каждого идентификатора
отдела department_id подсчитать
число фамилий, необходимо
добавить предложение GROUP BY.
SELECT
department_id, job_id, COUNT(last_name)
FROM
employees
GROUP BY department_id ;
Либо добавьте в GROUP BY столбец
job_id, либо удалите столбец
job_id из списка SELECT.
5 - 19
© Oracle, 2007. Все права защищены.

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

• Для ограничения групп нельзя использовать предложение WHERE.
• Для ограничения групп следует использовать предложение HAVING.
• Групповые функции в предложении WHERE использовать нельзя.
SELECT
FROM
WHERE
GROUP BY
department_id, AVG(salary)
employees
AVG(salary) > 8000
department_id ;
Для ограничения
групп нельзя
использовать
предложение
WHERE
5 - 20
© Oracle, 2007. Все права защищены.

21. Ограничение групповых результатов

EMPLOYEES
Максимальный оклад
по отделам, у которых
он больше $10 000
…
5 - 21
© Oracle, 2007. Все права защищены.

22. Ограничение групповых результатов при помощи предложения HAVING

Когда используется предложение HAVING, сервер Oracle
ограничивает группы следующим образом:
1. Строки объединяются в группы.
2. Применяется групповая функция.
3. Отображаются группы, соответствующие предложению
HAVING.
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
5 - 22
столбец, групповая_функция
таблица
условие]
выражение_group_by]
условие_группирования]
столбец];
© Oracle, 2007. Все права защищены.

23. Использование предложения HAVING

SELECT
FROM
GROUP BY
HAVING
5 - 23
department_id, MAX(salary)
employees
department_id
MAX(salary)>10000 ;
© Oracle, 2007. Все права защищены.

24. Использование предложения HAVING

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
5 - 24
job_id, SUM(salary) PAYROLL
employees
job_id NOT LIKE '%REP%'
job_id
SUM(salary) > 13000
SUM(salary);
© Oracle, 2007. Все права защищены.

25. План занятия

• Групповые функции:
– Типы и синтаксис
– Использование AVG, SUM, MIN, MAX, COUNT
– Использование в групповых функциях ключевого слова
DISTINCT
– Значения NULL в групповых функциях
• Группирование строк:
– Предложение GROUP BY
– Предложение HAVING
• Вложенные групповые функции
5 - 25
© Oracle, 2007. Все права защищены.

26. Вложенные групповые функции

Отображение максимального среднего оклада:
SELECT
MAX(AVG(salary))
FROM
employees
GROUP BY department_id ;
5 - 26
© Oracle, 2007. Все права защищены.

27. Заключение

На этом занятии были изучены следующие темы:
• Использование групповых функций COUNT, MAX, MIN, SUM
и AVG
• Создание запросов, использующих предложение GROUP BY
• Создание запросов, использующих предложение HAVING
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
5 - 27
столбец, групповая_функция
таблица
условие]
выражение_группирования]
условие_группирования]
столбец];
© Oracle, 2007. Все права защищены.

28. Упражнение 5: обзор

Упражнение охватывает следующие темы:
• Создание запросов, использующих групповые функции
• Группирование строк для получения нескольких
результатов
• Ограничение групп при помощи предложения HAVING
5 - 28
© Oracle, 2007. Все права защищены.