Spark SQL/Databricks中如何将CTE的值传入自定义UDF？

你尝试将CTE中的值传入UDF但运行失败，报错信息为：Error: A column, variable, or function parameter with name fx_date_new.fxdate cannot be resolved，以下是问题分析与可行解决方案：

原错误写法回顾 #

写法一：UDF参数直接使用未包裹的子查询 #

WITH fx_date_new AS (
   SELECT CASE
         WHEN '2025-01-01' > current_date() 
               THEN CAST(date_format(add_months(current_date(), -1), 'yyyyMM') AS INT)
               ELSE CAST(date_format(add_months('2025-01-01', -1), 'yyyyMM') AS INT)
         END AS fxdate
)

select calcccy (1000,
                     'USD',
                     'EUR',
                     select fxdate from fx_date_new
                    );

写法二：用普通INNER JOIN关联UDF #

WITH fx_date_new AS (
   SELECT CASE
         WHEN '2025-01-01' > current_date() 
               THEN CAST(date_format(add_months(current_date(), -1), 'yyyyMM') AS INT)
               ELSE CAST(date_format(add_months('2025-01-01', -1), 'yyyyMM') AS INT)
         END AS fxdate
)

SELECT 
    fx_date_new.fxdate,
    UDF.*
FROM 
    fx_date_new
INNER JOIN     -- or CROSS APPLY 
    gold.calcccy(
                    1000, 
                    'USD', 
                    'EUR',
                    fx_date_new.fxdate
                    ) AS UDF; 

报错核心原因 #

1. 写法一中，未加括号的子查询无法被SQL引擎识别为标量参数，导致参数格式不合法；
2. 写法二中，多数SQL引擎不支持在普通JOIN的UDF调用里直接引用左侧CTE的列，需要用支持行级关联的语法实现参数传递。

可行解决方案 #

方案1：给子查询加括号，作为标量参数传递 #

如果CTE只会返回单行数据，给UDF参数里的子查询加上括号，让引擎识别为单个标量值：

WITH fx_date_new AS (
   SELECT CASE
         WHEN '2025-01-01' > current_date() 
               THEN CAST(date_format(add_months(current_date(), -1), 'yyyyMM') AS INT)
               ELSE CAST(date_format(add_months('2025-01-01', -1), 'yyyyMM') AS INT)
         END AS fxdate
)

select calcccy (1000,
                     'USD',
                     'EUR',
                     (select fxdate from fx_date_new)
                    );

方案2：使用LATERAL JOIN（兼容多数现代SQL引擎） #

如果你的SQL引擎支持LATERAL JOIN（如PostgreSQL、BigQuery、Snowflake等），用它替代普通INNER JOIN，实现行级关联调用UDF：

WITH fx_date_new AS (
   SELECT CASE
         WHEN '2025-01-01' > current_date() 
               THEN CAST(date_format(add_months(current_date(), -1), 'yyyyMM') AS INT)
               ELSE CAST(date_format(add_months('2025-01-01', -1), 'yyyyMM') AS INT)
         END AS fxdate
)

SELECT 
    f.fxdate,
    UDF.*
FROM 
    fx_date_new f
LATERAL JOIN 
    gold.calcccy(1000, 'USD', 'EUR', f.fxdate) AS UDF;

方案3：将CTE结果赋值给变量（适用于SQL Server等支持变量的引擎） #

如果使用SQL Server这类支持变量的引擎，可以先把CTE的结果存入变量，再传入UDF：

DECLARE @fxdate INT;

WITH fx_date_new AS (
   SELECT CASE
         WHEN '2025-01-01' > GETDATE() 
               THEN CAST(FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyyMM') AS INT)
               ELSE CAST(FORMAT(DATEADD(MONTH, -1, '2025-01-01'), 'yyyyMM') AS INT)
         END AS fxdate
)
SELECT @fxdate = fxdate FROM fx_date_new;

SELECT gold.calcccy(1000, 'USD', 'EUR', @fxdate);

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