Home » SQL & PL/SQL » SQL & PL/SQL » Moving average
Moving average [message #184299] Wed, 26 July 2006 01:28 Go to next message
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
Re: Moving average [message #184303 is a reply to message #184299] Wed, 26 July 2006 01:49 Go to previous messageGo to next message
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...
Re: Moving average [message #184305 is a reply to message #184303] Wed, 26 July 2006 01:56 Go to previous messageGo to next message
Maaher
Messages: 7062
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

Re: Moving average [message #184308 is a reply to message #184305] Wed, 26 July 2006 01:59 Go to previous messageGo to next message
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
Re: Moving average [message #184313 is a reply to message #184308] Wed, 26 July 2006 02:23 Go to previous messageGo to next message
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>


Thumbs Up
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 Go to previous messageGo to next message
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> 
Re: Moving average [message #184370 is a reply to message #184325] Wed, 26 July 2006 06:26 Go to previous messageGo to next message
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
Re: Moving average [message #184385 is a reply to message #184370] Wed, 26 July 2006 06:53 Go to previous message
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.
Previous Topic: how to get server name
Next Topic: Delete the duplicate rows
Goto Forum:
  


Current Time: Tue Dec 06 14:01:04 CST 2016

Total time taken to generate the page: 0.16698 seconds