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

    Calculate average over last 12 months

    sliderrules
      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
          user639304
          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
            Frank Kulash
            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
              sliderrules
              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
                chris227
                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
                  chris227
                  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
                                     --numtodsinterval(month - add_months(month, -11), 'DAY')
                                     numtodsinterval(month - trunc(add_months(month, -11), 'MM'), 'DAY')
                                     preceding
                                     ) / 12,2) AS avg_agreed_rate
                  FROM      data
                  ORDER BY  month
                  
                  
                  numtodsinterval(month - add_months(month, -11), 'DAY')
                  is just an equivalent to the original range