rolling moving average in SQL

Question

I'm looking how to do a moving average in postgresql. What I already have done is:

with sma as (
select date,
       avg(col) over(order by date rows between 20 preceding and current row) mov_avg
    from eurusd_ohlc
)

select date, lag(mov_avg,1) over(order by date)  from sma

Which yields me the result of the moving average, but it also calculates the value even if there isn't enough terms to calculate a 20 period moving average. The result I get with this is as follows:

-----------------------------------------------------
|             date         |           sma          |
|2020-02-20 03:27:35.140751|    NULL                |
|2020-02-20 04:19:17.088462|    1.07966             |
|2020-02-20 05:54:44.060929|    1.0796299999999999  |
|2020-02-20 06:41:32.916934|    1.07964             |
|2020-02-20 07:11:59.667919|    1.0794899999999998  |
|2020-02-20 07:26:06.342439|    1.07938             |
|2020-02-20 07:44:15.313053|    1.0792033333333333  |
|2020-02-20 08:06:31.498739|    1.0791971428571427  |
|2020-02-20 08:26:12.278109|    1.07920625          |
|2020-02-20 08:50:23.925312|    1.079178888888889   |
|2020-02-20 09:14:48.951868|    1.079202            |

While this is correct, because it calculates the average with 1, 2, 3 and all the way up to 20 values and then it actually converges, I would like to have the first 20 rows as "nulls", since there isn't 20 rows to calculate an average and start getting an average in row n°20 (or 21 if I'm using lag).

How can I achieve this?

Answer 1

I think you can simplify the logic:

select date, avg(col) over (order by date rows between 21 preceding and 1 preceding)
from eurusd_ohlc

Use a case expression to get the nulls:

select date,
       (case when row_number() over (order by date) >= 21
             then avg(col) over (order by date rows between 21 preceding and 1 preceding)
        end) as mov_avg
from eurusd_ohlc