BigQuery中按行从OPTIONS列筛选对应最大值字段的问题

我在BigQuery中有如下结构的表：

ID  OPTIONS   A   B   C   D   E
1  ['A','C'] 0.1 0.9 0.3 0.7  0
2  ['B','C'] 0.2 0.3 0.4 0.6  1
3  ['A','D'] 0.3 0.4 0.5 0.1  0.6

需要按行从OPTIONS数组指定的选项中，找出对应字段值最大的选项，期望结果如下：

ID  OPTIONS   A   B   C   D   E    MAX_OPTION
1  ['A','C'] 0.1 0.9 0.3 0.7  0      C
2  ['B','C'] 0.2 0.3 0.4 0.6  1       C
3  ['A','D'] 0.3 0.4 0.5 0.1  0.6     A

我尝试了以下SQL语句，但MAX_OPTION列返回NULL：

SELECT 
  ID,
  GREATEST(
    IF('A' IN UNNEST(REGEXP_EXTRACT_ALL(bonus_options, r'"([^"]+)"')), A, -99999), 
    IF('B' IN UNNEST(REGEXP_EXTRACT_ALL(bonus_options, r'"([^"]+)"')), B, -99999), 
    IF('C' IN UNNEST(REGEXP_EXTRACT_ALL(bonus_options, r'"([^"]+)"')), C, -99999), 
    IF('D' IN UNNEST(REGEXP_EXTRACT_ALL(bonus_options, r'"([^"]+)"')), D, -99999), 
    IF('E' IN UNNEST(REGEXP_EXTRACT_ALL(bonus_options, r'"([^"]+)"')), E, -99999),
  ) AS MAX_OPTION
FROM 
  `TABLE`

原SQL的问题 #

1. 字段名不匹配：原SQL中引用了bonus_options，但表结构里的字段是OPTIONS，导致正则提取无结果，所有IF条件不成立。
2. 数组处理错误：如果OPTIONS是BigQuery原生ARRAY<string>类型，无需正则解析，直接UNNEST(OPTIONS)即可；若为字符串形式的数组，原正则表达式也无法正确解析格式。
3. 逻辑偏离需求：GREATEST返回的是数值而非选项名称，即使正常运行也无法得到期望的字母结果。

正确实现方式 #

场景1：OPTIONS为原生数组类型

WITH ranked_options AS (
  SELECT 
    ID,
    OPTIONS,
    A, B, C, D, E,
    option_name,
    option_value,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY option_value DESC) AS rn
  FROM 
    `TABLE`,
    UNNEST(OPTIONS) AS option_name,
    UNNEST([
      STRUCT('A' AS name, A AS value),
      STRUCT('B' AS name, B AS value),
      STRUCT('C' AS name, C AS value),
      STRUCT('D' AS name, D AS value),
      STRUCT('E' AS name, E AS value)
    ]) AS option_kv
  WHERE option_kv.name = option_name
)
SELECT 
  ID, OPTIONS, A, B, C, D, E, option_name AS MAX_OPTION
FROM ranked_options
WHERE rn = 1

场景2：OPTIONS为字符串类型（如"['A','C']"）

先解析字符串为原生数组再处理：

WITH parsed_options AS (
  SELECT 
    ID,
    OPTIONS,
    A, B, C, D, E,
    ARRAY(
      SELECT TRIM(option, "'[] ") 
      FROM UNNEST(SPLIT(OPTIONS, ',')) AS option
    ) AS parsed_option_array
  FROM `TABLE`
),
ranked_options AS (
  SELECT 
    ID,
    OPTIONS,
    A, B, C, D, E,
    option_name,
    option_value,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY option_value DESC) AS rn
  FROM 
    parsed_options,
    UNNEST(parsed_option_array) AS option_name,
    UNNEST([
      STRUCT('A' AS name, A AS value),
      STRUCT('B' AS name, B AS value),
      STRUCT('C' AS name, C AS value),
      STRUCT('D' AS name, D AS value),
      STRUCT('E' AS name, E AS value)
    ]) AS option_kv
  WHERE option_kv.name = option_name
)
SELECT 
  ID, OPTIONS, A, B, C, D, E, option_name AS MAX_OPTION
FROM ranked_options
WHERE rn = 1

补充说明 #

• 两种方法均通过将字段映射为键值对，筛选出OPTIONS包含的选项后按值降序排序，取每个ID的首个选项作为最大值对应项。
• 若存在多个选项值相同的情况，可将ROW_NUMBER()替换为RANK()，并筛选rn <= 1以保留所有最大值选项。

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