CODEWITHSUNDEEP
clickhouse
user16535642
user16535642

Reputation: 45

using something like group_concat on clickhouse

I'm trying to get a result of

types name
fruit banana, apple, guaba, strawberry

from a table with database like

table: fruits

types name
fruit banana
fruit apple
fruit guaba
fruit strawberry

I know with MySQL I can use group_concat to get the result I want by using

SELECT group_concat(name), types FROM fruits

I have done my research and people recommend me to use groupArray in clickhouse to obtain similar result but this is not what I want. Because when I use

SELECT groupArray(name), types FROM fruits GROUP BY types

it gives me result of

types name
fruit apple, banana, banana, strawberry,strawberry,strawberry, guaba,guaba,guaba,guaba

the order of groupArray is mixed up and I can't seem to find an answer to fix the order :(

is there any way in clickhouse where we can get array of results in order? and why are there duplicated results?

I can't use groupUniqArray because sometimes my result should be

banana, apple, guaba, strawberry, strawberry (if strawberry is there twice in DB)

how do I keep the duplicated data without having it multiplied in order???

types name input_time key
fruit banana 01:01 01
fruit apple 01:02 01
fruit guaba 01:03 02
fruit strawberry 01:04 03
fruit strawberry 01:05 04

and forgetting about 'types', I want to get result of grouped names in DB saved order (input_time order) group by key. How should I change my query??

I've tried

SELECT groupArray(name), key FROM fruits GROUP BY key ORDER BY input_time

but it does not give me the result I want..

Upvotes: 2

Views: 13507

Answers (1)

Denny Crane
Denny Crane

Reputation: 13370

use order by in sub-query. The order in the array is the same as the order of rows at a previous stage of query pipeline

select types, arrayCompact(groupArray(name))  names, length(names) len from(
   select types, name from fruit order by types, input_time)
group by types


┌─types─┬─names───────────────────────────────────┬─len─┐
│ fruit │ ['banana','apple','guaba','strawberry'] │   4 │
└───────┴─────────────────────────────────────────┴─────┘

https://clickhouse.com/docs/en/sql-reference/functions/array-functions/#arraycompact Removes consecutive duplicate elements from an array. The order of result values is determined by the order in the source array.

Upvotes: 3

Related Questions