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

select first_name||' '|| last_name name,job.job_name,dept.name as "Отдел",loc.loc_name, round(salary,0) salary,

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

inner join department dept on emp.department_id=dept.department_id inner join location loc on dept.location_id=loc.location_id

Для вывода всех данных, требуется увеличить число строк для вывода.

4.  Выполните запрос:

SELECT manager_id, last_name, hire_date, COUNT(*) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE NUMTODSINTERVAL(30, 'DAY') PRECEDING) AS t_count

FROM employee WHERE manager_id is NOT NULL;

Для задания интервала в запросе используется опция «RANGE NUMTODSINTERVAL(100, 'DAY') PRECEDING». Здесь «RANGE» диапазон, NUMTODSINTERVAL(30, 'DAY') – интервал в 30 дней, «preceding» означает разницу даты приема на работу (HIRE_DATE – в нашем запросе) очередного извлекаемого запросом сотрудника с датой первого принятого в этом временном интервале на работу сотрудника (в группе сотрудников под управлением «своего» менеджера – ведь мы используем «PARTITION BY manager_id»). Если в группе сотрудников, подчиненных одному менеджеру, дата приема на работу очередного извлекаемого запросом сотрудника отличается от даты первого принятого на работу под управлением этого менеджера сотрудника более, чем на тридцать дней, отсчет количества принятых на работу сотрудников вновь начинается с единицы.

5.  Выполните запрос:

SELECT dept.name "Отдел", job.job_name "Должность", SUM(salary) AS "Зарплата итого" FROM employee emp

,department dept,job

where emp.department_id=dept.department_id 

and emp.job_id=job.job_id

GROUP BY ROLLUP (dept.name,job.job_name);

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

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

SELECT  dept.name "Отдел",job.job_name "Должность",  SUM(salary), GROUPING(dept.name), GROUPING(job.job_name ) FROM employee emp,

department dept,job

where emp.department_id=dept.department_id 

and emp.job_id=job.job_id

GROUP BY CUBE(dept.name,job.job_name );

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

7.  Выполните данное задание, при помощи агрегатных функций используя запрос:

SELECT employee.department_id, employee.last_name, employee.salary, sum(employee4.salary) cum_salary , round(100*employee.salary/employee2.salary_by_dept,1) AS pct_dept , round(100*employee.salary/employee3.salary_overall,1) AS pct_overall FROM employee , (SELECT department_id, sum(salary) AS salary_by_dept FROM employee GROUP BY department_id ) employee2 , (SELECT sum(salary) AS 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)