Snowflake中动态/静态透视数据生成自定义ARR汇总列的方案

静态实现方案 #

如果维度（Product_Type、Country、Fiscal_Period）的唯一值数量较少且相对稳定，直接用CASE语句手动枚举所有组合即可，这是最直接的可行方案：

SELECT
    Account_ID,
    -- 按维度组合生成汇总列，别名用下划线拼接确保合法
    SUM(CASE WHEN Product_Type = 'SaaS' AND Country = 'US' AND Fiscal_Period = 'FY2024Q1' THEN ARR ELSE 0 END) AS ARR_SaaS_US_FY2024Q1,
    SUM(CASE WHEN Product_Type = 'On-Prem' AND Country = 'CA' AND Fiscal_Period = 'FY2024Q2' THEN ARR ELSE 0 END) AS ARR_OnPrem_CA_FY2024Q2,
    -- 继续添加所有需要的维度组合
    SUM(ARR) AS Total_ARR -- 可选：添加账户总ARR字段
FROM your_table_name
GROUP BY Account_ID;

该方案优点是性能稳定、逻辑清晰，缺点是需要提前明确所有维度组合，无法自动适配新增的维度值。

动态实现方案 #

如果维度值频繁新增或数量较多，可通过动态SQL自动生成所有维度组合的汇总列，步骤如下：

1. 生成CASE语句片段 #

先查询所有唯一的维度组合，拼接成对应的CASE语句片段：

SELECT DISTINCT
    CONCAT(
        'SUM(CASE WHEN Product_Type = ''', Product_Type, ''' AND Country = ''', Country, ''' AND Fiscal_Period = ''', Fiscal_Period, ''' THEN ARR ELSE 0 END) AS ARR_',
        REPLACE(Product_Type, ' ', '_'), '_', REPLACE(Country, ' ', '_'), '_', REPLACE(Fiscal_Period, ' ', '_')
    ) AS case_statement
FROM your_table_name;

注：用REPLACE替换空格为下划线，避免生成非法列名；单引号需转义为两个单引号。

2. 用存储过程执行动态SQL #

通过存储过程拼接所有CASE片段，生成完整SQL并执行：

CREATE OR REPLACE PROCEDURE generate_dynamic_arr_summary()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    case_scripts VARCHAR;
    full_sql VARCHAR;
BEGIN
    -- 拼接所有CASE语句
    SELECT LISTAGG(case_statement, ',\n') INTO case_scripts
    FROM (
        SELECT DISTINCT
            CONCAT(
                'SUM(CASE WHEN Product_Type = ''', Product_Type, ''' AND Country = ''', Country, ''' AND Fiscal_Period = ''', Fiscal_Period, ''' THEN ARR ELSE 0 END) AS ARR_',
                REPLACE(Product_Type, ' ', '_'), '_', REPLACE(Country, ' ', '_'), '_', REPLACE(Fiscal_Period, ' ', '_')
            ) AS case_statement
        FROM your_table_name
    );

    -- 组装完整查询SQL
    full_sql := CONCAT('
        SELECT
            Account_ID,
            ', case_scripts, ',
            SUM(ARR) AS Total_ARR
        FROM your_table_name
        GROUP BY Account_ID;
    ');

    -- 执行动态SQL
    EXECUTE IMMEDIATE full_sql;
    RETURN '动态汇总表已生成，执行SQL：' || full_sql;
END;
$$;

-- 调用存储过程生成结果
CALL generate_dynamic_arr_summary();

注：若维度组合过多，生成的列数可能超过Snowflake默认的列数限制（16384列），需提前评估数据规模。

关于Snowflake PIVOT的补充 #

Snowflake的PIVOT功能也可实现类似效果，但PIVOT的IN子句必须静态枚举维度值，无法动态捕获所有唯一值，因此仅适合静态场景：

SELECT *
FROM (
    SELECT Account_ID, ARR, CONCAT(Product_Type, '_', Country, '_', Fiscal_Period) AS dimension_key
    FROM your_table_name
)
PIVOT (
    SUM(ARR) FOR dimension_key IN ('SaaS_US_FY2024Q1', 'On-Prem_CA_FY2024Q2') -- 需手动枚举维度值
)
ORDER BY Account_ID;

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