智能路径
- 输入,在数据范围内指定结束事件与窗口大小
- 返回,按用户访问时间由小到大排序后的路径字符串
select
d_i,
arrayStringConcat(
arrayMap(
b - > tupleElement(b, 1),
arraySort(
y - > tupleElement(y, 2),
arrayFilter(
(x, y, z) - > toDateTimeOrZero(z) - toDateTimeOrZero(y) < 1000,
arrayMap(
(x, y) - > (x, y),
groupArray(e_t),
groupArray(time)
),
groupArray(time),
arrayWithConstant(
length(groupArray(time)),
maxIf(time, e_t = 'launch')
)
)
)
),
'->'
) path
from
bw.scene_tracker
where
d_i <> ''
group by
d_i
例子
上述例子窗口大小为1000s,结束事件 “launch”; 亿级数据妙出
- 简版
select path,count(1)cn from(
select uid, maxIf( ts, url = 'https://ark.analysys.cn/browseGoods' ) as maxTime,
arrayFilter(x->maxTime-x.1<60*30*1000 and maxTime>=x.1 , groupArray( (ts,url) )) as window_data,
arraySort(x->x.1,window_data) as sort_data,
arrayStringConcat(sort_data.2,'->') as path
from bw.session group by uid ) where path<>'' group by path order by cn desc limit 11
- 对路径中相邻页面重复的数据进行去重
select path,count(1)cn from(
select uid, maxIf( ts, url = 'https://ark.analysys.cn/browseGoods' ) as maxTime,
arrayFilter(x->maxTime-x.1<60*30*1000 and maxTime>=x.1 , groupArray( (ts,url) )) as window_data,
arraySort(x->x.1,window_data) as sort_data,
arrayFilter((x,y)->x<>sort_data[y-1].2 ,sort_data.2,arrayEnumerate( sort_data )) as sort_data_url, --相邻去重
arrayStringConcat(sort_data_url,'->') as path
from bw.session group by uid ) where path<>'' group by path order by cn desc limit 11
- 线上环境测试版
select data, count(1) cn from (
with maxIf( c_t , cat='page_view'and act='页面_浏览') as max_time, -- 目标事件时间
arraySort(
e -> e.1,
arrayFilter(x->x.1<=toUInt64OrZero(max_time),groupArray((toUInt64OrZero(c_t), (cat,act) )))
) as sorted_array,
-- 按时间排序后的数据
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='页面_浏览')),
array