II.3.5. Агрегатные и аналитические функции

Выше мы уже ознакомились с некоторыми агрегатными функциями (max(), min(), avg()). Общее число агрегатных и аналитических функций в Oracle10g - более 50. Аналитические функции, позволяют производить ранжирование (ranking), перенос сводных результатов (moving aggregates), сравнение данных за различные периоды (period comparisons), соотношение итогов (ratio of total), получение совокупных сводных результатов (cumulative aggregates) и другие действия.

Q3_25 Продемонстрируем формирование агрегатных данных опцией ROLLUP

ROLLUP обеспечивает агрегирование на каждом уровне, заданном столбцами в GROUP BY. Это приводит к получению информации, которую нельзя было получить раньше без дополнительного кодирования. Следующий запрос извлекает номер отдела, код должности, сгруппированные по этим двум столбцам суммы зарплат, но также и промежуточные итоги - общую сумму зарплаты по отделам.

demo@10g> select department_id,job_id,sum(salary) "Total SAL"

from EMPLOYEE

group by rollup (department_id,job_id);

Поменяем порядок столбцов в опции ROLLUP:

demo@10g> select job_id,department_id, sum(salary) "Total SAL"

from EMPLOYEE

group by rollup (job_id, department_id);

Отформатируем вывод:

demo@10g> col Department format a20

demo@10g> break on Department

demo@10g> select nvl(to_char(department_id),'Whole Company') Department,

decode(job_id,NULL,'All Employees') job,sum(salary) "Total SAL"

from EMPLOYEE

group by rollup (department_id,job_id);

Q3_26 Использование GROUP BY с опцией CUBE

В дополнение к групповым подытогам и общим итогам, созданным ROLLUP, CUBE автоматически вычисляет все возможные комбинации возможных подытогов.

Выведем столбцы: код должности, номер отдела, суммарная зарплата, а также идентификаторы группирования по этим столбцам.

demo@10g> select job_id, department_id, sum(salary),

grouping(department_id), grouping(job_id)

from EMPLOYEE

group by cube(job_id,department_id);

Наличие «1» в столбце grouping(department_id) означает, что группирование проводится по номерам отделов – сумма зарплаты подсчитывается и выводится для строк с одинаковым номером отдела, «1» в столбце grouping(job_id) – по коду работы. Если в обоих столбцах значения «0», подсчитывается сумма по зарплате для этого кода работы и номера отдела.

Q3_27 Получим имя работника, департамент и заработную плату; также требуется получить общую сумму заработной платы по департаментам и процент заработной платы конкретного служащего в сумме департамента и общей сумме (например, работник Х в департаменте Y получает 10% заработной платы от суммы заработной платы его департамента и 1% от заработной платы компании) /4/.

demo@10g>

elect employee.department_id,

employee.last_name,

employee.salary,

sum(employee4.salary) cum_salary,

round(100*employee.salary/employee2.salary_by_dept,1) pct_dept,

round(100*employee.salary/employee3.salary_overall,1) pct_overall

from employee,

(select department_id, sum(salary) salary_by_dept

from employee

group by department_id ) employee2,

(select sum(salary) salary_overall

from employee ) employee3,

employee employee4

where employee.department_id = employee2.department_id

and employee.department_id = employee4.department_id

and (employee.salary > employee4.salary or

(employee.salary = employee4.salary and employee.last_name >= employee4.last_name))

group by employee.department_id, employee.last_name, employee.salary,

round(100*employee.salary/employee2.salary_by_dept,1),

round(100*employee.salary/employee3.salary_overall,1)

order by department_id, salary;

Теперь отработаем этот запрос с помощью аналитической функции. Сначала отформатируем столбцы,

demo@10g> column pct_dept format 99.9

demo@10g> column pct_overall format 99.9

demo@10g> break on deptno skip 1

а теперь сам запрос:

demo@10g> select department_id, last_name, salary,

sum(salary) over (partition by department_id order by salary,last_name) cum_sal,

round(100*ratio_to_report(salary) over (partition by department_id), 1 ) pct_dept,

round(100*ratio_to_report(salary) over () , 1 ) pct_overall

from employee

order by department_id, salary;