Make your own free website on Tripod.com

Online analytical processing (OLAP) functions work on a set of rows. Most aggregate functions (like SUM, COUNT, AVG, MIN, MAX, STDEV) can be can be extended as OLAP functions by adding the OVER clause. These functions and new ranking functions behave as if they were scalar functions. That is, there is one result per row that it calculated based on the set of rows.

It is obvious that OLAP can reduce complexity.  Since I have no work experience with OLAP, I will defer to the following examples.  I found them in an article by

Bob Lyle
IBM Corporation

at http://www7b.boulder.ibm.com/dmdd/library/techarticle/lyle/0110lyle.html.

 select dept, salary, 
        sum(salary) over (partition by dept) as deptsum,
        avg(salary) over (partition by dept) as avgsal,
        count(*) over (partition by dept) as deptcount,
        max(salary) over (partition by dept) as maxsal 
  from emptab;

 DEPT  SALARY  DEPTSUM  AVGSAL  DEPTCOUNT MAXSAL 
 ----- ------- -------- ------- --------- --------
     1   50000 383000   63833         6    78000
     1   75000 383000   63833         6    78000
     1   52000 383000   63833         6    78000
     1   78000 383000   63833         6    78000
     1   75000 383000   63833         6    78000
     1   53000 383000   63833         6    78000
     2       -  51000   51000         2    51000
     2   51000  51000   51000         2    51000
     3   79000 209000   69666         3    79000
     3   55000 209000   69666         3    79000
     3   75000 209000   69666         3    79000
     -       -  84000   84000         2    84000
     -   84000  84000   84000         2    84000

Example showing the SUM reporting function

 select empnum, dept, salary,
 rank() over (partition by dept
       order by salary desc nulls last) as rank,
 dense_rank() over (partition by dept order by salary desc nulls last)as denserank,
 row_number() over (partition by dept order by salary desc nulls last)as rownumber
from emptab;

 EMPNUM  DEPT SALARY  RANK  DENSERANK   ROWNUMBER
 ------  ---- ------  ----  ---------   ---------
 6       1    78000   1     1           1
 2       1    75000   2     2           2
 7       1    75000   2     2           3
 11      1    53000   4     3           4
 5       1    53000   5     4           5
 1       1    50000   6     5           6
--------------------------------------------------
 9       2    51000   1     1           1
 4       2       -    2     2           2
 

Examples showing ranking