Home » SQL & PL/SQL » SQL & PL/SQL » stddev
stddev [message #194766] Mon, 25 September 2006 03:40 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello

I want to calculate the standard deviation of a time series (P). In excel, i took the stdev-function to calculate the rolling standard deviation (DNEW is the date YYYYMM). The result in excel is:

DNEW P STDEV
198912 2.11635
199001 1.97503 0.08159114
199002 1.98276 0.06818078
199003 2.00269 0.057462656
199004 1.98524 0.05390353
199005 2.10681 0.066635868
199006 2.06118 0.057289869
199007 2.04118 0.052892519
199008 1.8369 0.096856971
199009 1.7466 0.131189505
199010 1.74169 0.141191193
199011 1.85187 0.12756649
199012 1.88973 0.121760233
199101 1.97522 0.116694328


In oracle, I get a different result with the code below:
select a.DNEW, 
       a.P,
       stddev_samp(a.P) over ( order by a.dnew ) as STDEV
from test a


DNEW P STDEV
198912 2.11635 .
199001 1.97503 0.09993
199002 1.98276 0.07946
199003 2.00269 0.0658
199004 1.98524 0.05898
199005 2.10681 0.06533
199006 2.06118 0.06093
199007 2.04118 0.05649
199008 1.8369 0.08428
199009 1.7466 0.11558
199010 1.74169 0.13201
199011 1.85187 0.12991
199012 1.88973 0.12565
199101 1.97522 0.12092


What's wrong in my code? How can I get the same result as in excel?

Thanks
Stefan



Re: stddev [message #194812 is a reply to message #194766] Mon, 25 September 2006 07:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



select a.DNEW, 
       a.P,
       stddev_samp(a. STDEV) over ( order by a.dnew ) as STDEV
from test a


Thumbs Up
Rajuvan
Re: stddev [message #194813 is a reply to message #194812] Mon, 25 September 2006 08:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Better check your Excel. Oracle seems to return the RIGHT values...
Re: stddev [message #194888 is a reply to message #194766] Mon, 25 September 2006 23:32 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Excel is trying to calculate the STDDEV on the field STDEV and your query tries to calculate it in the field P. Thats why its not matching .


Thumbs Up
Rajuvan
Previous Topic: Bulk delete - save exceptions
Next Topic: How increase the speed for query
Goto Forum:
  


Current Time: Fri Dec 09 21:30:57 CST 2016

Total time taken to generate the page: 0.16633 seconds