SQL Getting Started


OLAP Functions

OnLine Analytical Processing (OLAP) functions perform a column function operation over a window of data. This window may specify a partitioning of rows, an ordering of rows within partitions, or an aggregation group. The aggregation group allows the user to specify which rows, relative to the current row, participate in the calculation. The use of such a window allows operations like cumulative sums and moving averages.

In addition to allowing the user to specify a window for existing column functions (such as SUM and AVG), OLAP functions can perform ranking (RANK and DENSE_RANK) operations, and provide row numbering (ROW_NUMBER), given a specific partitioning and ordering of rows.

The following example query gives an employee's rank within the department, based on salary, and shows the cumulative sum of the salaries within the department (for departments 15 and 38):

     SELECT NAME, DEPT,
            RANK () OVER (PARTITION BY DEPT ORDER BY SALARY DESC) AS RANK,
            SUM (SALARY) OVER (PARTITION BY DEPT
                               ORDER BY SALARY DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                AS CUMULATIVE_SUM
        FROM STAFF
        WHERE DEPT IN (15,38)
        ORDER BY DEPT, RANK

This statement produces the following result:

     NAME       DEPT      RANK       CUMULATIVE_SUM                 
     ---------- --------- ---------- --------------------
     Hanes      15                 1             20659.80                
     Rothman    15                 2             37162.63            
     Ngan       15                 3             49670.83              
     Kermisch   15                 4             61929.33             
     O'Brien    38                 1             18006.00
     Marenghi   38                 2             35512.75
     Quigley    38                 3             52321.05
     Naughton   38                 4             65275.80
     Abrahams   38                 5             77285.55


[ Top of Page | Previous Page | Next Page ]