基于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',
`_id` String DEFAULT '-9999' COMMENT '本品ID',
`_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 '价格偏好',
`level2_id` String DEFAULT '-9999' COMMENT '级别偏好',
`brand_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 '时间',
`设备` String DEFAULT '-9999' COMMENT '设备ID',
`id` Array(Int64) DEFAULT [-9999] COMMENT '本品ID',
`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 '价格偏好',
`level_id` String DEFAULT '-9999' COMMENT '级别偏好',
`品牌` 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的数组函数：
原本需要最少俩小时才能出试算结果，经过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 设备ID,is_yest_dau,is_yest_recom_dau ,99999 as cp_id,1 as type from 表
where hasAny(province_id, [1502]) > 0 and hasAny(_model_id,[5394]) > 0 and hasAny(package_type,[1]) > 0
union all
select 设备ID,is_yest_dau,is_yest_recom_dau ,99999 as cp_id,2 as type from 表
where hasAny(province_id,[1502]) > 0 and hasAny(_model_cp_id,[4597, 5301]) > 0 and hasAny(package_type,[2]) > 0
union all
select 设备ID,is_yest_dau,is_yest_recom_dau ,99999 as cp_id,3 as type from 表
where hasAny(province_id, [1502]) > 0 and hasAny(package_type,[3]) > 0
union all
select 设备ID , is_yest_dau , is_yest_recom_dau , arrayJoin(_model_cp_id) as cp_id , 99999 as type from
(
select 设备ID,is_yest_dau,is_yest_recom_dau ,_model_cp_id from 表
where
(hasAny(province_id,[1502]) > 0 and hasAny(_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 表
where
(hasAny(province_id, [1502]) > 0 and hasAny(_model_id,[5394]) > 0 and hasAny(package_type,[1]) > 0)
or
(hasAny(province_id,[1502]) > 0 and hasAny(_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处理后的优化。性能提升非常多....
