OLAP functions are similar to aggregate functions except that the aggregate functions will return only one value whereas the OLAP function will provide the individual rows in addition to the aggregates.
Syntax
Following is the general syntax of the OLAP function.
<aggregate function> OVER ([PARTITION BY] [ORDER BY columnname][ROWS BETWEEN UNBOUDED PRECEDING AND UNBOUNDED FOLLOWING)
Aggregation functions can be SUM, COUNT, MAX,MIN, AVG.
Example
Consider the following Salary table.
| EmployeeNo | Gross | Deduction | NetPay |
|---|---|---|---|
| 101 | 40,000 | 4,000 | 36,000 |
| 102 | 80,000 | 6,000 | 74,000 |
| 103 | 90,000 | 7,000 | 83,000 |
| 104 | 75,000 | 5,000 | 70,000 |
Following is an example to find the cumulative sum or running total of NetPay on Salary table. Records are sorted by EmployeeNo and cumulative sum is calculated on NetPay column.
SELECT EmployeeNo, NetPay, SUM(Netpay) OVER(ORDER BY EmployeeNo ROWS UNBOUNDED PRECEDING) as TotalSalary FROM Salary;
When the above query is executed, it produces the following output.
EmployeeNo NetPay TotalSalary ----------- ----------- ----------- 101 36000 36000 102 74000 110000 103 83000 193000 104 70000 263000 105 18000 281000
RANK
RANK function orders the records based on the column provided. RANK function can also filter the number of records returned based on the rank.
Syntax
Following is the generic syntax to use the RANK function.
RANK() OVER ([PARTITION BY columnnlist] [ORDER BY columnlist][DESC|ASC])
Example
Consider the following Employee table.
| EmployeeNo | FirstName | LastName | JoinedDate | DepartmentID | BirthDate |
|---|---|---|---|---|---|
| 101 | Mike | James | 3/27/2005 | 1 | 1/5/1980 |
| 102 | Robert | Williams | 4/25/2007 | 2 | 3/5/1983 |
| 103 | Peter | Paul | 3/21/2007 | 2 | 4/1/1983 |
| 104 | Alex | Stuart | 2/1/2008 | 2 | 11/6/1984 |
| 105 | Robert | James | 1/4/2008 | 3 | 12/1/1984 |
Following query orders the records of the employee table by Joined Date and assigns the ranking on Joined Date.
SELECT EmployeeNo, JoinedDate,RANK() OVER(ORDER BY JoinedDate) as Seniority FROM Employee;
When the above query is executed, it produces the following output.
EmployeeNo JoinedDate Seniority ----------- ---------- ----------- 101 2005-03-27 1 103 2007-03-21 2 102 2007-04-25 3 105 2008-01-04 4 104 2008-02-01 5
PARTITION BY clause groups the data by the columns defined in the PARTITION BY clause and performs the OLAP function within each group. Following is an example of the query that uses PARTITION BY clause.
SELECT EmployeeNo, JoinedDate,RANK() OVER(PARTITION BY DeparmentNo ORDER BY JoinedDate) as Seniority FROM Employee;
When the above query is executed, it produces the following output. You can see that the Rank is reset for each Department.
EmployeeNo DepartmentNo JoinedDate Seniority
----------- ------------ ---------- -----------
101 1 2005-03-27 1
103 2 2007-03-21 1
102 2 2007-04-25 2
104 2 2008-02-01 3
105 3 2008-01-04 1