OraFAQ Forum: SQL & PL/SQL » Moving average

Home » SQL & PL/SQL » SQL & PL/SQL » Moving average

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Moving average [message #184299]

Wed, 26 July 2006 01:28

steffeli
Messages: 112
Registered: July 2006

Senior Member

How can I calculate the moving average of a parameter? The problem in my data set is, that there are dates missing. Therefore, I need to calculate the moving average between a trailing range (xdate_min, xdate_max). I already have these two parameters in my table.

Does anyone know a fast solution?

Thanks
Stefan

Report message to a moderator

Re: Moving average [message #184303 is a reply to message #184299]

Wed, 26 July 2006 01:49

gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA

Senior Member

i could not understand you requirement clearly. so can you give somple data and desired output?

Further i could not post any new questions or new topic. when i do the same it is says like "Your session has expired. Please re-submit the form. Sorry for the inconvenience.". when i even resubmit i'm getting the same.

but i able to reply to any messange. hence do i need to do some settings or any criteria to post the new topic? just help me out...

Report message to a moderator

Re: Moving average [message #184305 is a reply to message #184303]

Wed, 26 July 2006 01:56

Maaher
Messages: 7065
Registered: December 2001

Senior Member

The "Your session has expired." message can be due to your cookie management. It's best that you post a message in feedback and suggestions (if you can that is Very Happy

).

MHE

Report message to a moderator

Re: Moving average [message #184308 is a reply to message #184305]

Wed, 26 July 2006 01:59

steffeli
Messages: 112
Registered: July 2006

Senior Member

Ok, here is a general example: I want to calculate xvalue_mab6, which is the moving average over the last 6 month:

xdate xvalue xvalue_mav6
200605 10 8.17
200604 12 6.67
200603 5 7.17
200602 9 9.00
200601 8 10.67
200512 5 13.50
200511 1 23.50
200510 15 28.50
200509 16 28.50
200508 19 28.50
200507 25 27.83
200506 65 26.67
200505 31 19.00
200504 15 16.00
200503 16 16.33
200502 15 16.50
200501 18 18.00

The code also should work when xdate are missing. In the example below, the xdates 200602 - 200604 are missing. The moving average for xdate = 200605 is calculated as average between 200605 and 200512 (only 3 records!).

xdate xvalue xvalue_mav6
200605 10 7.67
200601 8 10.67
200512 5 13.50
200511 1 23.50
200510 15 28.50
200509 16 28.50
200508 19 28.50
200507 25 27.83
200506 65 26.67
200505 31 19.00
200504 15 16.00
200503 16 16.33
200502 15 16.50
200501 18 18.00

Report message to a moderator

Re: Moving average [message #184313 is a reply to message #184308]

Wed, 26 July 2006 02:23

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Try this ...

SQL> select * from x_tab;

XDATE      XVALUE
------ ----------
200605         10
200601          8
200512          5
200511          1
200510         15
200509         16
200508         19
200507         25
200506         65
200505         31
200504         15
200503         16
200502         15
200501         18

14 rows selected.

SQL> SELECT XDATE ,XVALUE,
  2         ROUND((SELECT AVG(x2.XVALUE) FROM
  3                x_tab  x2
  4           WHERE x2.XDATE BETWEEN
  5           TO_CHAR(ADD_MONTHS(TO_DATE(x1.XDATE,'YYYYMM'),-5),'YYYYMM')
  6           AND x1.XDATE),2)  xvalue_mav6
  7    FROM x_tab  x1
  8  /

XDATE      XVALUE XVALUE_MAV6
------ ---------- -----------
200605         10        7.67
200601          8       10.67
200512          5        13.5
200511          1        23.5
200510         15        28.5
200509         16        28.5
200508         19        28.5
200507         25       27.83
200506         65       26.67
200505         31          19
200504         15          16
200503         16       16.33
200502         15        16.5
200501         18          18

14 rows selected.

SQL>

Rajuvan

[Updated on: Wed, 26 July 2006 02:26]

Report message to a moderator

Re: Moving average [message #184325 is a reply to message #184313]

Wed, 26 July 2006 02:52

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

And now the obligatory Analytic solution

SQL> desc temp_avg
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 XDATE                                              NUMBER(6)
 XVALUE                                             NUMBER(2)
 XVALUE_MAV6                                        NUMBER(6,2)

SQL> 
SQL> select * from temp_avg;

     XDATE     XVALUE XVALUE_MAV6
---------- ---------- -----------
    200605         10        8.17
    200604         12        6.67
    200603          5        7.17
    200602          9           9
    200601          8       10.67
    200512          5        13.5
    200511          1        23.5
    200510         15        28.5
    200509         16        28.5
    200508         19        28.5
    200507         25       27.83

     XDATE     XVALUE XVALUE_MAV6
---------- ---------- -----------
    200506         65       26.67
    200505         31          19
    200504         15          16
    200503         16       16.33
    200502         15        16.5
    200501         18          18

17 rows selected.

SQL> 
SQL> select xdate
           ,avg(xvalue) over (order by xdate range between numtoyminterval(5,'month') preceding and current row) xavg
           ,xvalue_mav6
  2  from (
  3  (select to_Date(xdate,'yyyymm') xdate,xvalue, xvalue_mav6 from temp_avg)
  4  );

XDATE                      XAVG XVALUE_MAV6
-------------------- ---------- -----------
01-Jan-2005 00:00:00         18          18
01-Feb-2005 00:00:00       16.5        16.5
01-Mar-2005 00:00:00 16.3333333       16.33
01-Apr-2005 00:00:00         16          16
01-May-2005 00:00:00         19          19
01-Jun-2005 00:00:00 26.6666667       26.67
01-Jul-2005 00:00:00 27.8333333       27.83
01-Aug-2005 00:00:00       28.5        28.5
01-Sep-2005 00:00:00       28.5        28.5
01-Oct-2005 00:00:00       28.5        28.5
01-Nov-2005 00:00:00       23.5        23.5

XDATE                      XAVG XVALUE_MAV6
-------------------- ---------- -----------
01-Dec-2005 00:00:00       13.5        13.5
01-Jan-2006 00:00:00 10.6666667       10.67
01-Feb-2006 00:00:00          9           9
01-Mar-2006 00:00:00 7.16666667        7.17
01-Apr-2006 00:00:00 6.66666667        6.67
01-May-2006 00:00:00 8.16666667        8.17

17 rows selected.

SQL>

Report message to a moderator

Re: Moving average [message #184370 is a reply to message #184325]

Wed, 26 July 2006 06:26

steffeli
Messages: 112
Registered: July 2006

Senior Member

Hi JRowbottom

Thanks very much. It works but takes quite a while.
Would it be faster to use DNEW_LAG6 instead of the code "between numtoyminterval(5,'month') preceding and current row)"? I already have this parameters, which is the date 6 month ago.
Does anyone know how to rewrite the code with DNEW_LAG6?

Thanks
Stefan

Report message to a moderator

Re: Moving average [message #184385 is a reply to message #184370]

Wed, 26 July 2006 06:53

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

How long is 'quite a while' and how much data do you have in this table?

This might be quicker, but NumtoymInterval isn't a terrible slow function.

select xdate
      ,avg(xvalue) over (order by xdate range between xdate-xdate_lag6 preceding and current row) xavg
      ,xvalue_mav6
from (select to_Date(xdate,'yyyymm') xdate
            ,xvalue
            ,xvalue_mav6 
            ,to_Date(xdate_lag6,'yyyymm') xdate_lag6
      from temp_avg);

If you have got lots of rows with the same Xdate, it might be worth changing the inner query to group them up by Xdate to reduce the amount of work the Analytic function has to do

Something like:

select xdate
      ,avg(xvalue) over (order by xdate range between xdate-xdate_lag6 preceding and current row) xavg
from (select to_Date(xdate,'yyyymm') xdate
            ,sum(xvalue) xvalue
            ,to_Date(xdate_lag6,'yyyymm') xdate_lag6
      from temp_avg
      group by to_Date(xdate,'yyyymm'),to_Date(xdate_lag6,'yyyymm'));

Oh yes - while I think of it, give your designer/analyst a swift slap upside the head and tell them to use a DATE column next time they want to store a date.

Report message to a moderator

Previous Topic:	how to get server name
Next Topic:	Delete the duplicate rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 07:47:17 CST 2024