Calculate moving average using SQL window functions with leading null's where not enough data is avaliable

Question

I want to calculate a moving average using SQL window functions. The following example of a 2 "day" moving average basically works fine, but It also calculates an average if only one data point is available. I rather want the average to be null as long as not enough data is available

create table average(
    nr int,
    value float
);

insert into average values (1, 2), (2, 4), (3, 6), (3, 8), (4, 10);

SELECT
    nr, 
    value, 
    AVG(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)::FLOAT AS "Moving-Average-2"
FROM average;

result:

1   2   2
2   4   3
3   6   5
3   8   7
4   10  9

expected result:

1   2   null
2   4   3
3   6   5
3   8   7
4   10  9

EDIT 1: Of course the average can be anything not only 2.

Answer 1

You could use another window function (COUNT()) to make sure that at least two records are available in the window before doing the computation, like:

SELECT
    nr, 
    value, 
    CASE WHEN COUNT(*) OVER(ORDER BY nr ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) > 1
        THEN AVG(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)::FLOAT 
        ELSE NULL
    END AS "Moving-Average-2"
FROM average;

Demo on DB Fiddle:

| nr  | value | Moving-Average-2 |
| --- | ----- | ---------------- |
| 1   | 2     |                  |
| 2   | 4     | 3                |
| 3   | 6     | 5                |
| 3   | 8     | 7                |
| 4   | 10    | 9                |