.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Tuesday, November 09, 2004
  Strange and usefull Oracle SQL structure
hello everybody,

Last week I talked a lot about how to get Oracle's data into Excel. I said that, in my opinion, Excel is not the right tool to re-structure and reformat datas. It is not an OLAP tool.
Today I would like to talk about a few Oracle specific SQL syntax that may be usefull for that: "rank over" and "group by grouping sets"

"rank over"
So what is this?
Here is a sample code:
Just imagine you have a table which contains the monthly turnover of the products: MONTHLY_TURNOVER with the following fields PRODUCT_NAME,MONTH,YEAR, AMOUNT.


select MONTH'/''YEAR', PRODUCT_NAME, Amount, rank() over(partition by MONTH, YEAR order by AMOUNT desc) Month_Rank from MONTHLY_TURNOVER order by YEAR asc, MONTH asc,Month_Rank asc;

This query returns all the products for each month, orders by decreasing amount of turnover with the rank of each product in the month.

This query does not make any filtering action. Instead, there is ordering and creation of information. The"rank()" syntax creates a new piece of information : the rank in the month of the product.


Now imagine you have 500 differents products. You may want to see only the 10 most important of each month. This may be obtained by the following syntax:

select * from (
select MONTH'/''YEAR', PRODUCT_NAME, Amount, rank() over(partition by MONTH, YEAR order by AMOUNT desc) Month_Rank from MONTHLY_TURNOVER order by YEAR asc, MONTH asc,Month_Rank asc)

where Month_Rank<10;

I suggest you to build this as an Oracle view and then to query it by filtering on the year field or by the period you are interested in.



"group by grouping sets"


Did you ever work with MS Access report or Crystal Report? Then you must have seen these features that permit you to make sums, averages or other aggregation functions at different levels in those reporting tools. For instance, you can calculate the sum of all the turnovers of your products year by year, month by month, and then product by product for one month.
You can do the same things directly with Oracle.

Using the same table as in the first paragraph, try the following:

select sum(AMOUNT ) AMOUNT , MONTH,YEAR, PRODUCT_NAME
from (MONTHLY_TURNOVER)
group by grouping sets ((MONTH,YEAR, PRODUCT_NAME),(MONTH,YEAR),(YEAR))
order by year, month, product_name;
you will have the following result assuming you have two products and one year :

AMOUNT MONTH YEAR PRODUCT_NAME
---------------------------------------------------------
10 1 2004 p1
20 1 2004 p2
30 1 2004
15 2 2004 p1
25 2 2004 p2
40 2 2004
. . .

60 12 2004
360 2004

As you can see, the line that sums all the turnovers of one month does not have any value for "product_name". The line that sums all the year does not have any value for "product_name" and "month".

I found this very usefull because if you have to do this kind of query usualy you need to use three "select from where " clauses and you need to repeat three times your "where" clause.
Personnaly, I used it in a view to sum surfaces for different real estate assets. For each building I get the sum of the rented and the free surface, and the total.


Ok that's all folks.

Hope this will help developers as it helps me!
Comments are welcome!



 
Comments: Post a Comment

Links to this post:

Create a Link



<< Home
You are a developer and work with Oracle and Microsoft technologies? Have a look!
ATOM
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

From the datawarehouse to the chart (part 2)
From the datawarehouse to the chart (part 1)
Using updatable views in Access
Introduction
ARCHIVES
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /


Powered by Blogger

mboizeau.free.fr