Snowflake中如何正确引用CTE解决无效标识符报错

Snowflake完全支持多CTE的串行引用，你遇到的报错和CTE能力无关，是引用方式错误：CTE本质是单行/多行的临时结果表，不是可以直接赋值比较的标量变量，你直接在WHERE子句里写= max_weekid时，SQL引擎会把max_weekid解析为当前表的列名，找不到对应列就会抛出标识符无效的错误。
另外你最初写的max_weekid CTE没有给聚合计算出的最大值指定别名，就算引用逻辑写对，也可能触发列名不存在的问题。

你可以选以下任意一种实现CTE拆分，都能正常运行：

写法1：子查询标量引用 #

把CTE作为子查询放在等值判断右侧，和你原始嵌套子查询的逻辑完全对齐，只是把内层子查询提前抽成了独立CTE：

with
    max_weekid as (select max("week_id") as max_wid from track_metrics),
    latest_track_metrics as (
        select * from track_metrics
        where "week_id" = (select max_wid from max_weekid)
    )
select * from latest_track_metrics

写法2：交叉连接关联 #

因为max_weekid是只有1行1列的结果集，用交叉连接关联不会产生数据膨胀，写法更简洁，执行效率和写法1没有差异：

with
    max_weekid as (select max("week_id") as max_wid from track_metrics),
    latest_track_metrics as (
        select t.* 
        from track_metrics t
        cross join max_weekid m
        where t."week_id" = m.max_wid
    )
select * from latest_track_metrics

• 提示：以上CTE写法无版本兼容性问题，适配所有Snowflake正式版本。

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