Home » SQL & PL/SQL » SQL & PL/SQL » standardize time series
standardize time series [message #194823] Mon, 25 September 2006 09:22
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello

I have a table with the Date ('DNEW', YYYYMM) and a parameter P over time (monthly data).
Now, I want to calculate the standardized values over a rolling 5 year period (example for 200608, the calculation period is 200109 to 200608).

The calculation is: Z = (P - avg(P)) / stdev(P)

In addition, the maximum of the standardized value should be 3, the minimum -3 (so I cut the fat tales).

Does anyone know an elegant code for this?

I tried it with the code below, but there is still an error in it:

	  create table P51 as
	  select a.DNEW,
			 case stddev_samp(a.P) over 
                       ( order by a.xdnew
		                 range between numtoyminterval(5,'year') preceding and current row ) 
				  when 0 then null
				  else 
				  	 case ( a.P - avg(a.P) over
                                         ( order by a.xdnew
		                                   range between numtoyminterval(5,'year') preceding and current row ))
					              /(stddev_samp(a.P) over
                                         ( order by a.xdnew
		                                   range between numtoyminterval(5,'year') preceding and current row ))
                            when > 3 then 3
							when < -3 then -3
							else
							      ( a.P - avg(a.P) over
                                         ( order by a.xdnew
		                                   range between numtoyminterval(5,'year') preceding and current row ))
					              /(stddev_samp(a.P) over
                                         ( order by a.xdnew
		                                   range between numtoyminterval(5,'year') preceding and current row ))
							end
                  end as P
	  	from (select b.*,
                      to_date(DNEW, 'yyyymm') as xdnew
               from P50 b ) a


Thanks
Stefan

Example:
DNEW P STANDARDIZED
198912 2.11635
199001 1.97503 -0.707106781
199002 1.98276 -0.528022116
199003 2.00269 -0.251019605
199004 1.98524 -0.460764969
199005 2.10681 1.204146859
199006 2.06118 0.464716199
199007 2.04118 0.128795207
199008 1.8369 -2.077644189
199009 1.7466 -2.066700471
199010 1.74169 -1.67885069
199011 1.85187 -0.786345901
199012 1.88973 -0.472329712
199101 1.97522 0.200733042
199102 2.09596 1.105825004
199103 2.10674 1.106942085
199104 2.0779 0.829825371
199105 2.15093 1.311061393


Previous Topic: Renaming Table
Next Topic: Another query tuning problem
Goto Forum:
  


Current Time: Sun Dec 11 00:26:01 CST 2016

Total time taken to generate the page: 0.10074 seconds