如何在Snowflake中用宏变量动态引用带年月的新增列？

一、自动引用当月新增列 #

如果只是需要动态引用当月的列进行查询或处理，可以通过信息架构表结合动态SQL实现，替代手动替换年月值的操作：

1. 生成当月的年月标识
   用Snowflake的日期函数生成匹配列名格式的年月字符串（如May_24）：

SET current_month_id = TO_CHAR(CURRENT_DATE(), 'Mon_YY');

2. 动态获取当月列名列表
   查询INFORMATION_SCHEMA.COLUMNS筛选包含当月年月标识的列，拼接成可用的列列表：

SET target_columns = (
    SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '你的Schema名'
      AND TABLE_NAME = '你的表名'
      AND COLUMN_NAME LIKE '%' || $current_month_id || '%'
);

3. 执行动态SQL引用目标列
   用EXECUTE IMMEDIATE执行拼接好的SQL：

EXECUTE IMMEDIATE 'SELECT ' || $target_columns || ' FROM 你的Schema名.你的表名';

二、生成「年月为行、Hello/Bye为列」的数据集 #

要把原宽表转成目标结构，需要先**拆分行（UNPIVOT）提取年月和指标类型，再转置列（PIVOT）**重组数据，全程可自动化实现：

自动化实现版本 #

利用动态SQL自动适配所有列，无需手动维护列名：

-- 先获取表中所有列名
SET all_columns = (
    SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '你的Schema名'
      AND TABLE_NAME = '你的表名'
);

-- 执行动态拆分行+转置列逻辑
EXECUTE IMMEDIATE '
WITH unpivoted_data AS (
    SELECT 
        -- 从列名中提取年月标识（如May_24）
        REGEXP_SUBSTR(COLUMN_NAME, ''_([A-Za-z]{3}_[0-9]{2})_'', 1, 1, ''e'') AS month_id,
        -- 从列名中提取指标类型（hello/bye）
        REGEXP_SUBSTR(COLUMN_NAME, ''_(hello|bye)$'', 1, 1, ''e'') AS metric_type,
        metric_value
    FROM 你的Schema名.你的表名
    UNPIVOT (
        metric_value FOR COLUMN_NAME IN (' || $all_columns || ')
    )
)
SELECT 
    month_id,
    -- 转置为hello和bye列
    MAX(CASE WHEN metric_type = ''hello'' THEN metric_value END) AS hello,
    MAX(CASE WHEN metric_type = ''bye'' THEN metric_value END) AS bye
FROM unpivoted_data
GROUP BY month_id
-- 按实际日期排序，避免字符串排序误差
ORDER BY TO_DATE(month_id, ''Mon_YY'');
';

关键说明 #

• 正则表达式可根据你的列名格式调整：如果列名的年月位置或分隔符有变化，修改REGEXP_SUBSTR的匹配模式即可。
• 聚合函数MAX的使用：因为每个month_id+metric_type对应唯一的原始列值，用MAX/MIN都能正确提取值，不会影响结果。

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