Похожие презентации:
Отчетность по агрегированным данным с использованием групповых функций
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. Групповые функции: синтаксис
SELECTFROM
[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. Создание групп данных
EMPLOYEES4400
9500
Средний оклад в таблице
EMPLOYEES для
каждого из отделов
3500
6400
10033
…
5 - 13
© Oracle, 2007. Все права защищены.
14. Создание групп данных: синтаксис предложения GROUP BY
SELECTFROM
[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 с несколькими столбцами
SELECTFROM
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
SELECTFROM
GROUP BY
HAVING
5 - 23
department_id, MAX(salary)
employees
department_id
MAX(salary)>10000 ;
© Oracle, 2007. Все права защищены.
24. Использование предложения HAVING
SELECTFROM
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. Все права защищены.