Отчетность по агрегированным данным с использованием групповых функций
Цели
План занятия
Что такое групповые функции?
Типы групповых функций
Групповые функции: синтаксис
Использование функций AVG и SUM
Использование функций MIN и MAX
Использование функции COUNT
Использование ключевого слова DISTINCT
Групповые функции и значения Null
План занятия
Создание групп данных
Создание групп данных: синтаксис предложения GROUP BY
Использование предложения GROUP BY
Использование предложения GROUP BY
Группирование по нескольким столбцам
Использование предложения GROUP BY с несколькими столбцами
Недопустимые запросы при использовании групповых функций
Недопустимые запросы при использовании групповых функций
Ограничение групповых результатов
Ограничение групповых результатов при помощи предложения HAVING
Использование предложения HAVING
Использование предложения HAVING
План занятия
Вложенные групповые функции
Заключение
Упражнение 5: обзор
342.00K
Категория: Базы данныхБазы данных

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

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. Все права защищены.
English     Русский Правила