Query
select *
from (
select [1] a
union all
select [1,2] a
union all
select [2] a
) q;
Result (3 rows)
[1]
[1,2]
[2]
Expected result (1 row)
[1,1,2,2]
It is possible ?
Add a comment
|
2 Answers
Alternate way using Array-combinator:
SELECT groupArrayArray(*)
FROM
(
SELECT [1] AS a
UNION ALL
SELECT [1, 2] AS a
UNION ALL
SELECT [2] AS a
) AS q
/* result
┌─groupArrayArray(a)─┐
│ [1,1,2,2] │
└────────────────────┘
*/
3 Comments
Matthew Formosa
Interesting. Are you aware whether this yields better performance compared to my approach?
vladimir
@MatthewFormosa not sure, to know it needs to make tests on a big amount of data or look at source code.
Iñaki Soria
I've just tested it, with big (about 35M arrays) dataset, groupArrayArray is 30% faster
Yes, sure. You just need a couple of array functions.
select arrayFlatten(groupArray(*))
from (
select [1] a
union all
select [1,2] a
union all
select [2] a
) q;
groupyArray gives you [[1],[1,2],[2]], essentially grouping all the results in one array.
arrayFlatten flattens the above array, resulting in [1,1,2,2].
