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