Postgres货币转换UDF迁移至Snowflake遇关联子查询报错求助

问题根源 #

Snowflake的标量UDF若包含引用外部表列的关联子查询，查询优化器会尝试将其展开为关联子查询，这类子查询在部分场景下无法被执行引擎处理，从而触发"Unsupported subquery type cannot be evaluated"错误。UDTF写法不当（内部仍依赖关联子查询）时也会出现同样问题。

可行解决方案 #

1. 正确使用UDTF + LATERAL JOIN

UDTF需仅依赖传入参数，而非外部表列。示例实现如下：

先创建UDTF：

CREATE OR REPLACE FUNCTION CONVERT_CURRENCY(
    amount DECIMAL(18,4),
    from_curr VARCHAR(3),
    to_curr VARCHAR(3),
    trans_date DATE
)
RETURNS TABLE(converted_amount DECIMAL(18,4))
LANGUAGE SQL
AS $$
    -- 校验逻辑
    SELECT CASE
        WHEN from_curr = to_curr THEN amount
        WHEN amount IS NULL OR from_curr IS NULL OR to_curr IS NULL OR trans_date IS NULL THEN NULL
        ELSE amount * cl.exchange_rate
    END AS converted_amount
    FROM CURRENCY_LOOKUP cl
    WHERE cl.source_currency = from_curr
      AND cl.target_currency = to_curr
      AND cl.effective_date <= trans_date
      AND cl.expiry_date >= trans_date
    -- 确保仅返回有效汇率记录（若多条取最新）
    QUALIFY ROW_NUMBER() OVER (PARTITION BY cl.source_currency, cl.target_currency ORDER BY cl.effective_date DESC) = 1
$$;

关联查询中通过LATERAL JOIN调用：

SELECT t.*, conv.converted_amount
FROM TRANSACTIONS t
LEFT JOIN LATERAL TABLE(CONVERT_CURRENCY(t.amount, t.from_currency, t.to_currency, t.transaction_date)) conv
ON TRUE;

这种方式将UDTF逻辑与外部表解耦，避免关联子查询展开问题。

2. 重构标量UDF为非关联子查询模式

若偏好标量UDF，需确保函数内子查询仅依赖传入参数，不引用外部表列：

CREATE OR REPLACE FUNCTION CONVERT_CURRENCY_SCALAR(
    amount DECIMAL(18,4),
    from_curr VARCHAR(3),
    to_curr VARCHAR(3),
    trans_date DATE
)
RETURNS DECIMAL(18,4)
LANGUAGE SQL
AS $$
    CASE
        WHEN from_curr = to_curr THEN amount
        WHEN amount IS NULL OR from_curr IS NULL OR to_curr IS NULL OR trans_date IS NULL THEN NULL
        ELSE amount * (
            SELECT cl.exchange_rate
            FROM CURRENCY_LOOKUP cl
            WHERE cl.source_currency = from_curr
              AND cl.target_currency = to_curr
              AND cl.effective_date <= trans_date
              AND cl.expiry_date >= trans_date
            QUALIFY ROW_NUMBER() OVER (PARTITION BY cl.source_currency, cl.target_currency ORDER BY cl.effective_date DESC) = 1
        )
    END
$$;

关联查询中直接调用：

SELECT t.*, CONVERT_CURRENCY_SCALAR(t.amount, t.from_currency, t.to_currency, t.transaction_date) AS converted_amount
FROM TRANSACTIONS t;

子查询仅使用函数参数，Snowflake会将其视为非关联子查询，规避展开错误。

3. 封装逻辑到视图（可选）

若需完全封装转换逻辑，可创建整合JOIN与校验的视图：

CREATE OR REPLACE VIEW TRANSACTIONS_WITH_CONVERTED_AMOUNT AS
SELECT 
    t.*,
    CASE
        WHEN t.from_currency = t.to_currency THEN t.amount
        WHEN t.amount IS NULL OR t.from_currency IS NULL OR t.to_currency IS NULL OR t.transaction_date IS NULL THEN NULL
        ELSE t.amount * cl.exchange_rate
    END AS converted_amount
FROM TRANSACTIONS t
LEFT JOIN CURRENCY_LOOKUP cl
    ON cl.source_currency = t.from_currency
    AND cl.target_currency = t.to_currency
    AND cl.effective_date <= t.transaction_date
    AND cl.expiry_date >= t.transaction_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY t.transaction_id ORDER BY cl.effective_date DESC) = 1;

查询时直接使用视图即可，无需显式调用函数。

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