>我有一个表,其中包含一列行
table_1.row_a: "ABC" "定义" "嘟嘟">
还有一个javascript UDF函数,它将一行作为输入,并为每一行输出一个字符串数组。
splitStrings(table_1.row_a)
["A", "B", "C"]
["D", "E", "F"]
["G", "H", "I"]
如何联接这些结果以获得所需的输出表:
A
B
C
D
E
F..
到目前为止,我有以下代码:
CREATE TEMP FUNCTION splitStrings(str STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
return str.split();
""";
SELECT * FROM UNNEST(["ABCD", "EFGH", "IJKL", "MNOP"]) AS words
CROSS JOIN UNNEST(splitStrings(words))
但是结果是:
-WORDS- f0_
ABCD . ABCD
EFGH . EFGH
IJKL . IJKL
MNOP . MNOP
我想要每行一个字母 一个 B C..
为什么不直接使用union all
?
select col1 from t union all
select col2 from t union all
select col3 from t;
或者,如果您确实想使用数组,则不需要 UDF:
select val
from (select 'A' as a, 'B' as b, 'C' as c union all
select 'D', 'E', 'F' union all
select 'G', 'H', 'I'
) x cross join
unnest(array[a, b, c]) val;
至于你的方法,我想你想要:
SELECT val
FROM table_1 CROSS JOIN
UNNEST(splitStrings(table_1.row_a)) val;
也就是说,从UNNEST()
中选择结果。
下面是 BigQuery Standard SQL 的示例
#standardSQL
WITH `project.dataset.table` AS (
SELECT "ABC" word UNION ALL
SELECT "DEF" UNION ALL
SELECT "GHI"
)
SELECT value
FROM `project.dataset.table`, UNNEST(SPLIT(word, '')) value
结果:
Row value
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I