Snowflake UDF报错：不支持的子查询类型无法求值，求解决方案

问题背景 #

我需要创建一个UDF，根据门店ID（store ID）将UTC时间戳转换为本地时间。涉及两张表：门店表（单门店对应一行数据）、州级信息表（单州对应一行数据，用于处理虚拟门店场景，比如某州所有远程员工的统一门店）。

已尝试的解决方法 #

为了让UDF每个输入行仅返回一行结果，我试过以下方案：

• 使用Min()、Max()和Any_Value()聚合函数
• 添加LIMIT 1限制结果行数
• 将逻辑放入子查询中，并对子查询结果做限制或聚合

优化步骤：创建时区映射视图 #

为简化逻辑，我基于两张表创建了视图，用于返回每个门店编号对应的ANSI时区名称：

CREATE OR REPLACE VIEW TEAMS.Team_Marketing.loc_num_ansi_tz
AS  (   SELECT              VVV_LOC.location_key                                        AS loc_num
                        ,   CASE    WHEN    VVV_LOC.utc_offset_standard =       0   AND STO.state       IS          NULL                                THEN    'America/New_York'
                                    WHEN    VVV_LOC.utc_offset_standard =       0   AND STO.utc_offset  =       5                                       THEN    'America/New_York'
                                    WHEN    VVV_LOC.utc_offset_standard =       0   AND STO.utc_offset  =       6                                       THEN    'America/Chicago'
                                    WHEN    VVV_LOC.utc_offset_standard =       0   AND STO.utc_offset  =       7       AND STO.uses_dst    =       0   THEN    'America/Phoenix'
                                    WHEN    VVV_LOC.utc_offset_standard =       0   AND STO.utc_offset  =       7                                       THEN    'America/Denver'
                                    WHEN    VVV_LOC.utc_offset_standard =       0   AND STO.utc_offset  =       8                                       THEN    'America/Los_Angeles'
                                    WHEN    VVV_LOC.utc_offset_standard =       0                                                                       THEN    'America/New_York'
                                    WHEN    VVV_LOC.utc_offset_standard =       5                                                                       THEN    'America/New_York'
                                    WHEN    VVV_LOC.utc_offset_standard =       6                                                                       THEN    'America/Chicago'
                                    WHEN    VVV_LOC.utc_offset_standard =       7   AND VVV_LOC.uses_dst_flag   =       'Y'                             THEN    'America/Phoenix'
                                    WHEN    VVV_LOC.utc_offset_standard =       7                                                                       THEN    'America/Denver'
                                    WHEN    VVV_LOC.utc_offset_standard =       8                                                                       THEN    'America/Los_Angeles'
                                    ELSE                                                                                                                        'America/New_York'
                                END                                                     AS ansi_tz
        FROM                ACCOUNTING.Valid_Values_Views.locations         AS VVV_LOC
        LEFT JOIN           TEAMS.Team_Marketing.state_time_offsets         AS STO
            ON                  VVV_LOC.address_state_code  =       STO.state
    )
;

当前UDF代码及问题 #

现在UDF仅调用上述视图，但仍然报错：

CREATE OR REPLACE FUNCTION localize_ts_loc(ts_utc TIMESTAMP, loc_num_input NUMBER)
    RETURNS TIMESTAMP_NTZ
    LANGUAGE SQL
    AS
        $$
            SELECT              Any_Value(Convert_Timezone( 'UTC'   ,   TZ.ansi_tz      ,   ts_utc::TIMESTAMP_NTZ))
            FROM                loc_num_ansi_tz             AS TZ
            WHERE               TZ.loc_num = loc_num_input
        $$
;

可行但不够优雅的UDTF方案 #

将逻辑改为UDTF可以正常运行，但查询语法繁琐，其他分析师使用门槛较高：

期望的UDF调用方式（无法运行） #

-- Calling my UDF doesn't work:
SELECT              t1.contact_start_ts_utc
                ,   t1.loc_num
                ,   localize_ts_loc(t1.contact_start_ts_utc, t1.loc_num)    AS localized_timestamp
FROM                table1 AS t1
SAMPLE ROW (100 ROWS)
;

可用的UDTF调用方式（语法繁琐） #

-- This UDTF works but it's clunky:
SELECT              t1.omni_journey_contact_key
                ,   t1.contact_start_ts_utc
                ,   t1.loc_num
                ,   TS.*
FROM                table1      AS t1
SAMPLE ROW (100 ROWS)
INNER JOIN          TABLE(localize_ts_loc_tbl(t1.contact_start_ts_utc, t1.loc_num)) AS TS
;

我需要一种无需使用UDTF，能直接通过输入门店ID实现功能的UDF写法。

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