在Snowflake中基于已计算移动平均值计算修正移动平均值

这个需求属于迭代依赖型计算——事件行的Mod_MA值依赖于之前已计算出的Mod_MA结果，普通窗口函数（如AVG OVER()）无法处理这种递归逻辑，需要用**递归CTE（公共表表达式）**实现。

示例数据集 #

假设你的数据集结构如下（含预期Mod_MA结果）：

递归CTE实现方案 #

以下是通用SQL写法（兼容SQL Server、PostgreSQL、MySQL 8.0+）：

WITH RankedData AS (
    -- 按日期排序生成连续行号，确保递归顺序正确
    SELECT 
        Date,
        ST,
        Event,
        ROW_NUMBER() OVER (ORDER BY Date) AS rn
    FROM YourDataset
),
RecursiveMA AS (
    -- 锚点：第一行直接取ST作为Mod_MA
    SELECT 
        rn,
        Date,
        ST,
        Event,
        CAST(ST AS DECIMAL(10,2)) AS Mod_MA
    FROM RankedData
    WHERE rn = 1

    UNION ALL

    -- 递归计算后续每一行的Mod_MA
    SELECT 
        r.rn,
        r.Date,
        r.ST,
        r.Event,
        CASE
            -- Event为空时，Mod_MA等于当前ST值
            WHEN r.Event IS NULL THEN CAST(r.ST AS DECIMAL(10,2))
            -- Event存在时，取最近4个已计算的Mod_MA的平均值
            ELSE (
                SELECT AVG(Mod_MA)
                FROM RecursiveMA
                WHERE rn BETWEEN r.rn - 4 AND r.rn - 1
            )
        END AS Mod_MA
    FROM RankedData r
    JOIN RecursiveMA rm ON r.rn = rm.rn + 1
)
-- 输出最终结果，按日期排序
SELECT Date, ST, Event, Mod_MA
FROM RecursiveMA
ORDER BY Date;

关键说明 #

1. 行号的作用：用ROW_NUMBER()确保数据按日期顺序生成连续行号，保证递归时逐行计算的顺序正确。
2. 递归逻辑：
   • 锚点成员处理第一行，直接赋值ST给Mod_MA。
   • 递归成员逐行判断：若Event为空则直接取当前ST；若Event存在，则查询递归结果中最近4行的Mod_MA平均值。
3. 边界情况处理：
   • 如果前4行出现Event，会自动取现有所有前面的Mod_MA平均值（比如第2行有事件时，取第1行的Mod_MA）。
   • 若需要严格取满4个值（不足时用默认值或跳过），可在子查询中添加COUNT(Mod_MA) = 4的判断，根据业务需求调整。

内容的提问来源于stack exchange，提问作者abatra