在 Snowflake 的公共表表达式中使用“match_recognize”

更新：已回答here https://stackoverflow.com/questions/68544567/match-recognize-with-cte-in-snowflake/68550312#68550312.

我正在将一个有点复杂的查询放在一起，以使用 Snowflake 中的大型时间序列数据集进行事件检测、连接和基于时间的分箱。我最近注意到match_recognize让我雄辩地检测时间序列事件，但每当我尝试使用match_recognize公共表表达式中的表达式 (with .. as ..），我收到以下错误：

SQL 编译错误：在此上下文中不支持 MATCH_RECOGNIZE。

我已经做了很多搜索/阅读，但没有发现任何记录在案的限制match_recognize在 CTE 中。这是我的查询：

with clean_data as (
    -- Remove duplicate entries
    select distinct id, timestamp, measurement
    from dataset
),

label_events as (
    select *
    from clean_data
        match_recognize (
            partition by id
            order by timestamp
            measures
                match_number() as event_number
            all rows per match
            after match skip past last row
            pattern(any_row row_between_gaps+)
            define
                -- Classify contiguous sections of datapoints with < 20min between adjacent points.
                row_between_gaps as datediff(minute, lag(timestamp), timestamp) < 20
        )
)

-- Do binning with width_bucket/etc. here
select id, timestamp, measurement, event_number
from label_events;

我得到了与上面相同的错误。

这是我没有看到的限制，还是我做错了什么？

非递归 cte 始终可以重写为内联视图：

--select ...
--from (
select id, timestamp, measurement, event_number
from (select distinct id, timestamp, measurement
     from dataset) clean_data
match_recognize (
        partition by id
        order by timestamp
        measures
            match_number() as event_number
        all rows per match
        after match skip past last row
        pattern(any_row row_between_gaps+)
        define
            -- Classify contiguous sections of datapoints with < 20min between adjacent points.
            row_between_gaps as datediff(minute, lag(timestamp), timestamp) < 20
    )mr
-- ) -- if other transformations are required

这并不理想，但至少它允许查询运行。