MATCH_RECOGNIZE с CTE в Snowflake

Question

Я использую функцию в запросе с несколькими CTE. При запуске запроса я получил следующую ошибку:

Ошибка компиляции SQL: MATCH_RECOGNIZE не поддерживается в этом контексте.

В моем запросе есть несколько CTE до и после MATCH_RECOGNIZE частично, как показано ниже.

      WITH cte1 AS (
SELECT *
FROM dataset
WHERE ID IS NOT NULL AND STATUS IS NOT NULL ),

cte2 AS (
SELECT *
FROM cte1
QUALIFY FIRST_VALUE(STATUS) OVER (PARTITION BY ID ORDER BY CREATED_AT) = 'created' )

mr as (
SELECT *
    FROM cte2
    MATCH_RECOGNIZE (
      PARTITION BY ID
      ORDER BY CREATED_AT
      MEASURES MATCH_NUMBER() AS mn,
               MATCH_SEQUENCE_NUMBER AS msn
      ALL ROWS PER MATCH
      PATTERN (c+m+)
      DEFINE
         c AS status='created'
        ,m AS status='missing_info'
        ,p AS status='pending'
    ) m1
    QUALIFY (ROW_NUMBER() OVER(PARTITION BY mn, ID ORDER BY msn) = 1)
          OR(ROW_NUMBER() OVER(PARTITION BY mn, ID ORDER BY msn DESC)=1)
    ORDER BY ID, CREATED_AT ),

cte3 as (
SELECT *
FROM mr
-- some other operations
)

Каков был бы идеальный подход к решению этой проблемы? например, создание обычного представления, материализованного представления или временной таблицы и т. д. Я попытался создать представление, но получил ошибку, не уверен, поддерживается ли оно. Как я могу использовать результат MATCH_RECOGNIZE в других более поздних CTE?

Когда я добавляю следующее, появляется эта ошибка:

синтаксическая ошибка строка xx в позиции 0 неожиданное 'create'.

      create view filtered_idents AS

SELECT *
FROM cte2
MATCH_RECOGNIZE (

)

Answer 1

Кажется, это недокументированное ограничение (я попросил нашу замечательную команду разработчиков исправить это).

А пока я мог бы предложить разделить процесс на шаги, чтобы использовать match_recognize Результаты.

Воспроизведение ошибки:

      with data as (
    select $1 company, $2 price_date, $3 price
    from values('a',1,10), ('a',2,15)
), cte as (

    select *
    from data match_recognize(
        partition by company
        order by price_date
        measures match_number() as "MATCH_NUMBER"
        all rows per match omit empty matches
        pattern(overavg*)
        define
            overavg as price > avg(price) over (rows between unbounded
                                      preceding and unbounded following)
    )
) 
select * from cte

-- 002362 (0A000): SQL compilation error: MATCH_RECOGNIZE not supported in this context.

2-х ступенчатое решение:

      with data as (
    select $1 company, $2 price_date, $3 price
    from values('a',1,10), ('a',2,15)
)

select *
from data match_recognize(
    partition by company
    order by price_date
    measures match_number() as "MATCH_NUMBER"
    all rows per match omit empty matches
    pattern(overavg*)
    define
        overavg as price > avg(price) over (rows between unbounded
                                  preceding and unbounded following)
)
;


with previous_results as (
    select *
    from table(result_scan(last_query_id()))
)

select * 
from previous_results
;

Answer 2

Кими, попробуй твой фрагмент, я получаю:

Ошибка компиляции SQL: строка синтаксической ошибки 11 в позиции 0 неожиданный «mr». синтаксическая ошибка строки 17 в позиции 6 неожиданные «МЕРЫ».

В строке 9 отсутствует завершающая запятая. Когда я добавляю один, а затем завершаю все простым оператором select, я больше не получаю синтаксических ошибок, я получаю только ошибки поиска имени (естественно, ожидаемые).