在BigQuery中拆分字符串列 [英] Splitting a string column in BigQuery

问题描述

比方说，我在BigQuery中有一个包含2列的表.第一列代表一个名称，第二列代表一个定界的值列表，其长度是任意的.示例:

Let's say I have a table in BigQuery containing 2 columns. The first column represents a name, and the second is a delimited list of values, of arbitrary length. Example:

Name | Scores
-----+-------
Bob  |10;20;20
Sue  |14;12;19;90
Joe  |30;15

我想转换成列，其中第一个是名称，第二个是单个得分值，如下所示:

I want to transform into columns where the first is the name, and the second is a single score value, like so:

Name,Score
Bob,10
Bob,20
Bob,20
Sue,14
Sue,12
Sue,19
Sue,90
Joe,30
Joe,15

这可以单独在BigQuery中完成吗?

Can this be done in BigQuery alone?

推荐答案

大家好消息！ BigQuery现在可以SPLIT()！

查看"

当前没有办法在BigQuery中对一个值进行split()来从一个字符串生成多行，但是您可以使用正则表达式来查找逗号并找到第一个值.然后运行类似的查询以找到第二个值，依此类推.使用上面示例中的模式(UNION到逗号)，它们都可以合并为一个查询.

There is no current way to split() a value in BigQuery to generate multiple rows from a string, but you could use a regular expression to look for the commas and find the first value. Then run a similar query to find the 2nd value, and so on. They can all be merged into only one query, using the pattern presented in the above example (UNION through commas).

这篇关于在BigQuery中拆分字符串列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！