如何在BigQuery中动态获取多列绝对值前三的列及其值

一、处理扁平列结构（第一种表结构） #

对于包含ID和多个特征列的扁平表，通过动态UNPIVOT + 分组排序聚合实现需求，无需手动指定列名：

DECLARE cols_str STRING;

-- 动态获取所有特征列（排除ID列）
SET cols_str = (
  SELECT STRING_AGG(DISTINCT column_name, ', ')
  FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_flat_table'
    AND column_name != 'ID'
);

-- 执行动态SQL，提取绝对值前三的特征
EXECUTE IMMEDIATE format("""
WITH unpivoted AS (
  SELECT
    ID,
    column_name,
    CAST(value AS INT64) AS value -- 根据实际数据类型调整，比如FLOAT64
  FROM `your-project.your-dataset.your_flat_table`
  UNPIVOT(value FOR column_name IN (%s))
),
ranked AS (
  SELECT
    ID,
    column_name,
    value,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ABS(value) DESC) AS rn
  FROM unpivoted
)
SELECT
  ID,
  ARRAY_AGG(STRUCT(column_name AS column, value) ORDER BY rn) AS output
FROM ranked
WHERE rn <= 3
GROUP BY ID
""", cols_str);

说明： #

• 借助INFORMATION_SCHEMA.COLUMNS自动获取所有非ID列，避免硬编码列名
• 用UNPIVOT将宽表转为长表，每行对应一个特征的列名和值
• 按ID分组，对特征值的绝对值降序排名后取前3条
• 通过ARRAY_AGG将结果聚合为STRUCT数组

二、处理STRUCT嵌套结构（第二种表结构） #

对于包含ID和featureAttributions STRUCT的表，利用JSON函数动态拆分STRUCT字段，无需手动枚举STRUCT内的键：

SELECT
  ID,
  ARRAY_AGG(STRUCT(key AS column, CAST(value AS INT64) AS value) ORDER BY ABS(CAST(value AS INT64)) DESC LIMIT 3) AS output
FROM `your-project.your-dataset.your_struct_table`,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(featureAttributions), r'"(\w+)":(-?\d+\.?\d*)')) AS kv_pair,
UNNEST([STRUCT(
  SPLIT(kv_pair, ':')[OFFSET(0)] AS key,
  SPLIT(kv_pair, ':')[OFFSET(1)] AS value
)])
GROUP BY ID;

说明： #

• TO_JSON_STRING(featureAttributions)将STRUCT转为JSON字符串
• REGEXP_EXTRACT_ALL提取所有键值对（匹配"键":值格式）
• 通过SPLIT拆分键和值并转为STRUCT
• 按ID分组，对值的绝对值降序排序后取前3条，聚合为目标数组

如果特征值是浮点型，只需将CAST(value AS INT64)改为CAST(value AS FLOAT64)即可适配。

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