Snowflake遍历表列生成列名值数组及PySpark代码转Snowflake代码

要实现遍历表列并生成由[列名, 列值]子数组组成的数组，有两种常用方式：

方式1：直接生成单个数组（针对整行） #

如果需要为每行生成一个包含目标列键值对的数组，可结合OBJECT_CONSTRUCT和OBJECT_ENTRIES函数：

-- 示例：针对指定列生成数组
SELECT 
  OBJECT_ENTRIES(OBJECT_CONSTRUCT(col5, col5, col6, col6, col7, col7)) AS column_value_array
FROM your_table;

方式2：动态遍历列（无需硬编码列名） #

如果需要从第N列开始动态遍历所有列，可通过动态SQL实现：

-- 设置表名和起始列索引（从第5列开始）
SET target_table = 'your_table';
SET start_col_idx = 5;

-- 获取目标列名列表
SET column_list = (
  SELECT LISTAGG('"' || COLUMN_NAME || '"', ', ')
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = $target_table
  ORDER BY ORDINAL_POSITION
  OFFSET $start_col_idx - 1
);

-- 执行动态SQL生成数组
EXECUTE IMMEDIATE $$
  SELECT 
    OBJECT_ENTRIES(OBJECT_CONSTRUCT($column_list)) AS column_value_array
  FROM $target_table
$$;

原PySpark代码的逻辑是：将从第5列开始的所有列，转换为[列名, 列值]的子数组，再通过explode将数组拆分为多行。在Snowflake中可通过以下两种方式实现：

方式1：使用OBJECT_ENTRIES + FLATTEN #

-- 先定义目标列（同问题1的动态列获取逻辑）
SET target_table = 'your_table';
SET start_col_idx = 5;
SET column_list = (
  SELECT LISTAGG('"' || COLUMN_NAME || '"', ', ')
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = $target_table
  ORDER BY ORDINAL_POSITION
  OFFSET $start_col_idx - 1
);

-- 执行转换逻辑
WITH flattened_data AS (
  SELECT 
    *,
    FLATTEN(OBJECT_ENTRIES(OBJECT_CONSTRUCT($column_list))) AS entry
  FROM $target_table
)
SELECT 
  *,
  entry.value AS array_mode -- entry.value即为[列名, 列值]的数组
FROM flattened_data;

方式2：使用UNPIVOT（更直观） #

UNPIVOT直接实现列转行，再构造目标数组，完全匹配原PySpark代码的explode效果：

-- 定义目标列
SET target_table = 'your_table';
SET start_col_idx = 5;
SET column_list = (
  SELECT LISTAGG('"' || COLUMN_NAME || '"', ', ')
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = $target_table
  ORDER BY ORDINAL_POSITION
  OFFSET $start_col_idx - 1
);

-- 执行转换逻辑
WITH unpivoted_data AS (
  SELECT 
    *,
    ARRAY_CONSTRUCT(column_name, column_value) AS array_mode
  FROM $target_table
  UNPIVOT (
    column_value FOR column_name IN ($column_list)
  )
)
SELECT * FROM unpivoted_data;

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