Агрегатные и аналитические функции. Создание других объектов базы данных (Лабораторная работа № 4)

Лабораторная работа №4

Тема: Агрегатные и аналитические функции. Создание других объектов базы данных.

Цель: Научиться использовать агрегатные и аналитические функции.

Итоговый отчёт: Должен содержать номер задания, текст задания и результат выполнения задания.

Часть 1

Цель: Научиться использовать аналитические и агрегатные функции.

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

Задания:

1.  Найдите суммарную, минимальную, максимальную и среднюю зарплату для каждого отдела при помощи агрегатных функций.

2.  Используя аналитическую функцию, найдите 5 сотрудников с максимальным окладом и 5 с минимальным.

3.  Получите список сотрудников с рангом по зарплате, начиная от максимальной зарплаты для каждого отдела.

4.  Определить, подразделив отчет по группам сотрудников, подчиняющихся своим менеджерам (PARTITION BY manager_id), нарастающим итогом количество сотрудников, принятых на работу в заданном временном интервале (30 дней), отсчитываемом от даты принятия на работу под управлением «своего» менеджера первого сотрудника.

5.  Сформируйте отчёт по зарплате сотрудников, по отделу, по должностям и итоговую зарплату при помощи опции ROLLUP.

6.  Вычисляет все возможные комбинации возможных подитогов суммарной зарплаты отделов и должностей используя GROUP BY с опцией CUBE.

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

8.  Выполните предыдущее задание, используя аналитическую функции.

Технология работы:

1.   

GROUP BY позволяет сгруппировать данные по определённым полям, и получить итог при помощи агрегатной функции.

В опции GROUP BY надо указывать все столбцы (кроме столбца sum(salary), конечно, по которому ищется сумма), составляющие выводимые строки. Выражение Group by ставится после условий WHERE, если в запросе есть фильтры.

Найдите суммарные затраты на зарплату сотрудников по каждому отделу при помощи запроса:

select dept.name "Отдел", sum(salary) "Зарплата" from employee emp inner join department dept on emp.department_id=dept.department_id

group by dept.name

order by sum(salary) desc

Найдите минимальные зарплаты сотрудников по каждому отделу при помощи запроса:

select dept.name "Отдел", min(salary) "Мин. зарплата" from employee emp inner join department dept on emp.department_id=dept.department_id

group by dept.name

order by min(salary) desc

Найдите максимальные зарплаты сотрудников по каждому отделу при помощи запроса:

select dept.name "Отдел", max(salary) "Макс. зарплата" from employee emp inner join department dept on emp.department_id=dept.department_id

group by dept.name

order by max(salary) desc

Найдите средние зарплаты сотрудников по каждому отделу, требуется вывести результат, только по отделам, у которых средняя зарплата больше 40 000 рублей, при помощи запроса:

select dept.name "Отдел", avg(salary) "Сред. зарплата" from employee emp inner join department dept on emp.department_id=dept.department_id

group by dept.name

having avg(salary)>=40000

order by avg(salary) desc;

Выражение having avg(salary)>=40000 позволяет отфильтровать данные по результатам агрегирования.

2.  Для нахождения 5 сотрудников с максимальной зарплатой выполните запрос:

select *

from

(

select first_name||' '|| last_name name,job.job_name, round(salary,0) salary,

rank() over (order by salary desc) salary_rank from employee emp inner join job on emp.job_id=job.job_id

) t

where t.salary_rank<=5

rank() over (order by salary desc) – аналитическая функция, вычисляющая ранг записи исходя из значения поля salary. Desc позволяет отсортировать записи по убыванию, от максимальной к минимальной зарплате.

Для нахождения 5 сотрудников с минимальной зарплатой требуется изменить порядок сортировки на asc rank() over (order by salary asc)  выполните запрос:

select *

from

(

select first_name||' '|| last_name name,job.job_name, round(salary,0) salary,

rank() over (order by salary asc) salary_rank from employee emp inner join job on emp.job_id=job.job_id

) t

where t.salary_rank<=5

3.  Соединим таблицу employee с таблицей department, которую соединим с location и при пощи функции rank()  с группировкой данных по номеру отделов (partition by emp.department_id) получим итоговые данные. Выполните запрос :