CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
KIC
KIC

Reputation: 6121

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

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.

Upvotes: 7

Views: 9030

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

This may be a handy place to use a window specification:

select a.*,
       (case when row_number() over w > 1
             then avg(value) over w
        end) as running_average
from average a
window w as (order by nr rows between 1 preceding and current row);

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Since you happen to form the average only between 1 preceding row and the current one, just using lag() might be simplest:

select nr, value
     ,(value + lag(value, 1, NULL) OVER (ORDER BY nr)) / 2 AS "Moving-Average-2"
from average;

lag() has an overloaded variant that allows to provide a default value (as 3rd parameter) in case there is no row. Provide NULL and you are there. Or, since NULL is the default default anyway, just:

 ... ,(value + lag(value) OVER (ORDER BY nr)) / 2 AS "Moving-Average-2"

While the underlying table column is of type float, you need no cast to float in this case.

This is assuming the column value is defined NOT NULL (like indicated by your sample data). Else you also get NULL where the previous row has value IS NULL and the current row has a value, while avg() returns the value in this case! (Or this may be what you want anyway, given your question.)

Upvotes: 3

GMB
GMB

Reputation: 222482

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                |

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

i think null is not come in agv first row otherwise below will work by using 

BETWEEN 1 PRECEDING AND CURRENT ROW

select nr, value, 
       avg(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS "Moving-Average-2"
from average;

BETWEEN 1 PRECEDING AND CURRENT ROW

but you can haddle it by useing case when

select nr, value, 
       case when nr=1 then null else
       avg(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) end AS "Moving-Average-2"
from average;

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

online demo link

Upvotes: 1

Related Questions