Snowflake中CTE调用MATCH_RECOGNIZE报错及解决方案咨询

错误根因 #

1. MATCH_RECOGNIZE上下文不支持错误 #

你遇到的第一个报错：

SQL compilation error: MATCH_RECOGNIZE not supported in this context.

是因为Snowflake当前版本对MATCH_RECOGNIZE的使用限制：MATCH_RECOGNIZE的返回结果不能在同一查询块内直接跟QUALIFY、ORDER BY子句后再嵌套进CTE，你原代码中在mr CTE里对MATCH_RECOGNIZE的结果直接加了QUALIFY和ORDER BY，触发了该限制。

2. CREATE语法错误 #

第二个报错：

syntax error line xx at position 0 unexpected 'create'.

是因为你把CREATE VIEW这个DDL语句插入到了WITH CTE的定义链中，CTE是单条查询语句的组成部分，不能嵌套DDL语句。

最优解决方案 #

优先推荐调整查询结构的方案，无需创建额外存储对象，即可直接在CTE中使用MATCH_RECOGNIZE的结果：
把MATCH_RECOGNIZE的计算和后续的QUALIFY拆分到两个独立的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' ),

-- 单独执行MATCH_RECOGNIZE计算，不附加QUALIFY/ORDER BY
mr_raw 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'
    )
),
-- 外层单独对MATCH_RECOGNIZE结果做QUALIFY过滤和排序
mr AS (
SELECT * 
FROM mr_raw
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
-- 后续自定义操作
)
-- 末尾补充主查询输出即可
SELECT * FROM cte3

其他可选方案 #

如果你的数据集很大，或者需要多次复用MATCH_RECOGNIZE的计算结果，可以选择以下方案：

临时表方案（性能最优，适合单次会话多次复用） #

临时表仅在当前会话有效，无需额外维护权限和存储生命周期：

-- 先单独计算MATCH_RECOGNIZE结果存入临时表
CREATE OR REPLACE TEMP TABLE mr_result AS
SELECT *
    FROM (
        SELECT *
        FROM dataset
        WHERE ID IS NOT NULL AND STATUS IS NOT NULL 
        QUALIFY FIRST_VALUE(STATUS) OVER (PARTITION BY ID ORDER BY CREATED_AT) = 'created'
    ) 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'
    );

-- 后续查询的CTE直接引用临时表即可
WITH mr AS (
    SELECT * 
    FROM mr_result
    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
-- 后续自定义操作
)
SELECT * FROM cte3

视图方案（适合长期复用逻辑） #

普通视图/物化视图都支持存储MATCH_RECOGNIZE逻辑，注意要单独执行CREATE语句，不要和CTE混写：

-- 单独执行DDL创建视图
CREATE OR REPLACE VIEW filtered_idents AS
SELECT *
    FROM (
        SELECT *
        FROM dataset
        WHERE ID IS NOT NULL AND STATUS IS NOT NULL 
        QUALIFY FIRST_VALUE(STATUS) OVER (PARTITION BY ID ORDER BY CREATED_AT) = 'created'
    ) 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'
    );

创建完成后即可在任意查询的CTE中直接引用该视图。

内容的提问来源于stack exchange，提问作者kimi