Snowflake中通过SQL实现百万级动态WHERE条件循环统计的方法

可以用纯SQL实现，不需要依赖JavaScript。根据数据量规模，推荐两种高效方案：

方案1：交叉连接+COUNT_IF（适合A1数据量较小的场景） #

利用集合运算替代循环，将基表与条件表交叉关联，通过COUNT_IF判断每条记录是否符合对应条件并统计数量。

-- 创建结果表（若不存在）
CREATE OR REPLACE TABLE result_table (
    GROUPING INT,
    APP_COUNT INT
);

-- 插入统计结果
INSERT INTO result_table
SELECT 
    nt.GROUPING,
    COUNT_IF( (nt.TEXT_STRING) ) AS APP_COUNT
FROM 
    A1
CROSS JOIN 
    new_table nt
GROUP BY 
    nt.GROUPING;

注意：此方法会生成A1行数 × new_table行数的中间数据集，若A1数据量达千万级以上，可能引发性能瓶颈或资源耗尽，此时建议使用方案2。

方案2：动态SQL批量生成UNION ALL（适合大数据量场景） #

通过LISTAGG将每条条件拼接成独立的统计SQL，再分批执行批量查询，规避LISTAGG的长度限制。

步骤1：分批生成并执行动态SQL #

用递归CTE将条件表分成若干批次（示例按每1000条为一批），逐批生成查询语句并执行：

-- 递归CTE拆分批次
WITH RECURSIVE batches AS (
    SELECT 
        GROUPING,
        TEXT_STRING,
        CEIL(ROW_NUMBER() OVER (ORDER BY GROUPING) / 1000) AS batch_id
    FROM new_table
),
batch_queries AS (
    SELECT 
        batch_id,
        LISTAGG(
            'SELECT ' || GROUPING || ' AS GROUPING, COUNT(DISTINCT APP_NUMBER) AS APP_COUNT FROM A1 WHERE ' || TEXT_STRING,
            ' UNION ALL '
        ) AS batch_sql
    FROM batches
    GROUP BY batch_id
)
-- 逐批执行并插入结果
INSERT INTO result_table
SELECT * FROM TABLE(
    FLATTEN(
        INPUT => ARRAY_AGG(EXECUTE IMMEDIATE(batch_sql))
    )
);

步骤2：初始化结果表 #

首次执行前先创建结果表：

CREATE OR REPLACE TABLE result_table (
    GROUPING INT,
    APP_COUNT INT
);

说明：

• 可根据Snowflake的SQL长度限制和资源情况，调整批次大小（如500或2000）。
• 每个批次独立统计，避免生成超大中间数据集，性能更稳定。

关键注意事项 #

• 确保TEXT_STRING中的条件语法完全合法，列名前缀（如A1.）与基表别名一致，否则会触发语法错误。
• 若无需统计去重的APP_NUMBER，可将COUNT(DISTINCT APP_NUMBER)替换为COUNT(*)，进一步提升查询性能。

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