ORACLE – Using DENSE_RANK and KEEP to simplify multi-layer analytic queries

In queries, I commonly need to extract only the top of a sorted list of results. As time has progressed, ROW_NUMBER() and the OVER() clause have proven essential in writing simple and fast queries to meet this need. Below is an example. In this case, we have an table that captures unit pricing of products by product name and date. We want to write a function that simply returns a text version of the price for display and “NO PRICE FOUND” if nothing is available.

SQL> DESC unit_price
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_NAME                                       VARCHAR2(50)
 PRICE_DATE                                         DATE
 PRICE                                              NUMBER


SQL> SELECT *
  2  FROM unit_price
  3  ORDER BY product_name, price_date;

PRODUCT_NAME                                       PRICE_DAT      PRICE
-------------------------------------------------- --------- ----------
SCREWDRIVER                                        21-DEC-13       6.25
SCREWDRIVER                                        22-DEC-13       6.27
SCREWDRIVER                                        23-DEC-13        6.4
SHOVEL                                             21-DEC-13      10.48
SHOVEL                                             22-DEC-13      10.49
SHOVEL                                             23-DEC-13       10.6

6 rows selected.


SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2
  3    scredriver_price VARCHAR2( 50 );
  4    wrench_price VARCHAR2( 50 );
  5
  6    FUNCTION get_latest_price( prodname VARCHAR2 )
  7    RETURN VARCHAR2
  8    IS
  9      latest_price VARCHAR2( 50 );
 10    BEGIN
 11
 12      SELECT TO_CHAR( price )
 13      INTO latest_price
 14      FROM
 15      (
 16        SELECT
 17          price,
 18          ROW_NUMBER() OVER ( ORDER BY price_date DESC ) rn
 19        FROM unit_price
 20        WHERE product_name = prodname
 21      )
 22      WHERE rn = 1;
 23
 24      RETURN latest_price;
 25
 26    EXCEPTION
 27      WHEN no_data_found THEN
 28        RETURN 'NO PRICE FOUND';
 29    END;
 30
 31  BEGIN
 32
 33    scredriver_price := get_latest_price( 'SCREWDRIVER' );
 34    DBMS_OUTPUT.PUT_LINE( 'SCREWDRIVER PRICE: ' || scredriver_price );
 35
 36    wrench_price := get_latest_price( 'WRENCH' );
 37    DBMS_OUTPUT.PUT_LINE( 'WRENCH PRICE: ' || wrench_price );
 38
 39  END;
 40  /
SCREWDRIVER PRICE: 6.4
WRENCH PRICE: NO PRICE FOUND

PL/SQL procedure successfully completed.

The get_latest_price() function does a good job of quickly extracting the latest available price for the passed product. You might argue that this is the most readable approach to extracting the required data. However, there are two points of get_latest_price() that make it slightly complicated:

1) The query that has two layers: an inner query to extract a list of of prices and their associated row numbers ordered by date, and an outer query to select only the first row of the inner query.
2) Since a NO_DATA_FOUND is an expected condition, an EXCEPTION block is needed to handle the return.

Below is an example of using the KEEP and DENSE_RANK analytic functions to achieve the same behavior with a single-layer query and no EXCEPTION block. Depending on your personal preference, you may not find this version easier to read. I also do not believe there is any cost/benefit in terms of speed.

SQL> DECLARE
  2
  3    scredriver_price VARCHAR2( 50 );
  4    wrench_price VARCHAR2( 50 );
  5
  6    FUNCTION get_latest_price( prodname VARCHAR2 )
  7    RETURN VARCHAR2
  8    IS
  9      latest_price VARCHAR2( 50 );
 10    BEGIN
 11
 12      SELECT NVL( TO_CHAR( MAX( price ) KEEP ( DENSE_RANK FIRST ORDER BY price_date DESC ) ), 'NO PRICE FOUND' )
 13      INTO latest_price
 14      FROM unit_price
 15      WHERE product_name = prodname;
 16
 17      RETURN latest_price;
 18
 19    END;
 20
 21  BEGIN
 22
 23    scredriver_price := get_latest_price( 'SCREWDRIVER' );
 24    DBMS_OUTPUT.PUT_LINE( 'SCREWDRIVER PRICE: ' || scredriver_price );
 25
 26    wrench_price := get_latest_price( 'WRENCH' );
 27    DBMS_OUTPUT.PUT_LINE( 'WRENCH PRICE: ' || wrench_price );
 28
 29  END;
 30  /
SCREWDRIVER PRICE: 6.4
WRENCH PRICE: NO PRICE FOUND

PL/SQL procedure successfully completed.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s