目录
二、使用漏斗函数 windowFunnel,分析用户操作行为,计算转化率
使用漏斗函数 windowFunnel,分析用户操作行为,计算转化率
本篇主要内容
一、clickhouse连续消费用户的sql分析
二、使用漏斗函数 windowFunnel,分析用户操作行为,计算转化率
clickhouse连续消费用户的sql分析
1)准备好数据 在本地 linux01 /data/user.csv
a,2017-02-05,200
a,2017-02-06,300
a,2017-02-07,200
a,2017-02-08,400
a,2017-02-10,600
b,2017-02-05,200
b,2017-02-06,300
b,2017-02-08,200
b,2017-02-09,400
b,2017-02-10,600
c,2017-01-31,200
c,2017-02-01,300
c,2017-02-02,200
c,2017-02-03,400
c,2017-02-10,600
a,2017-03-01,200
a,2017-03-02,300
a,2017-03-03,200
a,2017-03-04,400
a,2017-03-05,6002)clickhouse 中创建表 【表引擎为MergeTree ,指定主键为name,时间,排序字段为name,时间】
create table tb_shop
(name String,
ctime Date,
money Float64
)engine=MergeTree
primary key(name,ctime)
order by(name,ctime);3)导入数据
clickhouse-client -q "INSERT INTO default.tb_shop FORMAT CSV" < user.csv
也可以使用 cat user.csv | clickhouse-client -q "INSERT INTO default.tb_shop FORMAT CSV"; 导入数据
注:需要在本地linux01 /data/ 下 执行该命令
4)分组,查询每个人 对应的消费时间(数组)、编号数组
select name,
groupArray(ctime) arr,
arrayEnumerate(arr) arr2
from tb_shop
group by name;┌─name─┬─arr─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─arr2───────────────────┐
│ b │ ['2017-02-05','2017-02-06','2017-02-08','2017-02-09','2017-02-10'] │ [1,2,3,4,5] │
│ c │ ['2017-01-31','2017-02-01','2017-02-02','2017-02-03','2017-02-10'] │ [1,2,3,4,5] │
│ a │ ['2017-02-05','2017-02-06','2017-02-07','2017-02-08','2017-02-10','2017-03-01','2017-03-02','2017-03-03','2017-03-04','2017-03-05'] │ [1,2,3,4,5,6,7,8,9,10] │
└──────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────┘5)使用array join 连续连接 arr和arr2 进行多行拼接
select
name,
t,
diff
from(
select
name,
arr,
arr2
from
(
select name,
groupArray(ctime) arr,
arrayEnumerate(arr) arr2
from tb_shop
group by name))
array join arr as t,
arr2 as diff;
6)求出ctime和编号之差,
并通过用户名和差dif 分组,聚合求出次数
select
name,
addDays(t, -diff) as dif,
count(1) as cnt
from
(
select
name,
t,
diff
from(
select
name,
arr,
arr2
from
(
select name,
groupArray(ctime) arr,
arrayEnumerate(arr) arr2
from tb_shop
group by name))
array join arr as t,
arr2 as diff) group by name,dif;
7)再分组聚合,求出最大的连续消费天数
select
name,
max(cnt)
from(
select
name,
addDays(t, -diff) as dif,
count(1) as cnt
from
(
select
name,
t,
diff
from(
select
name,
arr,
arr2
from
(
select name,
groupArray(ctime) arr,
arrayEnumerate(arr) arr2
from tb_shop
group by name))
array join arr as t,
arr2 as diff) group by name,dif) group by name;
本案例要点:
- clickhouse中创建表一定要指定引擎。
- MergeTree表引擎,需要指定主键,本案例中,指定主键为name和ctime,排序字段为name和ctime,方便后续数据处理。记住加上小括号,否则报错。
- 在hive中编号函数为row_number() 加上窗口函数 over(partition by name order by ctime) 就可以对每个用户组的每行数据进行编号;而在clickhouse中,arrayEnumerate(arr)可以求出为数组中的每个元素编上号,但结果也是个数组。groupArray(ctime) arr,求出的也是时间的数组,我们使用array join使其两个数组进行炸裂拼接,得到多行对应数据。
- 编号不是目的,目的是为了得到时间和编号之间的差,方便依据其进行分组得到连续性数据。
使用漏斗函数 windowFunnel,分析用户操作行为,计算转化率
漏斗函数
现有用户行为采集日志数据
现需要对其进行初步采集整合到ODS原始数据层,并且需要求出uv(单一用户访问量)和 用户 事件相关的一些数据统计和计算,比如转化率(完成整个流程【最终交易】的人数占总人数的比例)
1)创建hive表,导入数据
由于所采集静态数据为json格式,不方便操作,我们将其读取,存入到hive中,再将其导出为CSV格式
准备数据
启动hive元数据服务
hive --service metastore &
启动hive客户端
hive
开启hive2服务
hiveserver2
启动连接beeline客户端
创建hive表
create table tb_log(
log string
)
partitioned by(dt string);导入数据到hive表中
load data local inpath "/data/log.log" into table tb_log partition(dt='20201007');
+----------------------------------------------------+------------+
| tb_log.log | tb_log.dt |
+----------------------------------------------------+------------+
| {"account":"l4d9TM","appId":"cn.doitedu.app1","appVersion":"2.0","carrier":"小米移动","deviceId":"KVlABkbMqqwn","deviceType":"REDMI-5","eventId":"adClick","ip":"180.62.91.180","latitude":25.213352855705107,"longitude":100.58718180057615,"netType":"WIFI","osName":"android","osVersion":"6.5","properties":{"adCampain":"7","adId":"6","adLocation":"3","pageId":"41"},"releaseChannel":"豌豆荚","resolution":"1024*768","sessionId":"rGeXt8N0rD1","timeStamp":1602063058368} | 20201007 |
| {"account":"DxL36Dom","appId":"cn.doitedu.app1","appVersion":"2.2","carrier":"中国联通","deviceId":"0HtfcnPofgoR","deviceType":"REDMI-5","eventId":"fetchCoupon","ip":"160.171.79.244","latitude":34.21346724565028,"longitude":117.60034950493103,"netType":"4G","osName":"android","osVersion":"6.5","properties":{"couponId":"2","pageId":"393"},"releaseChannel":"百度手机助手","resolution":"1024*768","sessionId":"meO4lXHYmx1","timeStamp":1602063059899} | 20201007 |
+----------------------------------------------------+------------+2)初步处理数据、创建原始数据层表 tb_ods_log
create table tb_ods_log as
select
if(account='' ,deviceId , account) as guid,
*
from
(select
json_tuple(
log ,
'account' ,'appId' ,'appVersion','carrier','deviceId','deviceType','eventId','ip','latitude','longitude','netType','osName','osVersion','properties','releaseChannel','resolution','sessionId' ,'timeStamp')
as
(account ,appId ,appVersion,carrier,deviceId,deviceType,eventId,ip,latitude,longitude,netType,osName,osVersion,properties,releaseChannel,resolution,sessionId ,`timeStamp`)
from
tb_log)t
where account != '' or deviceId !='' ;select * from tb_ods_log limit 2;
3)导出数据到本地 tsv 格式属性之间以tab键符号隔开
insert overwrite local directory '/data/'
row format delimited fields terminated by '\t'
select * from tb_ods_log;
4)创建clickhouse ods表
原始数据已经初步处理了一下,将json串解析成为了字段,并添加了账号标识guid
clickhouse-client 打开客户端
建表
create table tb_ods_log(
guid String,
account String,
appId String,
appVersion String ,
carrier String,
deviceId String,
deviceType String,
eventId String,
ip String,
latitude String,
longitude String,
netType String,
osName String,
osVersion String,
properties String,
releaseChannel String,
resolution String,
sessionId String,
`timeStamp` String
)engine=MergeTree
order by guid;导入数据
cat 000000_0 | clickhouse-client -q "insert into tb_ods_log FORMAT TSV";
select * from tb_ods_log limit 4;
5)创建需求主题表
CREATE TABLE funnel_test ( uid String, eventId String, eventTime UInt64)
ENGINE = MergeTree
PARTITION BY (uid, eventTime)
ORDER BY (uid, eventTime);导入数据
clickhouse-client -q "insert into funnel_test FORMAT TSV" < event.data
event.dada 数据如下:
uid1 event1 1551398404
uid1 event2 1551398406
uid1 event3 1551398408
uid2 event2 1551398412
uid2 event3 1551398415
uid3 event3 1551398417
uid3 event4 15513984196)链条漏斗sql实践
select uid,
windowFunnel(4)(toDateTime(eventTime),eventId='event1',eventId='event2',eventId='event3') as funnel
from
funnel_test
group by uid;┌─uid──┬─funnel─┐
│ uid3 │ 0 │
│ uid1 │ 3 │
│ uid2 │ 0 │
└──────┴────────┘解析:windowFunnel(n)表示在n毫秒内 执行了步骤事件1、事件2、事件3 的步数 ,如上述查询显示,uid1用户在4毫秒内执行到了第四步,其他人一步也没有执行,这也叫做批处理、流处理过程。
可以用来分析商城注册用户从浏览广告、查看详情、加入购物车、提交订单、支付成功步骤的一个用户执行情况,可以计算转换率,从而为后期的运营进行一个指导。
7)创建clickhouse log2
create table tb_ods_log2 engine=MergeTree order by (guid,`timeStamp`)
as select * from tb_ods_log
8)执行漏斗函数
select `all`,
four,
four+three,
four+three+two,
four+three+two+one
from
(
select
count(1) as `all`,
sum(if(funnel=4,1,0)) as four,
sum(if(funnel=3,1,0)) as three,
sum(if(funnel=2,1,0)) as two,
sum(if(funnel=1,1,0)) as one
from(
select guid,
windowFunnel(1000000)(toDateTime(cast(`timeStamp` as Int64)),
eventId='adShow' ,
eventId='adClick',
eventId='adCart',
eventId='submitOrder'
) as funnel
from tb_ods_log2
group by guid));
更多学习、面试资料尽在微信公众号:Hadoop大数据开发