如何在ClickHouse中结合groupArray()按不同指标排序结果

问题背景 #

现有product_info表结构及数据如下：

ProductId   ProductName
f0001       L1
f0002       L2
f0002       L2
f0002       L2
d0001       P3-01
d0001       P3-01
...

要获取唯一的ProductName并以数组形式展示，使用语句：

SELECT groupArray(DISTINCT ProductName) AS unique FROM product_info

需对结果排序，MySQL中可通过GROUP_CONCAT(DISTINCT ... ORDER BY)实现，但在ClickHouse中执行以下语句时报错：

SELECT groupArray(DISTINCT ProductName ORDER BY ProductName) AS unique FROM product_info

报错信息：

Code: 62. DB::Exception: Syntax error: failed at position 31 ('order'): order by *input expression*. Expected one of: token, Dot, UUID, Comma, ClosingRoundBracket, OR, AND, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, IS NULL, IS NOT NULL, alias, AS. (SYNTAX_ERROR) (version 22.12.3.5 (official build))

期望得到两种排序结果：

• 按名称排序：

unique
{L1,L2,P3-01}

• 按计数排序：

unique
{L1,P3-01,L2}

解决方案 #

ClickHouse的groupArray(DISTINCT)不支持直接在函数内添加ORDER BY，需通过子查询先完成去重、排序逻辑，再生成数组。

1. 按ProductName名称排序 #

先通过子查询去重并按名称排序，再用groupArray聚合为数组：

SELECT groupArray(ProductName) AS unique
FROM (
    SELECT DISTINCT ProductName
    FROM product_info
    ORDER BY ProductName
)

2. 按ProductName出现计数排序 #

先统计每个ProductName的出现次数，按计数排序后再生成数组：

SELECT groupArray(ProductName) AS unique
FROM (
    SELECT ProductName, count(*) AS cnt
    FROM product_info
    GROUP BY ProductName
    ORDER BY cnt
)

若需按计数降序排列，将ORDER BY cnt修改为ORDER BY cnt DESC即可。

内容的提问来源于stack exchange，提问作者Carlos