如何解决Snowflake中CTE引发的Unsupported subquery error错误？

原查询的错误源于CTE C 的JOIN条件中嵌套了关联子查询（引用外部表Y.Eid和B.Date），这类子查询是Snowflake不支持的类型。以下是两种可行的改写方案：

方案一：新增CTE预计算最小日期 #

提前通过CTE计算出每个Z1.id（对应Y.Eid）和B.Date对应的最小Z1.Date，再关联Z表匹配该值：

With A as ( ),
B as (),
-- 预计算每个id和日期对应的最小Z1日期
Z1_MIN_DATE as (
    SELECT 
        id,
        Date,
        MIN(Date) as min_z_date
    FROM Z1
    GROUP BY id, Date
),
C as ( 
    select * 
    from B
    Left join X on B.id = x.id
    Left join Y on B.id = Y.Eid
    -- 先关联预计算的最小日期结果，再匹配Z表
    Left join Z1_MIN_DATE 
        on Z1_MIN_DATE.id = Y.Eid 
        and Z1_MIN_DATE.Date = B.Date
    Left join Z 
        on Z.uid = Y.Eid 
        and Z.Date = Z1_MIN_DATE.min_z_date
)

方案二：用窗口函数筛选目标记录 #

通过窗口函数标记Z表中每个uid对应目标日期的最小记录，再关联到主查询：

With A as ( ),
B as (),
-- 计算Z1的最小日期并关联Z表，再用窗口函数筛选唯一最小记录
Z_FINAL as (
    SELECT 
        Z1.id as eid,
        Z1.Date as b_date,
        Z.*,
        ROW_NUMBER() OVER (PARTITION BY Z1.id, Z1.Date ORDER BY Z.Date) as rn
    FROM Z1
    LEFT JOIN Z ON Z.uid = Z1.id AND Z.Date = Z1.Date
    GROUP BY Z1.id, Z1.Date, Z.*
),
C as ( 
    select * 
    from B
    Left join X on B.id = x.id
    Left join Y on B.id = Y.Eid
    Left join Z_FINAL 
        on Z_FINAL.eid = Y.Eid 
        and Z_FINAL.b_date = B.Date
        and Z_FINAL.rn = 1 -- 只取最小日期的第一条记录
)

两种方案都规避了Snowflake不支持的关联子查询写法，改用预计算或窗口函数实现相同逻辑，可正常执行查询。

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