Snowflake窗口函数疑问：为何嵌套AVG可避免分组查询报错？

核心原因：SQL执行顺序与聚合后的数据范围 #

你的问题本质是没搞清楚GROUP BY和窗口函数的执行先后顺序，以及聚合后可用的数据范围：

1. 第一步：GROUP BY先执行
   当SQL执行到GROUP BY 1,2时，已经把原始表REP_DATA_SOURCES.DEAL_EXTENDED的数据按seller和date_base做了分组，此时：

   • 原始表的CREATED_AT_DATE、firstdate这些明细列已经被聚合操作“消耗”了，当前结果集里只剩下分组列（seller、date_base）和聚合函数计算出的结果（比如第三列的AVG(DATEDIFF(...))）。

2. 注释行报错的原因
   被注释的窗口函数：

   AVG(DATEDIFF('days',dex.CREATED_AT_DATE,dex.firstdate)) OVER (PARTITION BY dex.seller,date_base)
   

   这里试图直接引用原始表的CREATED_AT_DATE和firstdate，但这些列在GROUP BY之后已经不存在于当前结果集中了，所以Snowflake会报错“is not a valid group by expression”——因为你只能在聚合后的SELECT里使用分组列或者聚合后的计算值。

3. 双层AVG能运行的原因
   正常运行的窗口函数：

   AVG(AVG(DATEDIFF('days',dex.CREATED_AT_DATE,dex.firstdate))) OVER (PARTITION BY dex.seller,date_base)
   

   内层的AVG(DATEDIFF(...))是GROUP BY阶段已经计算好的聚合值（也就是SELECT里的第三列），属于当前结果集的有效列。外层的AVG(...) OVER (...)是对这个聚合值再做窗口计算——这时候操作的是聚合后的结果，完全符合SQL执行规则。

简化理解 #

你可以把GROUP BY后的结果看作一张临时表，这张表只有三列：seller、date_base、avg_diff（第三列的别名）。窗口函数只能基于这张临时表的列来计算，不能再去引用原始表的明细列。

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