英文:
Choose the max value out of options per row in SQL
问题
以下是翻译好的部分：
我在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
这是我尝试过的查询：
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。我可以做什么？
英文:
I have a table in BigQuery that looks like that:
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
I want to get the maximum option per row out of 'OPTIONS', example:
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
This is what I tried:
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`
But I get NULL in MAX_OPTION.
What can I do?
答案1
得分: 1
以下是您请求的代码部分的翻译：
SELECT
* EXCEPT (MAX_OPTION),
CASE
WHEN A=MAX_OPTION THEN 'A'
WHEN B=MAX_OPTION THEN 'B'
WHEN C=MAX_OPTION THEN 'C'
WHEN D=MAX_OPTION THEN 'D'
WHEN E=MAX_OPTION THEN 'E'
END as MAX_OPTION
FROM (
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`)
英文:
SELECT
* EXCEPT (MAX_OPTION),
CASE
WHEN A=MAX_OPTION THEN 'A'
WHEN B=MAX_OPTION THEN 'B'
WHEN C=MAX_OPTION THEN 'C'
WHEN D=MAX_OPTION THEN 'D'
WHEN E=MAX_OPTION THEN 'E'
END as MAX_OPTION
FROM (
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`)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论