Snowflake中计算当前Sprint与后续所有Sprint的标准差及平均值的技术方案咨询

针对你需要计算每个Sprint与后续所有Sprint的平均值、标准差的需求，结合Snowflake的特性，这里提供两种适配不同场景的方案，对应你提到的“移动平均扩展”和“跨Sprint差值统计”两种可能的需求方向：

方案1：计算当前Sprint+后续所有Sprint的字段集合的统计值 #

这个方案是你之前实现的“当前+下一个Sprint移动平均”的扩展，把统计范围从“下一个”扩大到“所有后续”，用窗口函数就能高效实现：

步骤说明 #

1. 先过滤掉全null的无效记录，保证数据准确性；
2. 按timestamp给每个Sprint生成唯一顺序号，确保统计的时间顺序正确；
3. 使用窗口聚合函数，将统计范围设为「当前行到最后一行」，直接计算平均值和标准差；
4. 给没有后续Sprint的最后一条记录单独设置stddev为null。

示例代码 #

WITH cleaned_sprints AS (
    SELECT 
        id,
        committed,
        delivered,
        timestamp,
        -- 按时间排序生成Sprint顺序标识
        ROW_NUMBER() OVER (ORDER BY TO_DATE(timestamp, 'MM-DD-YYYY') ASC) AS sprint_seq
    FROM your_table_name
    -- 过滤全为空的无效记录
    WHERE NOT (id IS NULL AND committed IS NULL AND delivered IS NULL AND timestamp IS NULL)
),
rolling_stats AS (
    SELECT 
        *,
        -- 当前Sprint + 所有后续Sprint的delivered平均值
        AVG(delivered) OVER (
            ORDER BY sprint_seq 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS avg_delivered,
        -- 当前Sprint + 所有后续Sprint的delivered样本标准差（用STDDEV_POP可算总体标准差）
        STDDEV_SAMP(delivered) OVER (
            ORDER BY sprint_seq 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS stddev_delivered
    FROM cleaned_sprints
)
SELECT 
    id,
    committed,
    delivered,
    timestamp,
    -- 最后一个Sprint无后续，stddev设为null
    CASE WHEN sprint_seq = (SELECT MAX(sprint_seq) FROM cleaned_sprints) 
         THEN NULL 
         ELSE stddev_delivered 
    END AS stddev,
    avg_delivered AS avg
FROM rolling_stats
ORDER BY sprint_seq;

方案2：计算当前Sprint与后续每个Sprint的字段差值的统计值 #

如果你的需求是统计当前Sprint与后续每个Sprint的差值的平均值和标准差（比如后续delivered - 当前delivered的分布情况），可以用自连接的方式实现：

示例代码 #

WITH cleaned_sprints AS (
    SELECT 
        id,
        committed,
        delivered,
        timestamp,
        ROW_NUMBER() OVER (ORDER BY TO_DATE(timestamp, 'MM-DD-YYYY') ASC) AS sprint_seq
    FROM your_table_name
    WHERE NOT (id IS NULL AND committed IS NULL AND delivered IS NULL AND timestamp IS NULL)
)
SELECT 
    cd1.id,
    cd1.committed,
    cd1.delivered,
    cd1.timestamp,
    -- 当前与后续所有Sprint的delivered差值的平均值
    AVG(cd2.delivered - cd1.delivered) AS avg_diff,
    -- 差值的样本标准差（无后续记录时自动返回null）
    STDDEV_SAMP(cd2.delivered - cd1.delivered) AS stddev_diff
FROM cleaned_sprints cd1
LEFT JOIN cleaned_sprints cd2 
    ON cd2.sprint_seq > cd1.sprint_seq
GROUP BY cd1.id, cd1.committed, cd1.delivered, cd1.timestamp, cd1.sprint_seq
ORDER BY cd1.sprint_seq;

关键提示 #

• 窗口函数方案的执行效率远高于自连接，适合大数据量的场景；
• 如果你需要统计committed字段，只需要把代码中的delivered替换成committed即可；
• STDDEV_SAMP是样本标准差，若需要计算总体标准差，替换为STDDEV_POP即可。

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