ГРУППОВЫЕ ФУНКЦИИ. Групповые функции Групповые функции работают с множествами строк и возвращают один результат на группу. Групповые функции могут быть. - презентация

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

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

3 Предложения GROUP BY и HAVING команде SELECT: синтаксис SELECTcolumn, group_function FROM table [WHEREcondition] [GROUP BYgroup_by_expression] [HAVINGgroup_condition] [ORDER BYcolumn]; Предложение 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.

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 ORD" title="Предложение 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 ORD" class="link_thumb"> 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) > ORDER BY SUM (salary); 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 ORD"> 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);"> 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 ORD" title="Предложение 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 ORD">

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

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

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. SELECTcolumn, group_function FROM table [WHEREcondition] [GROUP BYgroup_by_expression] [HAVINGgroup_condition] [ORDER BYcolumn] ;

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

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

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

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

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

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

24 Разбиение данных на группы для вычислений Если PARTITION BY не указано, то в качестве единственной группы для вычислений будет взят полный набор строк: SELECT ename, deptno, job, SUM(sal) OVER () sum_sal FROM 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_sal FROM 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_sal FROM emp;

32 Результат

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

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

35 Пример SELECT ename, hiredate, sal, FIRST_VALUE(sal) OVER (ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) first_rows, LAST_VALUE(sal) OVER (ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) last_rows, FIRST_VALUE(sal) OVER (ORDER BY hiredate RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) first_range, LAST_VALUE(sal) OVER (ORDER BY hiredate RANGE 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 hiredate RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND CURRENT ROW) avg_sal FROM 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 Источник /oracle/anal-itiv.htm