'Moving average within groups that returns NULL if any row is NULL (Snowflake - SQL)

I need to caluclate the moving average of a column per group (partitioned by id). The only twist is that I need the result to be NULL if any value in the corresponding window is NULL.

Example of expected behaviour (for a given id and window size=3):

A mov_ave_A
NULL NULL
1 NULL
1 NULL
1 1
4 2

The first 3 rows of the moving average are NULL, because the first value (which is included in the first 3 windows) is NULL. Row 4 of mov_ave_A is equal to 1 because it's the average of rows 2 to 4 of A, and so on.

I tried:

CASE WHEN SUM(CASE WHEN a IS NULL THEN 1 ELSE 0 END) = 0 THEN AVG(a) ELSE NULL END
    OVER (
    PARTITION BY id
    ORDER BY date_month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS mov_ave_A

but I get

"Sliding window frame unsupported for function CASE".

Also, I'd really like the solution to be short and simple as I need to create 6 such columns. So, I'll have to repeat the logic 6 times.

sqlwindow-functions


Solution 1:[1]

The issue with your query is the OVER clause is after the END. I believe this should work. You need to have the OVER clause for each window function so once for COUNT and once for AVG. COUNT is a easier to way to check for NULL's then using SUM

SELECT 
    *
    ,CASE
        /*Check for 3 values in a, if so return the rolling AVG value. Implicit ELSE NULL*/ 
        WHEN COUNT(a)   OVER (PARTITION BY ID ORDER BY date_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3 
        THEN AVG(a)     OVER (PARTITION BY ID ORDER BY date_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    END AS mov_ave_A
FROM YourTable

Solution 2:[2]

Use the following case expression:

CASE WHEN COUNT(a) OVER (
    PARTITION BY id
    ORDER BY date_month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) = 3 THEN AVG(a) OVER (
    PARTITION BY id
    ORDER BY date_month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) END AS mov_avg

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stephan
Solution 2

Related Questions