Oracle Analytic Functions III: RANK() & DENSE_RANK()

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

Result:

BLOG_0028_PIC01_Example_RANK_DENSE_RANK

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;

Result:

BLOG_0028_PIC02_Example_RANK_PARTITION_BY

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:

BLOG_0028_PIC03_Task1_Result

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)!

Download

BLOG_0028_RANK_DENSE_RANK_Examples_TASK.zip

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *