项目在使用clickhouse的时候遇到了这样一个需求:需要对一个字段做聚合操作,聚合后为用逗号分隔的字符串类型,而且这个字段本身就是array类型哦,且要求聚合后的项是要去重的。举例如下:
输入:
- 记录1:GSW | ['Curry','Klay','Green']
- 记录2:GSW | ['Curry','Wiggins', 'Pool']
输出:GSW | Curry, Klay, Green, Wiggins, Pool
对于字符串拼接的聚合操作之前在PG里面经常用到,但是在clickhouse里如何操作呢?而且还包含数据合并,数组元素去重这些,搜了一圈,现成能满足这个需求的函数我是没有直接找到。但别着急,办法总是有的~~
聚合函数groupArray
这个是为数不多的能够用到数组类型字段上的聚合函数,可以将聚合的元素组成一个新的数组。我们试试看上面的例子聚合以后会是什么结果:
上面的操作把数组字段聚合成了一个二维数组,这显然不是我们要的最终结果,看到还有一个类似的groupUniqArray
的聚合函数,会不会帮我们把去重的操作做了呢?
结果并没有,似乎只会对单一元素做去重,数组类型的是不可以的。那有没有办法将生成的二维数组重新变成一维呢?
flatten函数
在clickhouse的官方文档里针对数组操作的函数章节有一个arrayFlatten
的说明,可以将多维数组拍平成一维数组,这里的输入可不止局限于二维哦。
那么,将这个函数用在我们上一步的输出之后,现在变成什么样了呢?
嗯,看样子是可以的,就是似乎该数组还没有去重,革命尚未成功,同志仍需努力!
数组去重arrayDistinct
这应该是一个比较通用的操作,果然,clickhouse是直接有函数支持这个操作的。
那继续加在我们之前的输出上吧~
马上就大功告成了,就差把数组转成字符串了。
arrayStringConcat
想你所想,clickhouse当然也提供了这样的函数,只是没有在文档中举例,输入有两个参数,第一个是要转换的数组,第二个是拍平成字符串的分隔符。 我就直接用我这个例子吧:
至此,我们的项目需求能够满足了,勇士队的“死亡五小”也出炉了~~
小结
在上面的例子中,我用了四层函数嵌套才解决了文章开始提到的这个问题,可能在大数据量情况下性能会是个问题,只是不知道在clickhouse里是否还有更优更简单的写法,欢迎读者留言讨论~~