MySQL、HiveQL、SQL:组合集

Question

如何组合集合？例如，我有下面的表，db.table1

cola    colb  colc
abc     1     [1, 2]
abc     1     [3]

我想要：

abc  1    [1, 2, 3]

我试过了：

SELECT cola, colb, COLLECT_SET(colc)
FROM db.tbl1;

这给出了错误FAILED: UDFArgumentTypeException Only primitive type arguments are accepted but array<string> was passed as parameter 1.，因为我似乎不能收集集合的集合。

Answer 1

我想通了。

SELECT cola, colb, COLLECT_SET(b.col) AS colc
FROM db.table1
LATERAL VIEW EXPLODE(colc) b
GROUP BY cola, colb;

将返回：

cola    colb  colc
abc     1     [1, 2, 3]