SQL - Calculate Exponential Moving Average with CTEs or Aggregates?

Question

The general formula for EMA:

EMA(xn) = α * xn + (1 - α) * EMA(xn-1)

Where:

xn = PRICE
α  = 0.5 -- Given 3-day SMA

The following recursive CTE does the job:

WITH recursive
ewma_3 (DATE, PRICE, EMA_3, rn)
AS (

    -- Anchor
    -- Feed SMA_3 to recursive CTE
    SELECT rows."DATE", rows."PRICE", sma.sma AS ewma, rows.rn
    FROM (
        SELECT "DATE", "PRICE", ROW_NUMBER() OVER(ORDER BY "DATE") rn
        FROM PRICE_TBL
    ) rows
    JOIN (
        SELECT "DATE",
           ROUND(AVG("PRICE"::numeric)
              OVER(ORDER BY "DATE" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 6) AS sma
        FROM PRICE_TBL
    ) sma ON sma."DATE" = rows."DATE"
    WHERE rows.rn = 3

    UNION ALL

    -- Recursive Member
    SELECT rows."DATE", rows."PRICE"
    -- Calculate EMA_3 below
    ,ROUND(((rows."PRICE"::numeric * 0.5) +
            (ewma.EMA_3 * (1 - 0.5))), 6) AS ewma
    , rows.rn
    FROM ewma_3 ewma
    JOIN (
        SELECT "DATE", "PRICE", ROW_NUMBER() OVER(ORDER BY "DATE") rn
        FROM PRICE_TBL
    ) rows ON ewma.rn + 1 = rows.rn
    WHERE rows.rn > 3
)

SELECT ewma_3.rn AS "ID", DATE, PRICE, EMA_3
FROM ewma_3
;

This is rather more a matter of efficiency and swiftness. A sample set of 9852 rows takes 11 s 631 ms to complete.

---

I've read that the aggregator keeps the result of the last calculated element, if so:

• Can someone provide a working example using aggregate functions?

I'm open to any suggestions to improve the CTE as well, but, I somehow believe aggregates will be faster. I also know that this is an older topic but I'm kind of new to posgres so any help is highly appreciated. Thanks!

---

UPDATE

Sample Data:

My CTE on a 7-day period returns (excl. DATE):

ID  PRICE       EMA_7
--+----------+-----------
7   0.529018    0.4888393
8   0.551339    0.5044642
9   0.580357    0.5234374
10  0.633929    0.5510603
11  0.642857    0.5740095
12  0.627232    0.5873151

Although the recursive CTE that @GordonLinoff provided is a split second faster, an aggregator (aggregated func) would be optimal for speed. I tried this but get:

ERROR: function ema(numeric, numeric) does not exist

Apparently, no function matches the given name and argument types. Explicit type casts? Clueless

Answer 1

I would write the recursive CTE as:

with recursive p as (
      select p.*, row_number() over (order by date) as seqnum
      from price_tbl p
     ),
     cte as (
      select seqnum, date, price, price * 1.0 as exp_avg
      from p
      where seqnum = 1
      union all
      select p.seqnum, p.date, p.price, (cte.exp_avg * 0.5  + p.price * 0.5) 
      from cte join
           p
           on p.seqnum = cte.seqnum + 1
     )
select *
from cte;

The 0.5 is really 0.5 and 1 - 0.5. You can easily adjust that for different alphas.

You can also do this using window functions:

select p.*,
       (sum(power((1 / 0.5), seqnum) * price) over (order by seqnum) +
        first_value(price) over (order by seqnum)
       ) / power((1 / 0.5), seqnum + 1)
from (select p.*,
             row_number() over (order by date) - 1 as seqnum
      from price_tbl p
     ) p;

The first_value() is because of a quirk of the calculation. The first and second values actually get counted the same amount, so the first amount needs to be "added back in".

That said, this is prone to overflow and divide-by-zero errors if your sequences are even a few dozen rows long.

Here is a db<>fiddle.