Rank (computer science)
In computer science, rank denotes an ascending order of objects.
use
When data aggregation for data warehouse applications the ranking function often is used. This function gives records from a selection a consecutive number.
The following variants of the rank function are used:
- Duplicates should be given the same rank or different ranks
- Partitioning means (in this context) that when a higher-level classification criterion is changed, the numbering starts all over again
example
The list of products should be output with a rank. Duplicates should not be taken into account.
The rank without partitioning is output in the RANK column.
The rank with partitioning is output in the PART_RANG column.
select
abteilung
, produkt_nr
, row_number() over(order by abteilung , produkt_nr) rang
, row_number() over(partition by abteilung order by produkt_nr) part_rang
from pr
order by abteilung, produkt_nr
;
ABTEILUNG PRODUKT_NR RANG PART_RANG
---------- ---------- ---------- ----------
1 2 1 1
1 3 2 2
1 3 3 3
1 3 4 4
1 4 5 5
1 7 6 6
1 9 7 7
2 1 8 1
2 3 9 2
2 3 10 3
The list of products should be output with a rank. This time, duplicates should have the same rank.
The DRANG column shows the rank without partitioning.
The rank with partitioning is output in the PART_DRANG column.
select
abteilung
, produkt_nr
, dense_rank() over(order by abteilung , produkt_nr) drang
, dense_rank() over(partition by abteilung order by produkt_nr) part_drang
from pr
order by abteilung, produkt_nr
;
ABTEILUNG PRODUKT_NR DRANG PART_DRANG
--------- ---------- ---------- ----------
1 2 1 1
1 3 2 2
1 3 2 2
1 3 2 2
1 4 3 3
1 7 4 4
1 9 5 5
2 1 6 1
2 3 7 2
2 3 7 2