我在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
这就是我try 过的:
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`
但我在MAX_OPTION中得到NULL. 我能做什么?