结构和数组-错误:标量子查询生成了多个元素

Question

我正在尝试使用struct和array将两个表放在一起。我的想法是，对于表A中的每一行，将Levenstein距离应用于表B。

表A:

col1
whisky
delta
Tango

表B

col1
Whiskey
delta force
Tango is great

期望输出：

col1        col2             col3
whisky    Whiskey            <lv_distance_score>
delta force        <lv_distance_score>
Tango is great     <lv_distance_score>
delta     Whiskey            <lv_distance_score>
delta force        <lv_distance_score>
Tango is great     <lv_distance_score>
Tango     Whiskey            <lv_distance_score>
delta force        <lv_distance_score>
Tango is great     <lv_distance_score>

为此，首先我试图获得col1和col2的不期望的输出，但我一直得到一个error，它表示Scalar subquery produced more than one element。

我写的查询是：

WITH a AS (
SELECT col1, [STRUCT((SELECT col1 FROM table_B))] AS col2 FROM table_A
)
SELECT col1,c2 FROM a,UNNEST(a.col2) AS c2;

我在这里做错了什么？我如何才能实现我想要的目标？

Answer 1

我做错了什么？

以下是对您的原始查询的简单修复

WITH a AS (
SELECT col1, 
[STRUCT(ARRAY(SELECT col1 FROM table_B) as col2)] AS col2 
FROM table_A
)
SELECT col1, c2.col2 
FROM a, UNNEST(a.col2) AS c2;   

虽然上面希望向您展示您的查询出了什么问题，但我不确定这是正确的方向。

如何实现我想要的目标？

你只需要像下面的例子那样进行简单的交叉连接

SELECT a.col1, ARRAY_AGG(b.col1 ORDER BY lv_distance_score(a.col1, b.col1) LIMIT 1)
FROM table_A a 
CROSS JOIN table_B b
GROUP BY a.col1   

注意：你可以在SO 上找到很多Levenstein Distance UDF的例子

Answer 2

我有点迷路了。为什么不直接使用cross join？

select a.col1, b.col1
from a cross join
b

如果你想在a中每行一行，用一个b的数组，那么：

select a.col1, array_agg(b)
from a cross join
b
group by a.col1;