EWMA in PRQL with DuckDB

[eitsupi]

Just a link to a book that compares this: https://eitsupi.github.io/querying-with-prql/timeseries#sec-moving-ave

Very cool!

I also used an SMA like that in my presentations, as well as an EWMA which you can find in this Google Colab if you want to include that as well? https://colab.research.google.com/drive/1asOWKjQbv6VWW9WjWR2Mer7PLmm0S0dT?usp=sharing

Originally posted by @snth in https://github.com/PRQL/prql/issues/3618#issuecomment-1760829598

[eitsupi]

@snth Thanks for sharing that! That looks awesome!

I don't see myself adding it anytime soon, but it would be great to have it shared.

let calc_ewma = alpha:null span:1 rel -> (
  from rel
  filter t==1
  select {t, x, ewma=x}
  loop (
    join rel (that.t==this.t+1)
    select {t, x, ewma = (alpha ?? 2/(span+1))*x + (1-(alpha ?? 2/(span+1)))*ewma}
  )
)

let data = (
  from tris_with_sma
  sort Date
  derive {t=row_number this, x=google_tri}
)

from data
#calc_ewma alpha:2/(10+1)
calc_ewma span:10
derive google_ewma10 = ewma
select {Date=t, google_tri=x, google_ewma10=ewma}