ARRAY JOIN子句可以帮助查询进行与数组和nested数据类型的连接
CREATE TABLE nested_test
(
s String,
nest Nested(
x UInt8,
y UInt32)
) ENGINE = Memory
INSERT INTO nested_test VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], [])
SELECT s, nest.x, nest.y FROM nested_test ARRAY JOIN nest

复杂业务经常使用的数组函数
neighbor
获取某一列前后相邻的数据，第二个参数控制前后相邻的距离
SELECT a, neighbor( a,-1 ) from (SELECT arrayJoin( [1,2,3,6,34,3,11] ) as a,'u' as b)
arrayJoin
行变列，对数组进行展开操作
SELECT a, neighbor( a,-1 ) from (SELECT arrayJoin( [1,2,3,6,34,3,11] ) as a,'u' as b)
arraySort
对数组进行排序，降序的话用这个 arrayReverseSort

还是上面的例子 略作修改，可对比示例1和示例3的结果区别

SELECT a, neighbor( a,-1 ) from (SELECT arrayJoin( arraySort([1,2,3,6,34,3,11]) ) as a,'u' as b)
arrayFilter
过滤出数组中满足条件的数据

我们只获取数组中的偶数部分

SELECT a, neighbor( a,-1 ) from
(SELECT arrayJoin( arraySort(arrayFilter(x->x%2=0, [1,2,3,6,34,3,11])) ) as a,'u' as b)
arrayEnumerate
返回数组下标
SELECT arrayEnumerate( [1,2,3,6,34,3,11] )
arrayDifference
计算数组中前后两个值的差值部分
SELECT arrayDifference( [1,2,3,6,34,3,11] )
arrayReduce
对数组进行聚合操作，min 、max、avg 等
SELECT arrayReduce('avg', [1,2,3,6,34,3,11] )

arrayEnumerateDense
标记出数组中相同的元素
SELECT arrayEnumerateDense( [1,2,3,6,34,3,11] )
arraySlice
对数组进行切割 ，后面两个参数分别是切割的offset和切割长度
SELECT arraySlice( [1,2,3,6,34,3,11] , -3, 2)
hasAny
判断数组中是否包含某些值，包含其一返回1 ，否则0 ；如果判断全部包含 用hasAll
SELECT hasAny( [1,2,3,6,34,3,11] , [3,1])
arrayStringConcat
将数组元素按照给定分隔符进行拼接，返回拼接后的字符串
SELECT arrayStringConcat( [1,2,3,6,34,3,11] , '-')
arrayPushFront
向数组首位置最加value ；同理向数组末尾最加为arrayPushBack
SELECT arrayPushFront( [1,2,3,6,34,3,11] ,
arrayPopFront
移除数组下标为1的值；同理，移除数组最后一个值用arrayPopBack
SELECT arrayPopFront( [1,2,3,6,34,3,11] )
arrayUniq
计算数组中有多少个不重复的值；如进行数组去重操作 用arrayDistinct
SELECT arrayUniq( [1,2,3,6,34,3,11])
runningDifference
计算某一列前后数值的差值
select a,runningDifference(a) from (SELECT arrayJoin( [1,2,3,6,34,3,11] ) as a,'u' as b)