On Wed, Oct 12, 2005 at 09:05:26PM -0500, pilot pirx wrote: > The windowing functions described in the link > are different from recursive functions.
Yes, I think you're right. Your EMA example bugged me, so I fooled with it, but I couldn't come up with any way to implement EMA using plain SQL, even with the windowing/OLAP functions. Some explanations of the EMA algorithm are here: http://www.investopedia.com/university/tm/TradingIndicators/TheInsAndOutsOfMovingAverages.asp http://www.ivorix.com/en/products/tech/smooth/ema.html Looks like you don't even need real recursion for EMA (AKA, it is tail-recursive), plain old iteration will do fine, as you can see from the simple C++ code in the 2nd link above. (Yet it can't be done in SQL. Well we all knew that SQL isn't Turing complete, here's a practical consequence of that, I guess.) > Now, for the recursive function like exponential moving average the > defintion is that ema(i+1) = val(i) * coef + ema(i) * (1-coef). That > is I have to know the previous value of both EMA _and_ VALUE (while > for moving avearage I need to know _only_ the previous value(s) of > VALUE. It is quite possible to return previous (lagged) values of multiple columns, NOT just one. You do that with dense_rank, which I talked about a back on May 23 on this email list: I always thought of that feature as "look-aside", as in, "Find the max of foo, but don't give me that, instead look aside and return me the corresponding value of bar next to it instead." select max(playerid) keep (dense_rank last order by sb) as top_stealer from batting The problem is that EMA needs to use the lag of the actual column it's calculating, and there's just no damn way to do that in SQL. Well, I didn't try it, but maybe it would be possible to kludge EMA somehow using either Oracle's "connect by", or DB2's recursive SQL, which sounds possibly and less hideous than connect by: http://www-128.ibm.com/developerworks/db2/library/techarticle/0307steinbach/0307steinbach.html Here's the example I tried for EMA (on Oracle 8.1.7.4): create table atp_ema (symbol varchar2(8) ,day date ,val number); insert into atp_ema values ('a' ,'2005-01-03' ,67); insert into atp_ema values ('a' ,'2005-01-04' ,77); insert into atp_ema values ('a' ,'2005-01-05' ,80); insert into atp_ema values ('a' ,'2005-01-06' ,82); insert into atp_ema values ('a' ,'2005-01-07' ,94); insert into atp_ema values ('a' ,'2005-01-10' ,81); insert into atp_ema values ('a' ,'2005-01-11' ,83); insert into atp_ema values ('b' ,'2005-01-03' ,27); insert into atp_ema values ('b' ,'2005-01-04' ,27); insert into atp_ema values ('b' ,'2005-01-05' ,20); insert into atp_ema values ('b' ,'2005-01-06' ,22); insert into atp_ema values ('b' ,'2005-01-07' ,24); insert into atp_ema values ('b' ,'2005-01-10' ,21); insert into atp_ema values ('b' ,'2005-01-11' ,23); variable coeff number; begin :coeff := 0.66; end; / -- Correct algorithm, but can't run: select v.* ,(ema_term_1 + ema_term_2) as ema ,(avg(val) over (partition by symbol order by day rows between 3 preceding and current row)) as mean_4_day from ( select symbol ,day ,val ,(:coeff * val) as ema_term_1 ,( (1 - :coeff) * -- Of course ema is not defined here, so this fails with -- 'ORA-00904: invalid column name': nvl((lag(ema ,1) over (partition by symbol order by day)) ,val) ) as ema_term_2 from atp_ema ) v order by symbol ,day ; -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
