Snowflake中使用Dynamic SQL生成ILIKE语句实现名单批量排除的方法

你需要的效果完全可以实现，以下是两种可落地的方案，均可以避免笛卡尔关联的性能问题，无需手动拼接规则：

方案1：ILIKE ANY原生语法实现（无需动态SQL，性能最优） #

Snowflake原生支持ILIKE ANY批量匹配数组内的通配符规则，直接将排除关键词聚合为通配符数组即可实现过滤，无笛卡尔积开销：

SELECT * FROM test
WHERE last_name NOT ILIKE ANY (
    -- 动态将排除关键词拼接为 %关键词% 格式的通配符数组
    SELECT ARRAY_AGG('%' || excl_last || '%') 
    FROM test_excludes
);

该方案无需额外创建存储对象，规则会随test_excludes表的内容自动更新，适合规则迭代频繁的场景。如果是匹配邮箱域名，只需调整通配符拼接规则为'%@' || excl_domain即可适配。

方案2：动态SQL存储过程实现 #

如果需要获取拼接完成的静态ILIKE语句、或者要将过滤逻辑固化为可调用对象，可以使用Snowflake的SQL存储过程动态构造查询：

CREATE OR REPLACE PROCEDURE get_filtered_test_data()
RETURNS TABLE (id NUMBER, last_name VARCHAR, first_name VARCHAR)
LANGUAGE SQL
AS
DECLARE
    exclude_conditions STRING;
    final_query STRING;
BEGIN
    -- 动态拼接所有排除条件为 ILIKE 语句
    SELECT LISTAGG('last_name ILIKE ''%' || excl_last || '%''', ' OR ') 
    INTO exclude_conditions
    FROM test_excludes;

    -- 构造完整查询SQL
    final_query := 'SELECT * FROM test WHERE NOT (' || exclude_conditions || ')';
    
    -- 执行动态SQL返回结果
    RETURN TABLE(EXECUTE IMMEDIATE :final_query);
END;

调用存储过程即可获取过滤后的数据：

CALL get_filtered_test_data();

执行效果验证 #

两种方案执行后均会排除last_name包含ong、oe的记录，返回唯一符合要求的结果：

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