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

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

Copyright © 2004, Oracle. All rights reserved.

2. Objectives

После завершения этого урока вы должны знать :
• Что такое групповые функции и как их
использовать
• Как производить группировку с помощю GROUP
BY
• Как производить включение или исключение
сгруппированных строк с помощью HAVING
4-2
Copyright © 2004, Oracle. All rights reserved.

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

Групповые функции работают с наборами строк,
чтобы дать один результат в каждой группе.
EMPLOYEES
Maximum salary in
EMPLOYEES table
…
4-3
Copyright © 2004, Oracle. All rights reserved.

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

4-4
AVG
COUNT
MAX
MIN
SUM
…
Group
functions
Copyright © 2004, Oracle. All rights reserved.

5. Функции Группа: Синтаксис

SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
4-5
[column,] group_function(column), ...
table
condition]
column]
column];
Copyright © 2004, Oracle. All rights reserved.

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

Вы можете использовать AVG и SUM для числовых
данных.
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM
employees
WHERE job_id LIKE '%REP%';
4-6
Copyright © 2004, Oracle. All rights reserved.

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

Вы можете использовать MAX и MIN для типов
numeric, character, date
SELECT MIN(hire_date), MAX(hire_date)
FROM
employees;
4-7
Copyright © 2004, Oracle. All rights reserved.

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

COUNT(*) возвращает количество строк в таблице :
SELECT COUNT(*)
FROM
employees
WHERE department_id = 50;
1
COUNT(expr) возвращает количество строк с
ненулевых значений для expr:
SELECT COUNT(commission_pct)
FROM
employees
WHERE department_id = 80;
2
4-8
Copyright © 2004, Oracle. All rights reserved.

9. Использование DISTINCT

COUNT(DISTINCT expr) возвращает число
различных ненулевых значениях expr.
Для того, чтобы отобразить количество
различных значений отдела в таблице
EMPLOYEES :
SELECT COUNT(DISTINCT department_id)
FROM
employees;
4-9
Copyright © 2004, Oracle. All rights reserved.

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

Групповые функции игнорируют столбцы со
значением null :
SELECT AVG(commission_pct)
FROM
employees;
1
Функция NVL позволяет включать нулевые
значения:
SELECT AVG(NVL(commission_pct, 0))
FROM
employees;
2
4-10
Copyright © 2004, Oracle. All rights reserved.

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

EMPLOYEES
4400
9500
3500
6400
Average
salary in
EMPLOYEES
table for each
department
10033
…
4-11
Copyright © 2004, Oracle. All rights reserved.

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

SELECT
column, group_function(column)
FROM
table
[WHERE
condition]
[GROUP BY group_by_expression]
[ORDER BY column];
Вы можете разделить строки в таблице на более
мелкие группы при помощи предложения GROUP BY.
4-12
Copyright © 2004, Oracle. All rights reserved.

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

Все столбцы в списке выбора, к которым не
применяются групповые функции должны быть
описаны в предложении GROUP BY.
SELECT
department_id, AVG(salary)
FROM
employees
GROUP BY department_id ;
4-13
Copyright © 2004, Oracle. All rights reserved.

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

Столбец в GROUP не обязательно должен
находиться в SELECT .
SELECT
AVG(salary)
FROM
employees
GROUP BY department_id ;
4-14
Copyright © 2004, Oracle. All rights reserved.

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

EMPLOYEES
…
4-15
Add the
salaries in
the EMPLOYEES
table for
each job,
grouped by
department
Copyright © 2004, Oracle. All rights reserved.

16. Использование GROUP BY на несколько колонок

Использование GROUP BY
несколько колонок
на
SELECT
department_id dept_id, job_id, SUM(salary)
FROM
employees
GROUP BY department_id, job_id ;
4-16
Copyright © 2004, Oracle. All rights reserved.

17. Некорректные Запросы Использование групповых функций

Любой столбец или выражение в списке SELECT,
который не является агрегатной функцией должен
быть описан в предложении GROUP BY:
SELECT department_id, COUNT(last_name)
FROM
employees;
SELECT department_id, COUNT(last_name)
*
ERROR at line 1:
ORA-00937: not a single-group group function
4-17
Copyright © 2004, Oracle. All rights reserved.

18.

Некорректные Запросы
Использование групповых функций
Вы не можете использовать групповые функции в WHERE.
Для этой цели используйте HAVING.
SELECT
FROM
WHERE
GROUP BY
department_id, AVG(salary)
employees
AVG(salary) > 8000
department_id;
WHERE
AVG(salary) > 8000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
Cannot use the WHERE clause to restrict groups
4-18
Copyright © 2004, Oracle. All rights reserved.

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

EMPLOYEES
…
4-19
The maximum
salary
per department
when it is
greater than
$10,000
Copyright © 2004, Oracle. All rights reserved.

20. Ограничение результатов группировки с использованием HAVING

При использовании предложения HAVING, сервер
Oracle ограничивает группы следующим образом:
1. Строки сгруппированы.
2. Применяется групповая функция.
3. Отображаются группы, соответствующие
предложения HAVING.
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
4-20
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
Copyright © 2004, Oracle. All rights reserved.

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

SELECT
FROM
GROUP BY
HAVING
4-21
department_id, MAX(salary)
employees
department_id
MAX(salary)>10000 ;
Copyright © 2004, Oracle. All rights reserved.

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

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
4-22
job_id, SUM(salary) PAYROLL
employees
job_id NOT LIKE '%REP%'
job_id
SUM(salary) > 13000
SUM(salary);
Copyright © 2004, Oracle. All rights reserved.

23. Nesting Group Functions

Отображение максимальной средней заработной
платы:
SELECT
MAX(AVG(salary))
FROM
employees
GROUP BY department_id;
4-23
Copyright © 2004, Oracle. All rights reserved.

24. Summary

Вы научились:
Использовать групповые функции COUNT, MAX, MIN и AVG
Писать запросы, которые используют GROUP BY
Писать запросы, которые используют HAVING
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
4-24
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
Copyright © 2004, Oracle. All rights reserved.

25. Practice 4: Overview

This practice covers the following topics:
• Writing queries that use the group functions
• Grouping by rows to achieve more than one result
• Restricting groups by using the HAVING clause
4-25
Copyright © 2004, Oracle. All rights reserved.