# Re: [sqlite] windowing functions != recursive functions

```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.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/

```