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.



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!



  ¶ 1:08 AM