在雪花中，如何在下表中参考CTE

Question

我们在雪花中有以下工作查询：

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

select * from latest_track_metrics

为了稍微清理此代码，我们很想从track_metrics 中选择select max（“ week_id''）到其自己的行中，带有变量名称，因此：

with
    max_weekid as (select max("week_id") from track_metrics),
    latest_track_metrics as (
        select * from track_metrics
        where "week_id" = max_weekid // error on this line, not recognizing max_weekid
    )

select * from latest_track_metrics

但是后一个查询返回错误错误：无效标识符'max_weekid'（第5行）。我们试图将max_weekid与括号，报价，背景等包装。所有这些都没有运气。是否可以以这种方式调用CTE？

Answer 1

使用合格和窗口的最大值：

with latest_track_metrics as (
   select * 
   from track_metrics
   qualify "week_id" = max("week_id") over()
)
select * 
from latest_track_metrics;

Answer 2

将max_weekid作为子查询或表。

而不是，Week_ID =，您应该将其与Track_metrics一起使用JOIN子句。

也许这样的东西：

with
    max_weekid as (select max("week_id") AS M from track_metrics),
    latest_track_metrics as (
        select * from track_metrics
        inner join max_weekid
        on track_metrics.week_id = max_weekid.M)

select * from latest_track_metrics

Answer 3

错误是您将“ Week_ID”列与CTE“ max_weekid”匹配。 SQL应该是

with
    max_weekid as (select max("week_id") as max_week_id from track_metrics),
    latest_track_metrics as (
        select * from track_metrics
        where "week_id" = max_weekid.max_week_id // CTE.column
    )

select * from latest_track_metrics