英文:
Choose the max value out of options per row in SQL
问题
以下是翻译好的部分：
我在BigQuery中有一个表，看起来像这样：ID OPTIONS A B C D E1 ['A','C'] 0.1 0.9 0.3 0.7 02 ['B','C'] 0.2 0.3 0.4 0.6 13 ['A','D'] 0.3 0.4 0.5 0.1 0.6我想从'OPTIONS'列中的每一行中获取最大的选项，示例：ID OPTIONS A B C D E MAX_OPTION1 ['A','C'] 0.1 0.9 0.3 0.7 0. C2 ['B','C'] 0.2 0.3 0.4 0.6 1 C3 ['A','D'] 0.3 0.4 0.5 0.1 0.6 A这是我尝试过的查询：SELECTID,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_OPTIONFROM`TABLE`但是我在MAX_OPTION中得到NULL。我可以做什么？
英文:
I have a table in BigQuery that looks like that:
ID OPTIONS A B C D E1 ['A','C'] 0.1 0.9 0.3 0.7 02 ['B','C'] 0.2 0.3 0.4 0.6 13 ['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_OPTION1 ['A','C'] 0.1 0.9 0.3 0.7 0. C2 ['B','C'] 0.2 0.3 0.4 0.6 1 C3 ['A','D'] 0.3 0.4 0.5 0.1 0.6 A
This is what I tried:
SELECTID,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_OPTIONFROM`TABLE`
But I get NULL in MAX_OPTION.
What can I do?
答案1
得分: 1
以下是您请求的代码部分的翻译：
SELECT* EXCEPT (MAX_OPTION),CASEWHEN 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_OPTIONFROM (SELECTID,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_OPTIONFROM`TABLE`)
英文:
SELECT* EXCEPT (MAX_OPTION),CASEWHEN 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_OPTIONFROM (SELECTID,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_OPTIONFROM`TABLE`)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论