sql - 在 Snowflake 的公用表表达式中使用 "match_recognize"

Question

更新:这是 here 的回答。

我正在组合一个有点复杂的查询，以在 Snowflake 中对大型时间序列数据集进行事件检测、连接和基于时间的装箱。我最近注意到 match_recognize 让我可以 Eloquent 地检测时间序列事件，但每当我尝试在公用表表达式中使用 match_recognize 表达式时(with .. as ..)，我收到以下错误:

SQL compilation error: MATCH_RECOGNIZE not supported in this context.

我进行了大量搜索/阅读，但没有发现 CTE 中关于 match_recognize 的任何记录限制。这是我的查询:

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;

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

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

Answer 1

非递归 cte 总是可以重写为内联 View :

--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

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