Tuesday, June 20

Oracle analytical functions

Recently I've found myself using the analytical functions in Oracle more and more. Not really to do in depth analysis, but just to do normal queries.
The analytical functions give you a bigger perspective to work with in a query, you can access more than the current row in a query. Compare it to a "row level aggregator". Things usually done by a group by expression can be done straight in the select clause. You can access the previous rows and following rows in the resultset to compute things.
Very handy indeed.

Quick example using the very basic functions of analytics.
select 
distinct(deptno),
(count(deptno)over(partition by deptno)) /
(count(deptno)over())*100 dshare
from
emp
where
deptno is not null
order
by deptno
/
DEPTNO DSHARE
---------- ------
10 21
20 36
30 43
This query does two things and divide the two things to get the size of the deptartment in percent. first it finds the size of the current department, then the total head count (excluding top dog). Notice how both functions look at a bigger window than the current row without aggregating the data.

2 comments:

David Aldridge said...

This makes more sense to me, Hali:

Select deptno, ratio_to_report(num_emp)*100 over () pcnt
from (
select deptno,
count(*) num_emp
from emp
group by deptno
)
/

The inner query builds a view of department and number of employees, then the parent level uses ratio_to_report() to give the percents of total.mftmlbi2

David Aldridge said...

Oops, move that *100 after the over() ...

ratio_to_report(num_emp) over () *100 pcnt