先來一個(gè)完整的例子，該示例根據(jù)行為日志計(jì)算用戶訪問的top路徑
select data, count(1) cn from (
with maxIf( c_t , cat='page_view'and act='頁面瀏覽') as max_time, -- 目標(biāo)事件時(shí)間
arraySort(
e -> e.1,
arrayFilter(x->x.1<=toUInt64OrZero(max_time),groupArray((toUInt64OrZero(c_t), (cat,act) )))
) as sorted_array,
-- 按時(shí)間排序后的數(shù)據(jù)
-- arrayPushFront( sorted_array, sorted_array[1] ) as e_arr,
arrayFilter(
(i, e,z) -> z.1 < toUInt64OrZero(max_time)
and (e > 1800000 or (z.2.1='page_view' and z.2.2='頁面瀏覽')),
arrayEnumerate(sorted_array), arrayDifference( sorted_array.1 ),sorted_array
) as arr_indx, -- 過濾目標(biāo)事件、時(shí)間差后的數(shù)據(jù)
arrayReduce('max',arr_indx) +1 as smIndx,
arrayFilter(
(e,i) -> i>=smIndx and e.1<=toUInt64OrZero(max_time) ,
sorted_array, arrayEnumerate(sorted_array)
) as data_
select u_i,
arrayFilter((x,y,i)-> i=1 or i>1 and y<>0 ,data_.2,arrayDifference(arrayEnumerateDense(data_.2)),arrayEnumerate(data_)) as data__,
arraySlice(data__,-7,7 ) as data,
-- arrayStringConcat(data,'->') as path,
hasAll(data, [ ('page_view','頁面_瀏覽') ]) as has_way_point --路徑中必須經(jīng)過的點(diǎn)
from app.scene_tracker where c_p='PC' and length(u_i)>20
group by u_i having length(data)>1
) tab
where has_way_point=1 group by data order by cn desc limit 100
根據(jù)經(jīng)驗(yàn)，大家如處理復(fù)雜業(yè)務(wù)，這些函數(shù)會(huì)經(jīng)常用到
neighbor
獲取某一列前后相鄰的數(shù)據(jù)，第二個(gè)參數(shù)控制前后相鄰的距離
示例1：
SELECT a, neighbor( a,-1 ) from (SELECT arrayJoin( [1,2,3,6,34,3,11] ) as a,'u' as b)
arrayJoin
行變列，對(duì)數(shù)組進(jìn)行展開操作
示例2：
# 還是上面的例子
SELECT a, neighbor( a,-1 ) from (SELECT arrayJoin( [1,2,3,6,34,3,11] ) as a,'u' as b)
arraySort
對(duì)數(shù)組進(jìn)行排序，降序的話用這個(gè) arrayReverseSort
示例3：
# 還是上面的例子 略作修改，可對(duì)比示例1和示例3的結(jié)果區(qū)別
SELECT a, neighbor( a,-1 ) from (SELECT arrayJoin( arraySort([1,2,3,6,34,3,11]) ) as a,'u' as b)
arrayFilter
過濾出數(shù)組中滿足條件的數(shù)據(jù)
示例4：
# 我們只獲取數(shù)組中的偶數(shù)部分
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
返回?cái)?shù)組下標(biāo)
示例5：
SELECT arrayEnumerate( [1,2,3,6,34,3,11] )
arrayDifference
計(jì)算數(shù)組中前后兩個(gè)值的差值部分
示例6：
SELECT arrayDifference( [1,2,3,6,34,3,11] )
arrayReduce
對(duì)數(shù)組進(jìn)行聚合操作，min 、max、avg 等
示例7：
SELECT arrayReduce('avg', [1,2,3,6,34,3,11] )
arrayEnumerateDense
標(biāo)記出數(shù)組中相同的元素
示例8：
SELECT arrayEnumerateDense( [1,2,3,6,34,3,11] )
arraySlice
- 對(duì)數(shù)組進(jìn)行切割 ，后面兩個(gè)參數(shù)分別是切割的offset和切割長(zhǎng)度
- 示例9：
SELECT arraySlice( [1,2,3,6,34,3,11] , -3, 2)
# 返回：34 3
hasAny
- 判斷數(shù)組中是否包含某些值，包含其一返回1 ，否則0 ；如果判斷全部包含 用hasAll
- 示例10：
SELECT hasAny( [1,2,3,6,34,3,11] , [3,1])
arrayStringConcat
- 將數(shù)組元素按照給定分隔符進(jìn)行拼接，返回拼接后的字符串
- 示例11：
SELECT arrayStringConcat( [1,2,3,6,34,3,11] , '-')
arrayPushFront
- 向數(shù)組首位置最加value ；同理向數(shù)組末尾最加為arrayPushBack
- 示例12：
SELECT arrayPushFront( [1,2,3,6,34,3,11] , 8)
arrayPopFront
- 移除數(shù)組下標(biāo)為1的值；同理，移除數(shù)組最后一個(gè)值用arrayPopBack
- 示例13：
SELECT arrayPopFront( [1,2,3,6,34,3,11] )
arrayWithConstant
- 生成一個(gè)指定長(zhǎng)度的數(shù)組
- 示例14：
#生成長(zhǎng)度為3 的數(shù)組
SELECT arrayWithConstant( 3, 'a')
#范圍值為['a','a','a']
arrayUniq
- 計(jì)算數(shù)組中有多少個(gè)不重復(fù)的值；如進(jìn)行數(shù)組去重操作 用arrayDistinct
- 示例15：
SELECT arrayUniq( [1,2,3,6,34,3,11])
runningDifference
- 計(jì)算某一列前后數(shù)值的差值
- 示例16：
select a,runningDifference(a) from (SELECT arrayJoin( [1,2,3,6,34,3,11] ) as a,'u' as b)
arrayCompact
- 對(duì)數(shù)組內(nèi)數(shù)據(jù)實(shí)現(xiàn)相鄰去重
- 示例17：
SELECT arrayCompact([1, 2, 2, 3, 2, 3, 3])
#返回值為 [1,2,3,2,3]
arrayDistinct
- 對(duì)數(shù)組去重
- 示例18：
SELECT arrayDistinct(arrayConcat([1, 2], [3, 4], [4, 6]) ) AS res
#返回值為 [1,2,3,4,6]
開篇示例語句運(yùn)行結(jié)果如下圖
