Recursion with CTE not working for non-consecutive days but very slow with ROW_NUMBER

Question

I have to calculate Exponential Moving Average for stock price. In the query I made the recursion based on the PriceDate. But this is actually not working since there are missing days (weekends, holidays). I have tried to do it with ROW_NUMBER but it is running really slow (more than 40 minutes before I cancel it).

I am looking for a way to do it with dates but to take into account that there are missing days. Or if it is with ROW_NUMBER, I will need a serious speed optimization:

DECLARE @timePeriod12 INT = 12
DECLARE @smoothingFactor12 FLOAT = 2.0/(@timePeriod12 + 1);


;WITH SMA AS
(
  -- calculate SMA for each row for the last N days
  SELECT @smoothingFactor12 as alpha
      --  , ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY PriceDate DESC) rownum
       , Ticker
       , PriceDate
       , ClosePrice
       , AVG(ClosePrice) OVER (PARTITION BY Ticker ORDER BY PriceDate ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS sma
       
  FROM price.PriceHist
  WHERE PriceDate > (SELECT MAX(PriceDate) - 40 as PriceDate FROM price.PriceHist) --AND Ticker = 'AAPL'
  
),

EMA AS
(
    SELECT Ticker, PriceDate, ClosePrice, CONVERT(DECIMAL(10, 4), sma) AS ema 
      FROM SMA
            

     UNION ALL

     SELECT curr.Ticker, curr.PriceDate, curr.ClosePrice, CONVERT(DECIMAL(10,4), calc.ema) AS EMA 
       FROM EMA previous
      INNER
       JOIN SMA curr
         ON curr.PriceDate = previous.PriceDate + 1
        AND curr.Ticker = previous.Ticker
      CROSS
      APPLY (SELECT curr.alpha * curr.ClosePrice + (1 - curr.alpha) *  previous.ema AS ema) calc
      
)

INSERT INTO #tempEMA(Ticker, PriceDate, ClosePrice, EMA12)
SELECT * FROM EMA  

OPTION (MAXRECURSION 0)

GO

The result from the query above clearly shows that it is very wrong to use PriceDate the way I use it.

Answer 1

I took the advice from @Alex and moved SMA calculation in separate table. It worked great.

IF OBJECT_ID('tempdb..#tempSMA') IS NOT NULL DROP TABLE #tempSMA

CREATE TABLE #tempSMA
(
Ticker VARCHAR(20), 
PriceDate DATETIME2, 
ClosePrice DECIMAL(17,5), 
sma DECIMAL(17,5),
rownum INT,     
);

---

INSERT INTO #tempSMA(Ticker, PriceDate, ClosePrice, sma, rownum)
SELECT
         Ticker
       , PriceDate
       , ClosePrice
       , AVG(ClosePrice) OVER (PARTITION BY Ticker ORDER BY PriceDate ROWS BETWEEN 26 PRECEDING AND CURRENT ROW) AS sma 
       , ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY PriceDate) rownum
    FROM price.PriceHist
    WHERE PriceDate > (SELECT MAX(PriceDate) - 40 as PriceDate FROM price.PriceHist)

---

DECLARE @timePeriod12 INT = 12
DECLARE @smoothingFactor12 FLOAT = 2.0/(@timePeriod12 + 1);

---

;WITH SMA AS
(
  -- calculate SMA for each row for the last N days
  SELECT @smoothingFactor12 as alpha
       , rownum
       , Ticker
       , PriceDate
       , ClosePrice
       , sma
       
  FROM #tempSMA
  
),

EMA AS
(
    SELECT Ticker, PriceDate, ClosePrice, CONVERT(DECIMAL(10, 4), sma) AS ema, rownum 
    FROM SMA
    WHERE rownum = (SELECT MAX(rownum) / 2 FROM SMA)       

     UNION ALL

     SELECT curr.Ticker, curr.PriceDate, curr.ClosePrice, CONVERT(DECIMAL(10,4), calc.ema) AS EMA, curr.rownum 
       FROM EMA previous
      INNER
       JOIN SMA curr
         ON curr.rownum = previous.rownum + 1
        AND curr.Ticker = previous.Ticker
      CROSS
      APPLY (SELECT curr.alpha * curr.ClosePrice + (1 - curr.alpha) *  previous.ema AS ema) calc
      
)

INSERT INTO #tempEMA(Ticker, PriceDate, ClosePrice, EMA12)
SELECT Ticker, PRiceDate, ClosePrice, ema FROM EMA  

OPTION (MAXRECURSION 0)

GO