Home » SQL & PL/SQL » SQL & PL/SQL » rolling standard deviation
rolling standard deviation [message #202390] Thu, 09 November 2006 08:17 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hi,

I calculate a rolling standard deviation like this:

select xdnew,
       a.*
       stddev(a.return)      
             over (partition by a.id
                   order by a.xdnew
		   range between numtoyminterval(11,'month'
                   preceding and current row)					as stdev
from tab a


xdnew is the date. The issue is, that the calculation starts with the first record (date). How can I start the calculation 12 month after the first date, so that I always have 12 records in the calculation? (I need to adjust the code within the strdev... statement)

Thanks
Stefan
Re: rolling standard deviation [message #202414 is a reply to message #202390] Thu, 09 November 2006 11:59 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Can't you just do a count at the same time as doing the stddev and then filter out values with a count of < 12 e.g.:

drop table testx;

create table testx (id number, xdnew date, return number);

insert into testx
select 1, add_months(trunc(sysdate)-15, rownum), 1
from dual
connect by level <= 15;

select * from
(select a.*,
       sum(a.return)      
             over (partition by a.id
                   order by a.xdnew
		   range between numtoyminterval(11,'month') preceding and current row) as total,
	   count(a.return) over (partition by a.id
                   order by a.xdnew
		   range between numtoyminterval(11,'month') preceding and current row) as sample_months
from testx a) 
where sample_months = 12;


I've used SUM rather than STDDEV so that the output is a bit clearer.
Previous Topic: difference between Join and Inner Join
Next Topic: Converting SQL file to csv file
Goto Forum:
  


Current Time: Sun Dec 11 04:25:05 CST 2016

Total time taken to generate the page: 0.11335 seconds