Analysis of OLAP functions in DB2 (1)

OLAP is an important function in DB2 that extends the relational model and enables the relational model to understand the sorting methods in the row set. This article will analyze OLAP functions in detail, the related instances are provided for your reference.

Sort functions: the first class of OLAP functions introduced into DB2 are sort ranking functions, which are introduced in DB2 Version 6. These permutation functions provide the ability to define a set using the PARTITION clause), and then sort the elements in the Set in a certain sort method. For example, if we have an employee table, we need to sort the salaries of employees in each department. To achieve this, we need a function call, which can do the following:

Partition set) is defined as each department, and the sorting method in the set is defined as sort by salary. By convention, we usually place a higher salary first, so we will specify a descending sort of salary. The following example shows the query and output of this query.
Select empnum, dept, salary, rank () over (partition by dept order by salary desc nulls last) as rank, dense_rank () over (partition by dept order by salseary desc nulls last) as denserank, row_number () over (partition by dept order by salary desc nulls last) as rownumber from emptab;

EMPNUM DEPT SALARY RANK DENSERANK ROWNUMBER
--------------------------------------
6 1 78000 1 1 1
2 1 75000 2 2 2
7 1 75000 2 2 3
11 1 53000 4 3 4
5 1 52000 5 4 5
1 50000 6 5 6
--------------------------------------------------
9 2 51000 1 1 1
4 2-2 2 2