5 Replies Latest reply: Feb 1, 2013 6:08 AM by chris227

Calculate average over last 12 months

Hi,

I am trying to calculate the average value per customer and month starting and including the month and 12 months back

e.g.: 01-JAN-12 = average(agreed_rate) & average(process_rate) from '01-FEB-11' and '01-JAN-12'.
01-FEB-12= average(agreed_rate) & average(process_rate) from '01-MAR-11' and '01-FEB-12'
01-MAR-12= average(agreed_rate) & average(process_rate) from '01-APR-11' and '01-MAR-12' etc

create table test(
cust_num varchar2(5),
month date,
agreed_rate number,
process_rate number);

insert into test values('A001', '01-JAN-11', 87, 65);
insert into test values('A001', '01-FEB-11', 56, 62);
insert into test values('A001', '01-MAR-11', 76, 88);
insert into test values('A001', '01-APR-11', 45, 69);
insert into test values('A001', '01-MAY-11', 99, 65);
insert into test values('A001', '01-JUN-11', 98, 43);
insert into test values('A001', '01-JUL-11', 87, 70);
insert into test values('A001', '01-AUG-11', 43, 99);
insert into test values('A001', '01-SEP-11', 62, 32);
insert into test values('A001', '01-OCT-11', 89, 44);
insert into test values('A001', '01-NOV-11', 34, 65);
insert into test values('A001', '01-DEC-11', 87, 87);
insert into test values('A002', '01-JAN-12', 33, 65);
insert into test values('A002', '01-FEB-12', 56, 23);
insert into test values('A002', '01-MAR-12', 76, 46);
insert into test values('A002', '01-APR-12', 67, 69);
insert into test values('A002', '01-MAY-12', 99, 33);
insert into test values('A002', '01-JUN-12', 89, 21);
insert into test values('A002', '01-JUL-12', 87, 77);
insert into test values('A002', '01-AUG-12', 69, 99);
insert into test values('A002', '01-SEP-12', 89, 32);
insert into test values('A002', '01-OCT-12', 43, 20);
insert into test values('A002', '01-NOV-12', 34, 56);
insert into test values('A002', '01-DEC-12', 77, 78);

e.g. JAN-12 average agreed_rate = (33+87+34+89+62+43+87+98+99+45+76+56)/12
FEB-12 average agreed_rate = (56+33+87+34+89+62+43+87+98+99+45+76)/12

The results should be displayed by month(as above example) and cust_num.

Can anyone help?

Thanks
• 1. Re: Calculate average over last 12 months
Hi,

You wrote: The results should be displayed by month(as above example) and cust_num.,
but the JAN-12 agreed_rate average calculation you shows is a global average (it's an average where all the customers, namely here A001 and A002, have been taken into account.
So, what do you want exactly? Averages for every month, or Averages for a month and a customer?
• 2. Re: Calculate average over last 12 months
Hi,

That sounds like a job for the analytic AVG function:
``````WITH     got_month_num     AS
(
SELECT     cust_num, month, agreed_rate
,     MONTHS_BETWEEN ( TRUNC (month,      'MONTH')
, TRUNC (SYSDATE, 'MONTH')
)  AS month_num
FROM    test
--     WHERE     ...     -- If you need any filtering, put it here
)
SELECT       cust_num, month, agreed_rate
,       AVG (agreed_rate) OVER ( -- PARTITION BY  cust_num
ORDER BY         month_num
RANGE BETWEEN    12 PRECEDING
AND         CURRENT ROW
) AS avg_agreed_rate
FROM       got_month_num
ORDER BY  month
;``````
I only showed agreed_rate; process_rate can be done the same way.

As mentioned earlier, it's unclear if you want to keep a separate average for each cust_num or not. The query above does not; if you want separate averages, then un-comment the analytic PARTITON BY clause.
• 3. Re: Calculate average over last 12 months
Hi,

Thanks for getting back. I have look at the sample query but it does not return the correct average values

e.g. Jan-12 should equal= (33+87+34+89+62+43+87+98+99+45+76+56)/12 = 67.41. The query returns 68.92

Feb-12 should equal= (56+33++87+34+89+62+43+87+98+99+45+76)/12

I am after the average value per month calculating rates 12 months back per month and by customer(the current sample data does not contain enough values to split by cust_num)
can anyone help?

Thanks

Edited by: sliderrules on 01-Feb-2013 01:01
• 4. Re: Calculate average over last 12 months
You need to go 11 month back form the current row which is included as the twelvth one.
If you round up the result is 67.42 rather then 67.41. This is the result over all cust_num.
If you want the result for each customer just comment the partiton by clause in.
``````SELECT      cust_num, month, agreed_rate
,      round (sum (agreed_rate) OVER ( --PARTITION BY  cust_num
ORDER BY month
RANGE
INTERVAL '11' month
preceding
) / 12,2) AS avg_agreed_rate
FROM       test
ORDER BY  month``````
Partition over cust_num
``````SELECT      cust_num, month, agreed_rate
,      round (sum (agreed_rate) OVER ( PARTITION BY  cust_num
ORDER BY month
RANGE
INTERVAL '11' month
preceding
) / 12,2) AS avg_agreed_rate
FROM       test
ORDER BY  month``````
Edited by: chris227 on 01.02.2013 01:51
corrected table name

Edited by: chris227 on 01.02.2013 01:52
• 5. Re: Calculate average over last 12 months
I should add one remark to the solution provided.
It calculates the difference of the month form day to day. So if you change 01-JAN_2012 to 02-JAN-2012 you will get another result as the 01-JAN-2011 is not longer included.
If the requirement is to cover the last to month regardless of the specific dates the query could be rewritten as below
``````SELECT      cust_num, month, agreed_rate
,      round (sum (agreed_rate) OVER ( --PARTITION BY  cust_num
ORDER BY month
RANGE
--INTERVAL '11' month