Systems Engineering and RDBMS

Ratio_to_Report Analytic Function

Posted by decipherinfosys on April 24, 2009

We have covered the usage of analytic functions before in order to resolve grouping and ranking problems in couple of previous blog posts – here and here. Today, we are going to cover another analytic function ratio_to_report to calculate the ratio of value to the sum of the values. Let us say that we want to see what is the% of sales for each month for an entire year to know the sales trend for the company. Ratio_to_report can be very useful in such scenarios. Let’s see this in action with an example. Create following table and populate it with some data.

CREATE TABLE MONTHLY_SALE
(
MONTHLY_SALE_ID NUMBER(9) NOT NULL PRIMARY KEY,
COMPANY_ID VARCHAR2(3) NOT NULL,
MONTH VARCHAR2(2) NOT NULL,
SALE_AMT NUMBER(9,2) DEFAULT 0 NOT NULL,
CREATE_DATE DATE,
USER_ID VARCHAR2(15)
);

INSERT INTO MONTHLY_SALE(monthly_sale_id,company_id,month,sale_amt,create_date,user_id)
SELECT rownum,’DEC’,lpad(to_char(rownum),2,’0′),dbms_random.value(10000,999999),sysdate,’DECIPHER’
FROM user_objects
WHERE rownum < 13;

Commit;

In above example, we are using dbms_random package to generate the sales amount for each month randomly. We have covered it in detail here.

If we have to use traditional way to get the % of sales for each month, traditionally we have to use either one of the following queries. There could be other better ways to get same information as well.

SELECT month,((sale_amt/(select sum(sale_amt) from monthly_sale where company_id = ‘DEC’))*100) Sales_Percent
FROM monthly_sale
where company_id = ‘DEC’;

select month,(sale_amt/a.total)*100
from monthly_sale,
(
select sum(sale_amt) total
from monthly_sale
group by company_id
) a
where company_id = ‘DEC’;

It will give following results.

MO (SALE_AMT/A.TOTAL)*100
-- ----------------------
01             13.3452448
02              10.037815
03             5.17457293
04             11.0485086
05             3.49737182
06             2.58215091
07             14.3470303
08              4.4077088
09             15.7355199
10             .591053688
11             9.97939174
12             9.25363146

Now let us rewrite the query to use ratio_to_report function and execute it.

select month,ratio_to_report(sale_amt) over()*100 sales_percent
from monthly_sale
where company_id = ‘DEC’;

With the above query, we will exactly get the same results.

MO SALES_PERCENT
-- -------------
01    13.3452448
02     10.037815
03    5.17457293
04    11.0485086
05    3.49737182
06    2.58215091
07    14.3470303
08     4.4077088
09    15.7355199
10    .591053688
11    9.97939174
12    9.25363146

Once understood correctly, use of analytic functions makes our code simpler and more readable.

Resources:

  • Oracle 10g SQL Reference Manual – here.
  • Tom Kyte’s blog – here.

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

 
%d bloggers like this: