Oracle Techniques by Sameer Wadhwa ( Analytic Functions)

borrowed from : http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Ranked_func_bk

xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:w=”urn:schemas-microsoft-com:office:word”
xmlns=”http://www.w3.org/TR/REC-html40″>

 

id=”_x0000_t136″ coordsize=”21600,21600″ o:spt=”136″ adj=”10800″ path=”m@7,0l@8,0m@5,21600l@6,21600e”>

o:connectangles=”270,180,90,0″/>

strokeweight=”1.5pt”>

fitpath=”t” string=”Analytical functions”/>
     

style=’mso-tab-count:13′>                                                                                                                                                     

 

                                                                                    style=’color:maroon’>SAMEER WADHWA

                                                                        style=”mso-spacerun: yes”>       Wadhwa_S@Hotmail.com

                                                           

In this article I have 
tried to aware you about some of the analytic functions provided by oracle
8i.These funtions are very powerful and ease to use.

 

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
color:blue;mso-bidi-font-weight:bold’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Rollup_and_cube_bk”>ROLLUP
AND CUBE AGGREGATE FUNCTIONS

style=’mso-bidi-font-weight:normal’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Ranked_func_bk”>RANKED
FUNCTION

style=’mso-bidi-font-weight:normal’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Case_bk”>CASE

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
color:blue;mso-bidi-font-weight:bold’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Lag_and_lead_bk”>LAG
AND LEAD FUNCTION

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
color:blue;mso-bidi-font-weight:bold’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Ratio_to_report_bk”>RATIO_TO_REPORT

 

 

 

                                    name=”Rollup_and_cube_bk”>ROLLUP AND CUBE
AGGREGATE FUNCTIONS

 

To understand the power of ROLLUP and CUBE functions ,consider the
following SQL statement :-

 

ora816 SamSQL :> compute sum of totsal on deptno

ora816 SamSQL :> break on deptno

ora816 SamSQL :> select deptno,job,sum(sal) totsal from emp group
by deptno,job;

 

style=”mso-spacerun: yes”>    DEPTNO JOB           TOTSAL

———-
——— ———-

style=”mso-spacerun: yes”>        10 CLERK           1300

style=”mso-spacerun: yes”>           MANAGER         2450

style=”mso-spacerun: yes”>           PRESIDENT       5000

********** style=”mso-spacerun: yes”>           ———-

sum style=”mso-spacerun: yes”>                        8750

style=”mso-spacerun: yes”>        20 ANALYST         6000

style=”mso-spacerun: yes”>           CLERK           1900

style=”mso-spacerun: yes”>           MANAGER         2975

********** style=”mso-spacerun: yes”>           ———-

sum style=”mso-spacerun: yes”>                       10875

style=”mso-spacerun: yes”>        30 CLERK            950

style=”mso-spacerun: yes”>           MANAGER         2850

style=”mso-spacerun: yes”>           SALESMAN        5600

********** style=”mso-spacerun: yes”>           ———-

sum style=”mso-spacerun: yes”>                        9400

 

Now see the use of ROLLUP Function

 

ora816 SamSQL :> select deptno,job,sum(sal) totsal from emp
group by ROLLUP(deptno,job);

 

style=”mso-spacerun: yes”>    DEPTNO JOB           TOTSAL

———-
——— ———-

style=”mso-spacerun: yes”>        10 CLERK           1300

style=”mso-spacerun: yes”>        10 MANAGER         2450

id=”_x0000_t88″ coordsize=”21600,21600″ o:spt=”88″ adj=”1800,10800″ path=”m0,0qx10800@0l10800@2qy21600@11,10800@3l10800@1qy0,21600e”
filled=”f”>

textboxrect=”0,@4,7637,@5″/>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>        10 PRESIDENT style=”mso-spacerun: yes”>       5000

style=”mso-spacerun: yes”>        10                 8750   Total of Deptno 10

style=”mso-spacerun: yes”>        20 ANALYST         6000

style=”mso-spacerun: yes”>        20 CLERK           1900

style=”mso-spacerun: yes”>        20 MANAGER         2975

style=”mso-spacerun: yes”>        20                 style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:red’>10875

style=”mso-spacerun: yes”>        30 CLERK            950

style=”mso-spacerun: yes”>        30 MANAGER         2850

style=”mso-spacerun: yes”>        30 SALESMAN        5600

type=”#_x0000_t88″ style=’position:absolute;left:0;text-align:left;
margin-left:297pt;margin-top:5.55pt;width:9pt;height:18pt;z-index:6′>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>        30 style=”mso-spacerun: yes”>                 9400

style=”mso-spacerun: yes”>                          style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:red’>29025   Grand Total

 

 

So if you compare the two output you will notice that you are getting
the same output.  By using rollup
you can avoid compute and break clausesfrom SQL. style=”mso-spacerun: yes”>  This will mostly helpful in style=”mso-spacerun: yes”>  PL/SQL 
.  We do not have to put logic
for computing values on break of groups.

 

 

Now see the use of
CUBE  Function

 

ora816
SamSQL :> select deptno,job,sum(sal) totsal from emp group by
CUBE(deptno,job);

 

Fri Mar 23

style=”mso-spacerun:
yes”>                                                          NuGenesis
Report

 

style=”mso-spacerun: yes”>    DEPTNO JOB           TOTSAL

———-
——— ———-

style=”mso-spacerun: yes”>        10 CLERK           1300

style=”mso-spacerun: yes”>        10 MANAGER        
2450

type=”#_x0000_t88″ style=’position:absolute;left:0;text-align:left;
margin-left:4in;margin-top:8.25pt;width:9pt;height:18pt;z-index:4′>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>        10 PRESIDENT style=”mso-spacerun: yes”>       5000

style=”mso-spacerun: yes”>        10                 8750   Total of Deptno 10

style=”mso-spacerun: yes”>        20 ANALYST         6000

style=”mso-spacerun: yes”>        20 CLERK           1900

style=”mso-spacerun: yes”>        20 MANAGER         2975

style=”mso-spacerun: yes”>        20                10875

style=”mso-spacerun: yes”>        30 CLERK            950

style=”mso-spacerun: yes”>        30 MANAGER         2850

style=”mso-spacerun: yes”>        30 SALESMAN        5600

style=”mso-spacerun: yes”>        30                 9400

type=”#_x0000_t88″ style=’position:absolute;left:0;text-align:left;
margin-left:4in;margin-top:2.95pt;width:18pt;height:54pt;z-index:2′>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>           ANALYST style=”mso-spacerun: yes”>         6000 style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>

style=”mso-spacerun: yes”>           CLERK           4150 style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>

style=”mso-spacerun: yes”>           MANAGER         8275    Total w.r.t JOB

style=”mso-spacerun: yes”>           PRESIDENT        style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:maroon’>5000

style=”mso-spacerun: yes”>           SALESMAN        5600 style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>

id=”_x0000_t87″ coordsize=”21600,21600″ o:spt=”87″ adj=”1800,10800″ path=”m21600,0qx10800@0l10800@2qy0@11,10800@3l10800@1qy21600,21600e”
filled=”f”>

textboxrect=”13963,@4,21600,@5″/>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>            style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:red’>Grand Total   29025

 

Cube also do a total with respect to second group
for example JOB in our case . Also at end you will see the grand total

 

 

Conclusion : Rollup and
Cube are the aggregate function which allows developers and dbas to avoid
compute and break clauses and simplify logic of programming

 

name=”Ranked_func_bk”>Ranked Function in 8i (816) style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:red’>

 

Suppose you have a data in table which you want to rank in a specified
order for example you have a table test and you want to rank a value of repcol.

 

                                               style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>Ora816 SamSQL> select * from test;

 

REPCOL         
VALUE

———-   
———-

A                
100

A            style=”mso-spacerun: yes”>     200

A                
300

B               
1000

B                
900

B                
800

A                
500

B                
400

B                
500

 

Ora816
SamSQL> select repcol,value, style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>rank() over ( partition by repcol

style=”mso-spacerun: yes”>                2 style=”mso-spacerun: yes”>  order
by value desc ) ranked_value

style=”mso-spacerun: yes”>           style=”mso-spacerun: yes”>     3 
from test;

 

REPCOL         
VALUE RANKED_VALUE

———- ———- ————

A                
500            1

A                
300            2

A                
200            3

A                
100            4

B  style=”mso-spacerun: yes”>              1000            1

B                
900            2

B                
800            3

B                
500            4

B                
400            5 style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>

 

The
above value is ranked by the rank function provided by 8.1.6 style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>

 

 

style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:red’>Use of Case in SELECT style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:red’>

 

Case Statement are similar to decode , it is more
flexible and gives better performace

 

Ora816
SamSQL>  ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select sum(case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> repcol=’A’ then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) sum_of_A,

style=”mso-spacerun: yes”>           style=”mso-spacerun: yes”> 2        
sum(Case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> repcol=’B’ then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Sum_of_B,

style=”mso-spacerun: yes”>  3        
sum(case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>value = 500 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 1 else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Value_Eq_500,

style=”mso-spacerun: yes”>  4        
sum(case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value > 100 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>1 else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Value_Gre_100

style=”mso-spacerun: yes”>  5* 
from test

Ora816
SamSQL>  /

 

  SUM_OF_A style=”mso-spacerun: yes”>   SUM_OF_B VALUE_EQ_500 VALUE_GRE_100

———- ———- ———— ————-

      1100 style=”mso-spacerun: yes”>       3600            2             8

 

Use of Group by in CASE

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select repcol,sum(case when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> repcol=’A’ then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) sum_of_A,

style=”mso-spacerun: yes”>  2        
sum(Case when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> repcol=’B’ then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Sum_of_B,

style=”mso-spacerun: yes”>  3        
sum(case when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value = 500 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 1 else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Value_Eq_500,

style=”mso-spacerun: yes”>  4        
sum(case when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value > 100 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>1 else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Value_Gre_100

style=”mso-spacerun: yes”>  5  
from test

style=”mso-spacerun: yes”>  6* group
by repcol

Ora816 SamSQL> /

 

REPCOL      
SUM_OF_A   SUM_OF_B VALUE_EQ_500
VALUE_GRE_100

———- ———- ———- ————
————-

A               
1100          0 style=”mso-spacerun: yes”>            1             3

B                  
0       3600 style=”mso-spacerun: yes”>            1             5

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select (case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value between 100 and 300 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> ‘100-300′

style=”mso-spacerun: yes”>  2      
when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value between 400 and 700 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> ‘400-700′

style=”mso-spacerun: yes”>  3      
when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value between 800 and 900 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> ‘800-900′

style=”mso-spacerun: yes”>  4      
when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value > 900 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> ‘>900′ end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) VALUE_RANGE,

style=”mso-spacerun: yes”>  5       
count(*) as VALUE_COUNT

style=”mso-spacerun: yes”>  6      
from test

style=”mso-spacerun: yes”>  7 
group by

style=”mso-spacerun: yes”>  8 
(case when value between 100 and 300 then ‘100-300′

style=”mso-spacerun: yes”>  9       
when value between 400 and 700 then ‘400-700′

style=”mso-spacerun: yes”> 10       
when value between 800 and 900 then ‘800-900′

style=”mso-spacerun: yes”> 11*      
when value > 900 then ‘>900′ end )

Ora816
SamSQL> /

 

VALUE_R VALUE_COUNT

——- ———–

100-300          
3

400-700          
3

800-900          
2

>900             
1

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1  select
(case when value between 100 and 300 then ‘100-300′

style=”mso-spacerun: yes”>  2      
when value between 400 and 700 then ‘400-700′

style=”mso-spacerun: yes”>  3      
when value between 800 and 900 then ‘800-900′

style=”mso-spacerun: yes”>  4      
when value > 900 then ‘>900′ end) VALUE_RANGE,value

style=”mso-spacerun: yes”>  5*     
from test

Ora816
SamSQL> /

 

VALUE_R     
VALUE

——- ———-

100-300       
100

100-300       
200

100-300       
300

>900         
1000

800-900       
900

800-900       
800

400-700       
500

400-700       
400

400-700       
500

 

9 rows selected.

 

style=”mso-spacerun: yes”> Lag and Lead
Functions

 

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select repcol,

style=”mso-spacerun: yes”>  2 
value,

style=”mso-spacerun: yes”>  3  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>lag(value, style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>1) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over ( style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>order by repcol) lag_value ,

style=”mso-spacerun: yes”>  4  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>lead(value, style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>1) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over ( style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>order by repcol) lead_value

style=”mso-spacerun: yes”>  5* from test

Ora816
SamSQL> /

 

REPCOL         
VALUE  LAG_VALUE LEAD_VALUE

———- ———- ———- ———-

A                
100                   200

A                
200        100 style=”mso-spacerun: yes”>        500

A                
500        200 style=”mso-spacerun: yes”>        300

A                
300        500 style=”mso-spacerun: yes”>       1000

B              style=”mso-spacerun: yes”>  1000       
300        500

B                
500       1000 style=”mso-spacerun: yes”>        900

B                
900        500 style=”mso-spacerun: yes”>        400

B                
400        900 style=”mso-spacerun: yes”>        800

B                
800        400

 

9 rows selected.

 

The LAG function provides access
to a row at a given offset prior to the position and the LEAD function provides
access to a row at a given offset after the current position.

The functions have the
following syntax:

{LAG | LEAD}   (, [ [, ]]) OVER       ([PARTITION BY [,...]]      ORDER BY  [collate clause>]      [ASC | DESC] [NULLS FIRST | NULLS LAST] [,...]) 

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>is
an optional parameter and defaults to 1. is an optional parameter and
is the value returned if the falls outside the bounds of the table or
partition.

 

style=’font-size:12.0pt;mso-bidi-font-size:14.0pt;color:red’>Ratio_to_report style=’font-size:12.0pt;mso-bidi-font-size:14.0pt;color:red’>

The RATIO_TO_REPORT
function computes the ratio of a value to the sum of a set of values. If the
expression value expression style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>
evaluates to NULL, RATIO_TO_REPORT also evaluates to NULL, but it is treated as
zero for computing the sum of values for the denominator. Its syntax is:

RATIO_TO_REPORT() OVER        ([PARTITION BY [,...]]) 

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>where

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
mso-bidi-font-weight:bold;mso-bidi-font-style:italic’>· style=’font-size:7.0pt;mso-bidi-font-weight:bold;mso-bidi-font-style:italic’>        
and can be any valid
expression involving column references or aggregates.

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
mso-bidi-font-style:italic’>·         style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>The
PARTITION BY clause defines the groups on which the RATIO_TO_REPORT function is
to be computed. If the PARTITION BY clause is absent, then the function is
computed over the whole query result set style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>. style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select repcol,

style=”mso-spacerun: yes”>  2 
value,

style=”mso-spacerun: yes”>  3 
sum(value) over() style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> sumofvalue,

style=”mso-spacerun: yes”>  4  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>ratio_to_report (sum(value)) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over() RATIO

style=”mso-spacerun: yes”>  5 
from test

style=”mso-spacerun: yes”>  6* group by repcol ,value

Ora816
SamSQL> /

 

REPCOL         
VALUE SUMOFVALUE      RATIO

———- ———- ———- ———-

A                
100       4700 .021276596

A                
200       4700 .042553191

A                
300       4700 .063829787

A                
500       4700 .106382979

B                
400       4700 .085106383

B                
500       4700 .106382979

B                
800       4700 .170212766

B            style=”mso-spacerun: yes”>     900      
4700 .191489362

B               
1000       4700 .212765957

 

9 rows selected.

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select repcol,

style=”mso-spacerun: yes”>  2 
value,

style=”mso-spacerun: yes”>  3  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>sum(value) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over(partition by repcol) sumofvalue,

style=”mso-spacerun: yes”>  4  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>ratio_to_report (sum(value)) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over(partition by repcol) RATIO

style=”mso-spacerun: yes”>  5 
from test

style=”mso-spacerun: yes”>  6* group by repcol ,value

Ora816
SamSQL> /

 

REPCOL         
VALUE SUMOFVALUE      RATIO

———- ———- ———- ———-

A                
100       1100 .090909091

A                
200       1100 .181818182

A                
300       1100 .272727273

A                
500       1100 .454545455

B                
400       3600 .111111111

B                
500       3600 .138888889

B                
800       3600 .222222222

B                
900       3600        .25

B               
1000       3600 .277777778

 

9 rows selected.

 

Conclusion : These analytic functions are very powerful and very
useful.  Thanks to Oracle to provides us
such a great tool.

 

 

References:

 

Oracle Server concept manual

Oracle Datawarehouse manual.

 

Please feel free to send  comments or  feedback at style=’mso-fareast-font-family:”MS Mincho”;color:red’> href=”mailto:wadhwa_s@hotmail.com”>wadhwa_s@hotmail.com style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:red’>.

Thanks for reading this article style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:blue’>.

SAMEER WADHWA

 

Copyright  2001 style=”mso-spacerun: yes”>  Sameer Wadhwa (All right reserved)

 

 

style=”mso-spacerun: yes”> 

height=1 id=”_x0000_i1030″ src=”swAnalyticalFuntions_files\serv.gif” alt=1>