如果任何行为 NULL，则组内的移动平均值返回 NULL（Snowflake - SQL）

Question

我需要计算每组列的移动平均值（按id分区）。唯一的问题是，如果相应窗口中的任何值为 NULL，我需要结果为 NULL。

预期行为示例（对于给定的 id 和窗口大小=3）：

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

移动平均值的前 3 行为 NULL，因为第一个值（包含在前 3 个窗口中）为 NULL。 mov_ave_A 的第 4 行等于 1，因为它是 A 第 2 行到第 4 行的平均值，依此类推。

我尝试过：

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

但我明白了

“功能 CASE 不支持滑动窗框”。

另外，我真的希望解决方案简短而简单，因为我需要创建 6 个这样的列。所以，我必须重复这个逻辑六次。

Answer 1

您的查询的问题是 OVER 子句位于 END 之后。我相信这应该有效。每个窗口函数都需要有 OVER 子句，一次用于 COUNT，一次用于 AVG。 COUNT 是比使用 SUM 检查 NULL 更简单的方法

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

Answer 2

使用以下 case 表达式：

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