CODEWITHSUNDEEP
sqlpostgresqlwhere-clausepartitionrolling-average
Michele Cordini
Michele Cordini

Reputation: 117

Rolling average only when specific percentage of null values

I need to get the rolling average of a value on the 8 days before to the considered record. However I want it only calculated when I have at least 6 non-null values out of the 8 recorded (8 days). In other terms calculate the rolling average on 8 days only when you have at least 75% valid values.

Here's my query:

select
    case when
        row_number() over (order by DATEX) > 8
        then
            avg(VALUEX) OVER (
                ORDER BY DATEX DESC
                ROWS BETWEEN 1 following AND 8 FOLLOWING
            )
        else null end
from (
    select *
    from TABLEX
) s

How can I make it return the rolling average only when I have at least 6 non-null in my 8 prior values and return null or something else when I have less than 6 non-null values?

Upvotes: 0

Views: 61

Answers (1)

eshirvana
eshirvana

Reputation: 24568

just count the number of non-null values and filter based on that ( if this is what you want) :

select case when cnt >=6 then avg else null end as avg 
from (
    select
        count(VALUEX) OVER w as cnt
        , avg(VALUEX) OVER w as avg
    from TABLEX
    window w as (ORDER BY DATEX DESC ROWS BETWEEN 1 following AND 8 FOLLOWING)
) s

Upvotes: 0

Related Questions