Clickhouse 實現 MySQL 的 group_concat() 的作法

MySQL 有 group-concat function，能將 group by 後的資料合併，方便查詢、處理。

例如

mysql> select id, group_concat(arr) from (
    -> select 1 as id, 'orange' as arr
    -> union all
    -> select 1, 'banana'
    -> union all
    -> select 2, 'apple'
    -> union all
    -> select 1, 'banana'
    -> union all
    -> select 3, 'peach'
    -> union all
    -> select 1, 'grava'
    -> union all
    -> select 4, 'pineapple'
    -> union all
    -> select 4, 'pear'
    -> ) as f
    -> group by id;
+----+----------------------------+
| id | group_concat(arr)          |
+----+----------------------------+
|  1 | banana,grava,orange,banana |
|  2 | apple                      |
|  3 | peach                      |
|  4 | pineapple,pear             |
+----+----------------------------+
4 rows in set (0.00 sec)

然而這畢竟是 MySQL 特有的 function， clickhouse 並無此 function，要做到同樣的事就得用其他 function 來處理

clickhouse 本身有 array 這種類型，且有眾多的 function 可以處理

其中，groupArray() 可以將 group 內的資料合併成一個 array；arrayStringConcat()則是類似 Java、javascript 的 array.join()

運用這兩者就可以達成我們要的效果，如下

my.clickhoue.db :) select id, arrayStringConcat(groupArray(arr), ',') from (
:-] select 1 as id, 'orange' as arr
:-] union all
:-] select 1, 'banana'
:-] union all
:-] select 2, 'apple'
:-] union all
:-] select 1, 'banana'
:-] union all
:-] select 3, 'peach'
:-] union all
:-] select 1, 'grava'
:-] union all
:-] select 4, 'pineapple'
:-] union all
:-] select 4, 'pear'
:-] ) as f
:-] group by id

SELECT
    id,
    arrayStringConcat(groupArray(arr), ',')
FROM
(
    SELECT
        1 AS id,
        'orange' AS arr
    UNION ALL
    SELECT
        1,
        'banana'
    UNION ALL
    SELECT
        2,
        'apple'
    UNION ALL
    SELECT
        1,
        'banana'
    UNION ALL
    SELECT
        3,
        'peach'
    UNION ALL
    SELECT
        1,
        'grava'
    UNION ALL
    SELECT
        4,
        'pineapple'
    UNION ALL
    SELECT
        4,
        'pear'
) AS f
GROUP BY id

┌─id─┬─arrayStringConcat(groupArray(arr), ',')─┐
│  1 │ orange,banana,grava,banana              │
│  2 │ apple                                   │
│  3 │ peach                                   │
│  4 │ pineapple,pear                          │
└────┴─────────────────────────────────────────┘

4 rows in set. Elapsed: 0.012 sec.