为什么该Snowflake子查询报Unsupported subquery type无法执行？

SQL compilation error: Unsupported subquery type cannot be evaluated

该报错是Snowflake对关联子查询的支持限制导致的：你在SELECT列表中写的关联子查询属于行匹配场景，Snowflake无法直接解析执行。你的本质需求是按number_id、country分组后，取每组最大datetime对应的整行数据，再做字段转换写入TBL_B，不需要用嵌套子查询实现。

用ROW_NUMBER()窗口函数按分组内时间倒序排序，取排序第一的行即可，直接避免关联子查询：

INSERT INTO TBL_B (number_id, country, status, claimed_date, bought_date, returned_date)
SELECT 
    number_id,
    country,
    status,
    CASE WHEN status = 'CLAIMED' THEN datetime ELSE '2999-12-31' END AS claimed_date,
    CASE WHEN status = 'BOUGHT' THEN datetime ELSE '2999-12-31' END AS bought_date,
    CASE WHEN status = 'RETURNED' THEN datetime ELSE '2999-12-31' END AS returned_date
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY number_id, country ORDER BY datetime DESC) AS rn
    FROM TBL_A
) t
WHERE rn = 1;

执行后写入TBL_B的数据符合预期：USA分组取最大日期2021-10-12对应的RETURNED状态，AU分组取最大日期2021-09-11对应的BOUGHT状态。

如果要保留原来先分组取最大时间的逻辑，可以把关联子查询换成INNER JOIN，Snowflake对JOIN的支持没有限制：

INSERT INTO TBL_B (number_id, country, status, claimed_date, bought_date, returned_date)
SELECT
    w.number_id, 
    w.country, 
    w.status,
    CASE w.status WHEN 'CLAIMED' THEN w.datetime ELSE '2999-12-31' END claimed_date,
    CASE w.status WHEN 'BOUGHT' THEN w.datetime ELSE '2999-12-31' END bought_date,
    CASE w.status WHEN 'RETURNED' THEN w.datetime ELSE '2999-12-31' END returned_date
FROM (
    SELECT x.*, z.status
    FROM (
        SELECT a.number_id, a.country, max(datetime) as datetime
        FROM TBL_A a
        GROUP BY a.number_id, a.country
    ) x
    INNER JOIN TBL_A z 
        ON z.number_id = x.number_id 
        AND z.country = x.country 
        AND z.datetime = x.datetime
) w;

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