ГРУППОВЫЕ ФУНКЦИИ

1. ГРУППОВЫЕ ФУНКЦИИ

2. Групповые функции • Групповые функции работают с множествами строк и возвращают один результат на группу. • Групповые функции могут быть заданы в списках SELECT и предложении HAVING. • Предложение GROUP BY в команде SELECT разбивает множество строк на группы. • Предложение HAVING исключает из результата некоторые группы.

3. Предложения GROUP BY и HAVING команде SELECT: синтаксис SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; • Предложение GROUP BY делит строки на группы. • Предложение HAVING исключает из рассмотрения некоторые группы.

4. Групповые функции • AVG(DISTINCT|ALL|n) • COUNT(DISTINCT|ALL|выражение|*) • МАХ(DISTINCT |ALL|выражение) • МIN(DISTINCT |ALL|выражение) • STDDEV(DISTINCT|ALL|n) • SUM(DISTINCT|ALL|n) • VARIANCE(DISTINCT|ALL|n)

5. Групповые функции: пример • Функции AVG и SUM применяются к столбцам с числовыми данными. SQL> SELECT AVG(salary),MAX(salary) , 2 MIN(salary),SUM (salary) 3 FROM s_emp 4 WHERE UPPER(title) LIKE 'SALES%’; • Функции MAX и MIN применяются к данным любого типа. SQL> SELECT MIN(last_name),MAX(last_name) 2 FROM s_emp;

6. Функция COUNT: примеры • COUNT(*) возвращает количество строк в таблице SQL> SELECT COUNT(*) 2 FROM s_emp 3 WHERE dept_id =31; • COUNT(expr) возвращает количество строк с определенными значениями (не NULL). SQL> SELECT COUNT(commission_pct) 2 FROM s_emp 3 WHERE dept_id=31;

7. Предложение GROUP BY:синтаксис SELECT столбец, групповая_функция FROM таблица [WHERE условие] [GROUP BY выражение_группирования] [ORDER BY столбец]; • Предложение GROUP BY разбивает строки таблицы на группы. • Если в списке SELECT заданы столбцы, их список должен использоваться и в предложении GROUP BY. • С помощью предложения ORDER BY можно изменить порядок сортировки, используемый по умолчанию.

8. Предложение GROUP BY: пример • Все столбцы из списка SELECT, не входящие в групповые функции, должны быть включены в предложение GROUP BY. • Столбец, заданный в предложении GROUP BY, не обязательно должен быть задан в предложении SELECT. • Если столбец из предложения GROUP BY входит в список SELECT, результат имеет больше смысла. SQL> SELECT title, MAX(salary) 2 FROM s_emp 3 GROUP BY title;

9. Недействительные запросы с групповыми функциями • Если предложение GROUP BY отсутствует или неправильно, выдается сообщение об ошибке. • Все столбцы или выражения из списка SELECT, не являющиеся групповой функцией, должны быть включены в предложение GROUP BY. SQL> SELECT region_id,COUNT (name) 2 FROM s_dept; SELECT region_id,COUNT (name) * ERROR at line 1: ORA-00937:not a single-group group function

10. Группы внутри групп: примеры • Для получения сводных результатов по нескольким группам и подгруппам следует указать в предложении GROUP BY более одного столбца. • Порядок сортировки, используемый по умолчанию, определяется порядком столбцов в предложении GROUP BY. SQL> SELECT dept_id,title,COUNT(*) 2 FROM s_emp 3 GROUP BY dept_id,title;

11. Предложение HAVING: синтаксис SELECT столбец, групповая_функция FROM таблица [WHERE условие] [GROUP BY выражения_группирования] [HAVING условие_группы] [ORDER BY столбец] ; Предложение HAVING используется для дальнейшего ограничения количества групп. - Шаг 1: Группирование строк. - Шаг 2: Применение групповых функций к группам. - Шаг 3: Вывод групп, удовлетворяющих условию предложения HAVING.

12. Предложение HAVING: пример Группа "President" в выходных данных отсутствует, т.к. не удовлетворяет заданному критерию. SQL> SELECT title, SUM(salary) PAYROLL 2 FROM s_emp 3 WHERE title NOT LIKE 'VP%' 4 GROUP BY title 5 HAVING SUM(salary) > 5000 6 ORDER BY SUM (salary);

13. Пример Вывод номера отдела и средней заработной платы для отделов, где средняя заработная плата превышает 2000. SQL> SELECT dept_id,AVG(salary) 2 FROM s_emp 3 WHERE AVG (salary) >2000 4 GROUP BY dept_id; WHERE AVG(salary) >2000 * ERROR at line 3: ORA-00934:group function is not allowed here (Использование здесь групповой функции невозможно)

14. Вместо этого для ограничения количества групп следует использовать предложение HAVING. SQL> SELECT dept_id, AVG (salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING AVG(salary) >2000; DEPT_ID AVG(SALARY) ------- ----------- 50 2025

15. ВЛОЖЕННОСТЬ ГРУППОВЫХФУНКЦИЙ • Групповые функции могут быть вложены на глубину не более 2 уровней • Вложенные функции вычисляются от внутреннего уровня к внешнему • Предложение GROUP BY в запросе с вложенной групповой функцией обязательно в любом случае

16. ВЛОЖЕННОСТЬ ГРУППОВЫХФУНКЦИЙ: ПРИМЕР SQL> SELECT MAX(AVG(salary)) 2 FROM s_emp 3 GROUP BY title; Вычисление максимального среди средних размеров заработной платы по всем должностям

17. Заключение • Имеется семь групповых функций: AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE. • С помощью предложения GROUP BY создаются группы. • Некоторые группы исключаются с помощью предложения HAVING. SELECT column,group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column] ;

18. Аналитические функции • В версии СУБД Oracle 8.1.6 появился новый класс из 26 функций, названных аналитическими, и получившим дальнейшее развитие в версии 9. Их описания были созданы совместными усилиями фирм IBM, Informix, Oracle и Compaq путем разработки так называемых "улучшений" некоторых конструкций, имеющихся в стандарте SQL1999.

19. Сравнение с обычными функциями агрегирования • Многие аналитические функции действуют подобно обычным скалярным функциям агрегирования SUM, MAX и прочим, примененным к группам строк, сформированным с помощью GROUP BY. Однако обычные функции агрегирования уменьшают степень детализации, а аналитические функции нет. 

20. Поясняющий сравнительный пример • SELECT deptno, job, SUM(sal) sum_salFROM emp GROUP BY deptno, job; • SELECT ename, deptno, job,             SUM(sal) OVER (PARTITION BY deptno, job) sum_salFROM emp;

21. Результат первого запроса

22. Результат второго запроса

23. Разбиение данных на группы для вычислений • Аналитические функции агрегируют данные порциями (partitions; группами), количество и размер которых можно регулировать специальной синтаксической конструкцией. Ниже она указана на примере агрегирующей функции SUM: • SUM(выражение 1) OVER([PARTITION BY выражение 2 [, выражение 3 [, …]]])

24. Разбиение данных на группы для вычислений • Если PARTITION BY не указано, то в качестве единственной группы для вычислений будет взят полный набор строк: • SELECT ename, deptno, job, SUM(sal) OVER () sum_salFROM emp;

25. Результат последнего запроса

26. Упорядочение в границах отдельной группы • С помощью синтаксической конструкции ORDER BY строки в группах вычислений можно упорядочивать. Синтаксис иллюстрируется на примере агрегирующей функции SUM: SUM(выражение 1) OVER([PARTITION …] ORDER BY выражение 2 [,…] [{ASC|DESC}] [{NULLS FIRST|NULLS LAST}]) • Правила работы ORDER BY - как в обычных SQL-операторах. 

27. Пример • SELECT ename, deptno, job,SUM(sal) OVER (PARTITION BY deptno, job ORDER BY hiredate) sum_salFROM emp;

28. Результат

29. Выполнение вычислений для строк в группе по плавающему окну (интервалу) • Для некоторых аналитических функций, например, агрегирующих, можно дополнительно указать объем строк, участвующих в вычислении, выполняемом для каждой строки в группе. Этот объем, своего рода контекст строки, называется "окном", а границы окна могут задаваться различными способами.

30. Синтаксис • {ROWS | RANGE} {{UNBOUNDED | выражение} PRECEDING | CURRENT ROW } • {ROWS | RANGE} BETWEEN {{UNBOUNDED PRECEDING | CURRENT ROW | {UNBOUNDED | выражение 1}{PRECEDING | FOLLOWING}} AND {{UNBOUNDED FOLLOWING | CURRENT ROW | {UNBOUNDED | выражение 2}{PRECEDING | FOLLOWING}} 

31. Выполнение вычислений для строк в группе по плавающему окну (интервалу) • Фразы PRECEDING и FOLLOWING задают верхнюю и нижнюю границы агрегирования (то есть интервал строк, "окно" для агрегирования). • Вот поясняющий пример, воспроизводящий результат из предыдущего раздела: • SELECT ename, deptno, job,SUM(sal) OVER (PARTITION BY deptno, job ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_salFROM emp;

32. Результат

33. Выполнение вычислений для строк в группе по плавающему окну (интервалу) • Обратите внимание, что плавающий интервал задается в терминах упорядоченных строк (ROWS) или значений (RANGE), для чего фраза ORDER BY в определении группы обязана присутствовать.

34. Функции FIRST_VALUE и LAST_VALUE для интервалов агрегирования • Эти функции позволяют для каждой строки выдать первое значение ее окна и последнее.

35. Пример • SELECT ename, hiredate, sal,FIRST_VALUE(sal)OVER (ORDER BY hiredateROWS BETWEEN 2 PRECEDING AND CURRENT ROW) first_rows,LAST_VALUE(sal)OVER (ORDER BY hiredateROWS BETWEEN 2 PRECEDING AND CURRENT ROW) last_rows,FIRST_VALUE(sal)OVER (ORDER BY hiredateRANGE BETWEEN 2 PRECEDING AND CURRENT ROW) first_range,LAST_VALUE(sal)OVER (ORDER BY hiredateRANGE BETWEEN 2 PRECEDING AND CURRENT ROW) last_range FROM emp;

36. Результат

37. Интервалы времени • Для интервалов (окон), упорядоченных внутри по значению ("логическом", RANGE) в случае, если это значение имеет тип "дата", границы интервала можно указывать выражением над датой, а не конкретными значениями из строк. Примеры таких выражений: • INTERVAL число {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} • NUMTODSINTERVAL(число, '{DAY | HOUR | MINUTE | SECOND}') • NUMTOYMINTERVAL(число, '{YEAR | MONTH}')

38. Пример выдачи зарплат сотрудников и средних зарплат за последние полгода на момент приема нового сотрудника: • SELECT ename, hiredate, sal,AVG(sal)OVER (ORDER BY hiredateRANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND CURRENT ROW) avg_salFROM emp;

39. Результат

40. CORR (выражение, выражение) • Выдает коэффициент корреляции для пары выражений, возвращающих числовые значения. В статистическом смысле, корреляция — это степень связи между переменными. Связь между переменными означает, что значение одной переменной можно в определенной степени предсказать по значению другой. Коэффициент корреляции представляет степень корреляции в виде числа в диапазоне от -1 (высокая обратная корреляция) до 1 (высокая корреляция). Значение 0 соответствует отсутствию корреляции

41. COVAR_POP( выражение, выражение) • Возвращает ковариацию генеральной совокупности (population covariance) пары выражений с числовыми значениями.

42. COVAR_SAMP(выражение, выражение) • Возвращает выборочную ковариацию (sample covariance) пары выражений с числовыми значениями.

43. CUME_DIST • Вычисляет относительную позицию строки в группе.Функция CUME_DIST всегда возвращает число большее 0 и меньше или равное 1. Это число представляет "позицию" строки в группе из N строк. В группе из трех строк, например, возвращаются следующие значения кумулятивного распределения: 1/3, 2/3 и 3/3.

44. DENSE_RANK • Эта функция вычисляет относительный ранг каждой возвращаемой запросом строки по отношению к другим строкам, основываясь на значениях выражений в конструкции ORDER BY. Данные в группе сортируются в соответствии с конструкцией ORDER BY, а затем каждой строке поочередно присваивается числовой ранг, начиная с 1. Ранг увеличивается при каждом изменении значений выражений, входящих в конструкцию ORDER BY. Строки с одинаковыми значениями получают один и тот же ранг (при этом сравнении значения NULL считаются одинаковыми). Возвращаемый этой функцией "плотный" ранг дает ранговые значения без промежутков.

45. Источник • http://www.interface.ru/fset.asp?Url=/oracle/anal-itiv.htm