Today I’ve prepared a lesson in Oracle analytic functions. Basicly these functions are an enhancement of the aggregation functions like SUM, MAX, MIN, etc. With the help of this enhancement it’s possible to calculate different aggregations within the same query. With a classic group by you can only have one aggregation per query. With analytic functions you can have more than this. It’s also possible to calculate windows, i.e. to access rows before or after the current row which gives you the ability to create dynamic window sums, etc. This basic functionality is described in part 1 and part 2 of this tutorial. Beside these basics functions there are a lot of additional functions in the database.
Today I want to demonstrate the use of the functions RANK() and DENSE_RANK(). These are used to calculate rankings in lists. The ranking depends on the sorting order which you can define within each calculation, i.e. you can create one ranking based on turnover descending and another on turnover ascending within the same query. As with all analytical functions you can also use partitioning with the ranking functions. This gives you the ability to start a new ranking for each partition. We will see that in the example. Basicly RANK() and DENSE_RANK() work in the same way but the difference lays in the way how they deal with rows that have the same ranking. If you have two rows with the same turnover they receive the same rank. The function RANK() then skips the next rank and continues with the following one. In comparision with that DENSE_RANK() continues with the next rank. Here comes the first example to demonstrate the basic stuff. This example is based on the TBL_TEST table (it consists of four columns: DAY, PRODUCT_NO, PRODUCT_NAME, TURNOVER. See also the example files):
|SELECT x.*, RANK() OVER(ORDER BY PRODUCT_NO) AS RANK_NO, DENSE_RANK() OVER(ORDER BY PRODUCT_NO) AS DRANK_NO FROM ( SELECT to_char(DAY,’YYYY-MM’) AS MONTH, PRODUCT_NO, SUM(TURNOVER) as TURNOVER FROM TBL_TEST GROUP BY to_char(DAY,’YYYY-MM’), PRODUCT_NO ) x|
This example works with a sub-query to aggregate the data to a month based level. Then in the main query RANK() and DENSE_RANK() are used to calculate a ranking based on the product-no column. For that PRODUCT_NO appears in the ORDER BY of the RANK and DENSE_RANK functions. In the results you can see that the first four rows have the same rank. The function RANK() continues with rank 5 then but DENSE_RANK with 2. That’s the difference between these two functions when rows have the same rank.
I’ve mentioned that it’s possible to create a new ranking which each partition. The next example deals with that:
|SELECT x.*, RANK() OVER(PARTITION BY MONTH ORDER BY PRODUCT_NO) AS RANK_NO FROM ( SELECT to_char(DAY,’YYYY-MM’) AS MONTH, PRODUCT_NO, SUM(TURNOVER) as TURNOVER FROM TBL_TEST GROUP BY to_char(DAY,’YYYY-MM’), PRODUCT_NO ) x;|
Again we have a sub-query to aggregate daily values in the table to monthly values. In the main query we just added a PARTITION BY MONTH to the definition of the RANK_NO column and that changes the list in that way that we have a ranking per month now.
To finish todays article I’ve created a small task. The solution can be found in the download area:
Produce a list with products, days and turnover. Create a ranking based on the turnover (of product and day). The data should be filtered to January 2017. And in the result the 3 top days should be displayed. The list should look like this one:
That’s it for RANK and DENSE_RANK. I hope you enjoyed today’s lesson.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!