基于CTE的雪花匹配识别 [英] MATCH_RECOGNIZE with CTE in Snowflake
本文介绍了基于CTE的雪花匹配识别的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在带有几个CTE的查询中使用MATCH_RECOGNIZE
函数。运行查询时,我收到以下错误:
SQL编译错误:此上下文不支持Match_Recognition。
在我的查询中,Match_Recognition Partial前后有几个CTE,如下所示。
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
)
解决此问题的理想方法是什么?例如,创建常规视图、实例化视图或临时表等。我尝试创建视图,但遇到错误,不确定是否支持该视图。
如何在以后的其他CTE中使用MATCH_RECOGNIZE
的结果?
当我添加以下内容时,出现此错误:
位置0处的语法错误行xx意外为‘CREATE’。
create view filtered_idents AS
SELECT *
FROM cte2
MATCH_RECOGNIZE (
)
推荐答案
这似乎是一个未记录的限制(我要求我们的AWOWE文档团队解决此问题)。
同时,我可以建议将流程分成几个步骤来使用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.
两步解决方案:
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
;
这篇关于基于CTE的雪花匹配识别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文