Saturday 26 January 2008

RANK, DENSE_RANK AND ROW_NUMBER analytic functions

I wrote this test to better understand the use of RANK, DENSE_RANK and ROW_NUMBER Oracle analytic functions and Top-N querying. The beauty of this exercise is that all 3 analytic functions are illustrated with one example.


Suppose the business requirement in this case is to rank the products of each customer by the quantity purchased.

The test table I used for this is:

CREATE TABLE t
(
customer_name VARCHAR2(50),
product_name VARCHAR2(50),
hits NUMBER
)

The data in the table is 16 rows like below:

INSERT INTO t VALUES('bill', 'egg', 1);
INSERT INTO t VALUES('bill', 'egg', 1);
INSERT INTO t VALUES('bill', 'beer', 3);
INSERT INTO t VALUES('bill', 'beer', 2);
INSERT INTO t VALUES('bill', 'jeans', 1);
INSERT INTO t VALUES('larry', 'beer', 4);
INSERT INTO t VALUES('larry', 'beer', 2);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'olives', 1);
INSERT INTO t VALUES('larry', 'olives', 1);
INSERT INTO t VALUES('larry', 'car', 1);
COMMIT;


SELECT * FROM t;

CUSTOMER_NAME PRODUCT_NAME HITS
------------------ ----------------- ----------
bill egg 1
bill egg 1
bill beer 3
bill beer 2
bill jeans 1
larry beer 4
larry beer 2
larry cheese 1
larry cheese 1
larry cheese 1
larry cheese 1
larry cheese 1
larry cheese 1
larry olives 1
larry olives 1
larry car 1


The solution query


SELECT
customer_name,
product_name,
hits,
rank() over(PARTITION BY customer_name ORDER BY hits DESC nulls LAST) rank,
dense_rank() over(PARTITION BY customer_name ORDER BY hits DESC nulls LAST) denserank,
row_number() over(PARTITION BY customer_name ORDER BY hits DESC) rownumber
FROM
(
SELECT
customer_name,
product_name,
SUM(hits) hits
FROM t
GROUP BY customer_name, product_name
);

CUSTOMER_NAME PRODUCT_NAME HITS RANK DENSERANK ROWNUMBER
--------------- --------------- ---------- ---------- ---------- ----------
bill beer 5 1 1 1
bill jeans 1 2 2 2
bill egg 1 2 2 3
larry cheese 6 1 1 1
larry beer 6 1 1 2
larry olives 2 3 2 3
larry car 1 4 3 4

7 rows selected.



Observe how dense rank will not skip the ranking sequence when products with the same amount of hits are ranked with the same rank. In this case Larry's cheese and beer are ranked in rank 1 in the DENSERANK column and Larry's olives immediately after that are ranked with 2. Whereas the situation is quite different for this case in the RANK column where Larry's olives are ranked with 3.

Thursday 24 January 2008

Data Modeling with Oracle JDeveloper

Long time now I have been looking for a Free Data Modelling tool which is from the Oracle breed but is not part of a gigabytes size software installation (Discoverer...etc).



Finally oracle has bundled Data Modeling in JDeveloper which is a free tool they give for building Java applications. All this time JDeveloper hasn't been of my taste because of its name "the J". I am not a Java Developer! But I kind of started liking it now, when I checked it closely, I found out that it has changed a bit since the last time I had a look at it couple of years before, it is more DBA friendly.

JDeveloper is:


  • Free!

  • Smaller, compact sizes of 400M for a full installation

  • Familiar interface to Oracle SQL Developer. (I didn't realise that Oracle SQL Developer actually was already part of JDeveloper all this time, I always thought that Oracle SQL Developer was a new tool, clever!)

  • Capable of database development and more DBA friendly with SQL Sheet, ER Modeling, PL/SQL Editing


I would still prefer to see the Data Modeling part of it available in Oracle SQL Developer as well, as a DBA I want a mean and lean tool to do SQL, PL/SQL and ER Modeling.

For a download, and tutorials on how to create data models with JDeveloper go http://www.oracle.com/technology/obe/obe1013jdev/10131/database%20development/obe_%20dbasedevmt.htm
(This link takes time, alternatively search for JDeveloper in Google)