Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Advanced Oracle SQL #4

Analytic functions. Basic syntax.
by

Rodion Mironov

on 30 November 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Advanced Oracle SQL #4

Problem Links
and practice Basic syntax for each employee show his salary and three average salaries: for all employees, for his job and for his department;
for each employee show number of people who had already been working in his department when he was hired.
for each employee show average salary of people hired in the same year;
for each employee show his hire_date, salary and average salary of five people: himself, two people hired right before him and two people hired right after;
for each employee show total salary of people hired 90 days before or after him. Analytic functions "Pro Oracle SQL", глава 8. http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#i97640 "Oracle для профессионалов" Тома Кайта, глава 12. Partitioning Ordering Sliding window Some common tasks can't be solved efficiently with traditional SQL. Example #1 For each employee show
his salary;
total salary of his department;
% of his salary in department's total. Example #2 For each employee show his salary rank in the department. Example #3 For each employee show average salary of people hired for the same position during previous year. Common features of such tasks:
kind of mix of individual and aggregated data;
all "aggregations" can be calculated from found individual data, but we can't address several rows in SQL and have to use subqueries. select
e.department_id
,e.last_name
,e.salary
,d.budget
,ROUND(e.salary/d.budget*100,2) as percent_in_budget
from employees e left join
(select department_id, sum(salary) as budget
from employees
group by department_id) d on e.department_id = d.department_id
order by e.department_id; select
e.department_id
,e.last_name
,e.salary
,(select sum(e1.salary)
from employees e1
where e1.department_id = e.department_id) as budget
,ROUND(e.salary/(select sum(e1.salary)
from employees e1
where e1.department_id = e.department_id)*100,2) as pct
from employees e
order by e.department_id; select
e.first_name || ' ' || e.last_name as Full_Name
,e.Department_ID
,e.Salary
,(select count(*)
from employees e1
where e1.department_id = e.department_id
and e1.salary > e.salary) + 1 as Salary_Rank
from employees e
order by e.Department_ID, Salary_Rank; select
e.job_id
,e.last_name
,e.hire_date
,e.salary
,(select avg(e1.salary)
from employees e1
where e1.job_id = e.job_id
and e1.hire_date between e.hire_date-366 and e.hire_date-1) as sliding_avg
from employees e
order by e.job_id, e.hire_date; Analytic functions compute result basing on the group of rows.
Difference with aggregate functions: analytic can return several rows per group. select
last_name
,salary
,sum(salary) over() as total_salary
from emp; select
last_name
,salary
,avg(salary) over() as avg_salary
,count(*) over() as total_emps
from emp; OVER() construction:
differentiate analytic function from aggregate function;
define set of rows analytic function operates on (in this case - all rows). Like GROUP BY for aggregate functions Aggregate functions:
compute totals for all rows without GROUP BY;
compute totals for several groups with GROUP BY;
Analytic functions:
operates on all rows without partitioning clause;
operates on groups of rows with partitioning clause. select
department_id
,last_name
,salary
,min(salary) over(partition by department_id) as min_dep_sal
,max(salary) over(partition by department_id) as max_dep_sal
from emp
order by department_id; select
department_id
,extract(month from hire_date) as month
,last_name
,salary
,sum(salary) over(partition by department_id,extract(month from hire_date)) as sum_salary
from emp
order by department_id, month; Much more features are available if we virtually sort rows by some criteria. select
department_id
,last_name
,salary
,rank() over(partition by department_id order by salary desc) as salary_rank
from emp
order by department_id, salary desc; select
last_name
,hire_date
,salary
,rank() over(order by salary desc) as salary_rank
,rank() over(order by hire_date) as workterm_rank
from emp
order by salary desc; When data set is ordered, we can ask questions about:
all rows "before" or "after" current;
several previous rows;
rows "around" current (several before and several after);
etc.
If we use ordering, default sliding windows is:
from the beginning of partition;
to current row (including current).
Beyond default, sliding windows can be specified in:
rows (several rows before and several after);
range (some range before/after/around current row, based on ordering criteria).
select
department_id
,last_name
,salary
,sum(salary) over(partition by department_id order by last_name)
as cum_salary
from emp
order by department_id; select
department_id
,last_name
,hire_date
,count(*) over(partition by department_id order by hire_date)
as cum_num_people
from emp
order by department_id; Row-based window select
department_id
,last_name
,hire_date
,salary
,avg(salary) over(partition by department_id
order by hire_date
rows between 1 preceding and 1 following )
as sliding_avg
from emp
order by department_id; Range-based window select
department_id
,last_name
,hire_date
,salary
,avg(salary) over(partition by department_id
order by hire_date
range between 15 preceding and 15 following ) as sliding_avg
from emp
order by department_id; Complete windows syntax
Full transcript