Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Return clickhouse array as column

Tags:

sql

clickhouse

Is it possible with Clickhouse to have result containing a pair of array transformed as columns?

Form this result:

┌─f1──┬f2───────┬f3─────────────┐
│ 'a' │ [1,2,3] │ ['x','y','z'] │
│ 'b' │ [4,5,6] │ ['x','y','z'] │
└─────┴─────────┴───────────────┘

to :

┌─f1──┬x──┬y──┬z──┐
│ 'a' │ 1 │ 2 │ 3 │
│ 'b' │ 4 │ 5 │ 6 │
└─────┴───┴───┴───┘

The idea is to not have to repeat the header values for each line.

In my case, the "header" array f3 unique by queries and join to the f1,f2.

like image 677
crak Avatar asked Feb 21 '19 16:02

crak


1 Answers

You can do it with help of indexOf function.

SELECT *
FROM test_sof 

┌─f1─┬─f2──────┬─f3────────────┐
│ a  │ [1,2,3] │ ['x','y','z'] │
└────┴─────────┴───────────────┘
┌─f1─┬─f2────────┬─f3────────────────┐
│ c  │ [7,8,9,0] │ ['x','y','z','n'] │
└────┴───────────┴───────────────────┘
┌─f1─┬─f2─────────┬─f3────────────────┐
│ d  │ [7,8,9,11] │ ['x','y','z','n'] │
└────┴────────────┴───────────────────┘
┌─f1─┬─f2──────┬─f3────────────┐
│ b  │ [4,5,6] │ ['x','y','z'] │
└────┴─────────┴───────────────┘

4 rows in set. Elapsed: 0.001 sec.

Then:

SELECT 
    f1, 
    f2[indexOf(f3, 'x')] AS x, 
    f2[indexOf(f3, 'y')] AS y, 
    f2[indexOf(f3, 'z')] AS z, 
    f2[indexOf(f3, 'n')] AS n
FROM test_sof 
ORDER BY 
    f1 ASC, 
    x ASC

┌─f1─┬─x─┬─y─┬─z─┬──n─┐
│ a  │ 1 │ 2 │ 3 │  0 │
│ b  │ 4 │ 5 │ 6 │  0 │
│ c  │ 7 │ 8 │ 9 │  0 │
│ d  │ 7 │ 8 │ 9 │ 11 │
└────┴───┴───┴───┴────┘

4 rows in set. Elapsed: 0.002 sec. 

Keep in mind situation when index from header array will not be present in data array or vise-versa.

UPD: the way how to get data without knowing "headers".

You will get three columns, third one with headers.

SELECT 
    f1, 
    f2[num] AS f2_el, 
    f3[num] AS f3_el
FROM test_sof 
ARRAY JOIN arrayEnumerate(f2) AS num
ORDER BY f1 ASC

┌─f1─┬─f2_el─┬─f3_el─┐
│ a  │     1 │ x     │
│ a  │     2 │ y     │
│ a  │     3 │ z     │
│ b  │     4 │ x     │
│ b  │     5 │ y     │
│ b  │     6 │ z     │
│ c  │     7 │ x     │
│ c  │     8 │ y     │
│ c  │     9 │ z     │
│ c  │     0 │ n     │
│ d  │     7 │ x     │
│ d  │     8 │ y     │
│ d  │     9 │ z     │
│ d  │    11 │ n     │
└────┴───────┴───────┘

14 rows in set. Elapsed: 0.006 sec.
like image 122
Victor Perov Avatar answered Oct 19 '22 01:10

Victor Perov