Oracle PL/SQL Aggregation – Rank
One of the more interesting aggregation functions is RANK. After doing a standard SELECT and GROUP BY; you can then have SQL rank the results using an aggregated value from another column.
Let’s dive in and look at how RANK works and what can it do for us.
Rank
When using the RANK you will need to specify which column you want to RANK on.
|
1 2 3 4 5 6 7 8 9 |
SELECT cust_vendor_dim.name ,SUM(EXTCOST) AS sum_extcost ,RANK() OVER (ORDER BY SUM(EXTCOST) DESC) AS rank_Customer FROM inventory_fact INNER JOIN cust_vendor_dim ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey WHERE transtypekey = 5 -- 'Sales Order Shipment' GROUP BY cust_vendor_dim.name ; |
Here we will rank the cust_vendor_dim.name by the SUM of the extcost column. In addition the results will be returned in DESC order. DESC means descending order, highest value listed first. We will still need to use the GROUP BY operator to group by cust_vendor_dim.name.
Rank Partition
One of the options we can do with RANK is to partition the ranking results. In other words when there is a change in value for a specified column, the ranking will restart.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT cust_vendor_dim.state ,cust_vendor_dim.name ,SUM(EXTCOST) AS sum_extcost ,RANK() OVER ( PARTITION BY cust_vendor_dim.state ORDER BY SUM(EXTCOST) DESC) AS rank_Customer FROM inventory_fact INNER JOIN cust_vendor_dim ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey WHERE transtypekey = 5 -- 'Sales Order Shipment' GROUP BY cust_vendor_dim.state ,cust_vendor_dim.name ORDER BY cust_vendor_dim.state ; |
In this example the query will partition by cust_vendor_dim.state, therefore when the previous rows value in cust_vendor_dim.state is different from the current rows value the ranking will restart with 1. The query needs to use the ORDER BY operator to get the correct results.
Dense_Rank
A variation of RANK is DENSE_RANK, the primary difference is that DENSE_RANK will not skip sequential order of numbers when two rows have the same value. DENSE_RANK will show them as having the same rank and the next row will have the next sequential number.
An example would be DENSE_RANK values of 1,2,3,3,3,4,5,5,6. Where RANK would show the same result as 1,2,3,3,3,6,7,7,9
|
1 2 3 4 5 6 7 8 9 10 |
SELECT cust_vendor_dim.name ,COUNT(inventorykey) ,RANK() OVER (ORDER BY COUNT(inventorykey) DESC) AS rank_Customer ,DENSE_RANK() OVER (ORDER BY COUNT(inventorykey) DESC) AS dense_rank_Customer FROM inventory_fact INNER JOIN cust_vendor_dim ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey WHERE transtypekey = 5 -- 'Sales Order Shipment' GROUP BY cust_vendor_dim.name ; |
Cumulative Sum
One of the most common tasks in grouping is to show a cumulative sum for the column values. with using aggregate SUM with the OVER option you can create a cumulative sum for the specified grouping levels.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT zip ,calyear ,calmonth ,SUM(EXTCOST) AS sum_extcost ,SUM( SUM(EXTCOST)) OVER(ORDER BY zip,calyear,calmonth ROWS UNBOUNDED PRECEDING ) AS cumulative_extcost FROM inventory_fact INNER JOIN date_dim ON inventory_fact.datekey = date_dim.datekey INNER JOIN cust_vendor_dim ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey WHERE transtypekey = 5 -- 'Sales Order Shipment' GROUP BY zip ,calyear ,calmonth ; |
In the example above query will produce a cumulative sum for each group of zip, calyear, calmonth. The ROWS option specifies to look at all preceding rows.
I hope you found the above informative, let me know if you have any questions in the comments below.
— michael.data@eipsoftware.com
Leave a Reply