OLAP IN SQL

OLAP stands for Online Analytical Processing, a new feature in SQL that is being implemented in database products by various vendors. OLAP SQL is typically used in data warehouses. OLAP differs from OLTP which is Online Transaction Processing used in transaction systems. The key differences are:

OLTP:	OLAP:
Data changes often	Data is static
Current data stored	Stores historic data
Few indexes	Many indexes to speed up complex queries
Fast and optimized	Accurate and complete
Highly normalized	Highly denormalized

There are several OLAP SQL functions. This post will discuss the RANK function, aggregate and analytical. The RANK function calculates the rank of a value in a group of values and returns a number. An example is shown below:

SELECT RANK(45500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Rank"
FROM employees;

Rank
----------
55

As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_expression in the order_by_clause. Here is an example of an analytical RANK:

SELECT department_id, last_name, salary, commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) "Rank"
FROM employees WHERE department_id = 80;

DEPARTMENT_ID	LAST_NAME	SALARY	COMMISSION_PCT	Rank
80	Brown	14000	.4	1
80	Lewis	13500	.3	2
80	Smith	12000	.3	3
80	Carlson	11500	.25	4
80	Chambers	11000	.3	5
80	Adams	11000	.3	5

There is another version of the RANK function, called DENSE RANK which computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

The following example computes the ranking of a hypothetical employee with the salary $45,500 and a commission of 5%.

SELECT DENSE_RANK(45500, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) "Dense Rank"
FROM employees;

Dense Rank
-------------------
3

There it is the OLAP RANK SQL. I'll discuss other OLAP SQL functions in future posts.