基于clickhouse实现标签圈人（人群试算）的优化

公司前期做智能营销，其中有一个基于标签全选人群的操作，让广告主能看到在当前标签条件下，能够全选多少人群，从而做到最大投放收益效果

1、产品背景

公司要实现一套基于标签的投放平台;主要想基于广告主给定的标签来圈定人群。然后在将广告投放出去，达到精准投放的效果;

2、页面产品

页面大概涨这个样子：

 3、数据量

3.1、每天日活的用户大概300万左右,假如90天90秒的活用用户量为600万 

3.2、产品数量大概在3000个

3.3、多个标签维度，比如有50个标签维度

3.4、地域维度

综上所属，假如90天90秒的活跃用户量为600万，在标签维度和产品数量等交叉后出现的结果总量不会小于百亿......

4、项目前期处理方式

前期处理的调用链非常不合理，大概的意思是：

 从上图能看出调用链非常的长，而且服务端和数据端耦合性特别高，是一种非常不合理的设计方案;

而且上述的处理方式，广告主想要获取人群的试算结果，需要等若干个小时，用户体验也特别不好;

5、基于ck进行优化处理

为什么我们这里要使用ck？

上面简单介绍过每天的日活，以及各种维度标签，当他们进行多维交叉处理后，产生的数据量非常惊人.即便是hive也是很吃力的，而且处理响应速度慢，用户体验差;

那么为什么使用ck后，就可以优化处理，用户体验变好？上面提到过，有可能多维交叉处理后，出现百亿条数据,即便是ck，这么多行数据，也是吃力的;

因此，我们使用了ck的一些数组和数组函数.将多条数据进行压缩处理;

比如：

正常在hive，当前表的schema表现形式是：

CREATE TABLE hive的表
(
    `dt` String DEFAULT '9999-01-11' COMMENT '时间',
    `dvid` String DEFAULT '-9999' COMMENT '设备ID',
    `car_model_id` String DEFAULT '-9999' COMMENT '本品ID',
    `car_model_cp_id` String DEFAULT '-9999' COMMENT '竞品ID',
    `package_type` String DEFAULT '-9999' COMMENT '人群包类型',
    `age_range` String DEFAULT '-9999' COMMENT '年龄范围',
    `city_level_id` String DEFAULT '-9999' COMMENT '城市级别',
    `pricerange_id` String DEFAULT '-9999' COMMENT '价格偏好',
    `car_level2_id` String DEFAULT '-9999' COMMENT '级别偏好',
    `carbrand_id` String DEFAULT '-9999' COMMENT '品牌偏好',
    `province_id` String DEFAULT '-9999' COMMENT '省份偏好',
    `city_id` String DEFAULT '-9999' COMMENT '城市偏好',
    `is_yest_dau` Int16 DEFAULT -9999 COMMENT '是否昨日日活(0不是 1是)',
    `is_yest_recom_dau` Int16 DEFAULT -9999 COMMENT '是否昨日首页推荐日活(0不是 1是)',
    `create_date` Date DEFAULT now() COMMENT '创建时间'
)

但是上述方式有个比较大的问题就是，多维交叉出来的数据太多了，可能百亿条.放入ck去做处理，各种维度求和，也是非常慢的;

所以最终我们将表结构处理成：

CREATE TABLE ck的表
(
    `dt` String DEFAULT '9999-01-11' COMMENT '时间',
    `dvid` String DEFAULT '-9999' COMMENT '设备ID',
    `car_model_id` Array(Int64) DEFAULT [-9999] COMMENT '本品ID',
    `car_model_cp_id` Array(Int64) DEFAULT [-9999] COMMENT '竞品ID',
    `package_type` Array(Int64) DEFAULT [-9999] COMMENT '人群包类型',
    `age_range` Array(Int64) DEFAULT [-9999] COMMENT '年龄范围',
    `city_level_id` Array(Int64) DEFAULT [-9999] COMMENT '城市级别',
    `pricerange_id` Array(Int64) DEFAULT [-9999] COMMENT '价格偏好',
    `car_level2_id` String DEFAULT '-9999' COMMENT '级别偏好',
    `carbrand_id` Array(Int64) DEFAULT [-9999] COMMENT '品牌偏好',
    `province_id` Array(Int64) DEFAULT [-9999] COMMENT '省份偏好',
    `city_id` Array(Int64) DEFAULT [-9999] COMMENT '城市偏好',
    `is_yest_dau` Int16 DEFAULT -9999 COMMENT '是否昨日日活(0不是 1是)',
    `is_yest_recom_dau` Int16 DEFAULT -9999 COMMENT '是否昨日首页推荐日活(0不是 1是)',
    `create_date` Date DEFAULT now() COMMENT '创建时间'
)

细节点就是将字段类型从字符串变成了数组.这样做的好处就是原本百亿条数据，经过数据数组处理后，最终不到一亿条，同时利用clickhouse的数组函数：

hasAny

原本需要最少俩小时才能出试算结果，经过ck优化后，只需要2~10秒钟（跟品牌关注度有关）

最终出来的sql大概长这个样子：

select cp_id , type ,count(1) as totalNum , sum(is_yest_dau) as is_yest_dau , sum(is_yest_recom_dau ) as is_yest_recom_dau 
from  
(
select dvid,is_yest_dau,is_yest_recom_dau ,99999 as cp_id,1 as type   from app_index_renqun_shisuan_day_v2_all 
where hasAny(province_id, [1502]) > 0 and hasAny(car_model_id,[5394]) > 0 and hasAny(package_type,[1]) > 0
union all
select dvid,is_yest_dau,is_yest_recom_dau ,99999 as cp_id,2 as type  from app_index_renqun_shisuan_day_v2_all 
where hasAny(province_id,[1502]) > 0 and hasAny(car_model_cp_id,[4597, 5301]) > 0 and hasAny(package_type,[2]) > 0
union all
select dvid,is_yest_dau,is_yest_recom_dau ,99999 as cp_id,3 as type  from app_index_renqun_shisuan_day_v2_all 
where hasAny(province_id, [1502]) > 0 and hasAny(package_type,[3]) > 0

union all
select dvid , is_yest_dau , is_yest_recom_dau , arrayJoin(car_model_cp_id) as cp_id , 99999 as type from
(
select dvid,is_yest_dau,is_yest_recom_dau ,car_model_cp_id  from app_index_renqun_shisuan_day_v2_all 
where 
(hasAny(province_id,[1502]) > 0 and hasAny(car_model_cp_id,[4597, 5301]) > 0 and hasAny(package_type,[2]) > 0)
)
where has([4597, 5301] , cp_id) >0
) group by cp_id,type 

union all
select  99999 , 99999 ,count(1) totalNum ,sum(is_yest_dau) as is_yest_dau ,sum(is_yest_recom_dau ) as is_yest_recom_dau from app_index_renqun_shisuan_day_v2_all 
where 
(hasAny(province_id, [1502]) > 0 and hasAny(car_model_id,[5394]) > 0 and hasAny(package_type,[1]) > 0)
or
(hasAny(province_id,[1502]) > 0 and hasAny(car_model_cp_id,[4597, 5301]) > 0 and hasAny(package_type,[2]) > 0)
or
(hasAny(province_id, [1502]) > 0 and hasAny(package_type,[3]) > 0)

获取人群试算结果
|竞品ID    |包类型   |人群试算结果
|99999    |99999   |人群包整体
|99999    |1       |本品关注包
|99999    |2       |竞品关注包整体
|某竞品ID    |99999   |某竞品包总人数
|某竞品ID    |99999   |某竞品包总人数
|99999     |3       |自选关注宝

下一篇准备写一写，画像数据，基于bitmap处理后的优化。性能提升非常多....