构建clickhouse复杂数据模型

智能路径

• 输入，在数据范围内指定结束事件与窗口大小
• 返回，按用户访问时间由小到大排序后的路径字符串

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