GROUP_CONCAT alternative in ClickHouse

[Martin Sebastijan Šestak]

Hi,

I would like to use GROUP_CONCAT functionality in ClickHouse. What are my options.

My query in MySQL looks like:
        SELECT ps, ti, species_name, COUNT(species_name) AS gene_hits_per_species,
        GROUP_CONCAT(prot_id ORDER BY prot_id) AS gene_list
        FROM table
        GROUP BY species_name

[man...@gmail.com]

Hi.

There is groupArray aggregate function, that will collect all values in array.
Also, there is arrayStringConcat function: arrayStringConcat(arr[, delim]) which joins array values to single string, optionally using delimiter.
And also, there is toString function and arrayMap function to transform arrays.

Lets review all steps:

1. Create array of prot_id values: groupArray(prot_id).
2. If prot_id is not of String type, transform array to Array(String): arrayMap(x -> toString(x), groupArray(prot_id))
3. Join array to single string: arrayStringConcat(arrayMap(x -> toString(x), groupArray(prot_id)), ',')

Probably, you need just first step, to get result in arrays.

SELECT ps, ti, species_name, COUNT(species_name) AS gene_hits_per_species,

    groupArray(prot_id)

FROM table
GROUP BY species_name

It is not possible to specify order of values for groupArray function.