Working with OLAP functions

Consider a scenario where we have a sales table that holds sales data for different regions, and we want to rank all regions based on their total sales. We might also want to rank them based on their contribution to the total profit. This can certainly be achieved by writing complex SQL statements. Let's see what DB2 offers in such situations. DB2 provides a rich set of Online Analytical Processing (OLAP) functions that provide us with the ability to perform complex ordering and aggregation of result sets, in a very simple manner. OLAP functions do not perform any kind of filtering, and act on every row in the result set. They are always applied on a window. We can also use scalar functions and aggregate functions over OLAP windows. In this recipe, we will discuss how we can use the different OLAP functions provided by DB2.

Getting ready

This recipe uses the T_SALES table as an example. The sample table and data can be created by the following set of SQL statements...