已用窗口函数实现Average by ID，如何用Snowflake SQL每行展示Overall Average？

我希望在每行中显示overall average数值，该值应为(300+150+100)/3（即各ID对应的Average by ID的平均值）。我已通过窗口函数AVG(Value) OVER (PARTITION BY ID)实现了Average by ID列，预期结果如下表所示，请问如何用Snowflake SQL最优实现该需求？

直接使用嵌套窗口函数是Snowflake中最优的实现方式，无需额外子查询或CTE，仅需一次数据扫描即可完成计算：

SELECT 
    ID,
    "Desc",
    SUB_ID,
    Value,
    -- 计算每个ID的平均值（你已实现的逻辑）
    AVG(Value) OVER (PARTITION BY ID) AS "Average by ID",
    -- 基于ID级别的平均值，计算全局整体平均，每行重复显示
    ROUND(AVG(AVG(Value) OVER (PARTITION BY ID)) OVER (), 2) AS "Overall Average"
FROM your_table_name
ORDER BY ID, SUB_ID;

逻辑说明 #

1. 内层窗口函数AVG(Value) OVER (PARTITION BY ID)：按ID分组计算每个ID对应的平均值，即Average by ID列。
2. 外层窗口函数AVG(...) OVER ()：将所有ID的平均值作为数据集，计算它们的算术平均；OVER ()表示不分区、不排序，因此该值会在每一行重复显示。
3. ROUND(..., 2)用于将结果保留两位小数，匹配预期输出格式，若不需要可以去掉。

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