分析函数hive计算均值_clickhouse之连续消费用户和漏斗函数案例实践

目录

本篇主要内容

一、clickhouse连续消费用户的sql分析

二、使用漏斗函数 windowFunnel&＃xff0c;分析用户操作行为&＃xff0c;计算转化率

clickhouse连续消费用户的sql分析

使用漏斗函数 windowFunnel&＃xff0c;分析用户操作行为&＃xff0c;计算转化率

本篇主要内容

一、clickhouse连续消费用户的sql分析
二、使用漏斗函数 windowFunnel&＃xff0c;分析用户操作行为&＃xff0c;计算转化率

clickhouse连续消费用户的sql分析

1&＃xff09;准备好数据 在本地 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,600
2&＃xff09;clickhouse 中创建表 【表引擎为MergeTree &＃xff0c;指定主键为name&＃xff0c;时间&＃xff0c;排序字段为name&＃xff0c;时间】
create table tb_shop
(name String,
ctime Date,
money Float64
)engine&＃61;MergeTree
primary key(name,ctime)
order by(name,ctime);
3&＃xff09;导入数据
clickhouse-client -q "INSERT INTO default.tb_shop FORMAT CSV" 也可以使用 cat user.csv | clickhouse-client -q "INSERT INTO default.tb_shop FORMAT CSV"; 导入数据
注&＃xff1a;需要在本地linux01 /data/ 下 执行该命令

4&＃xff09;分组&＃xff0c;查询每个人 对应的消费时间&＃xff08;数组&＃xff09;、编号数组
select name,
groupArray(ctime) arr,
arrayEnumerate(arr) arr2
from tb_shop
group by name&＃xff1b;
┌─name─┬─arr─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─arr2───────────────────┐
│ b │ [&＃39;2017-02-05&＃39;,&＃39;2017-02-06&＃39;,&＃39;2017-02-08&＃39;,&＃39;2017-02-09&＃39;,&＃39;2017-02-10&＃39;] │ [1,2,3,4,5] │
│ c │ [&＃39;2017-01-31&＃39;,&＃39;2017-02-01&＃39;,&＃39;2017-02-02&＃39;,&＃39;2017-02-03&＃39;,&＃39;2017-02-10&＃39;] │ [1,2,3,4,5] │
│ a │ [&＃39;2017-02-05&＃39;,&＃39;2017-02-06&＃39;,&＃39;2017-02-07&＃39;,&＃39;2017-02-08&＃39;,&＃39;2017-02-10&＃39;,&＃39;2017-03-01&＃39;,&＃39;2017-03-02&＃39;,&＃39;2017-03-03&＃39;,&＃39;2017-03-04&＃39;,&＃39;2017-03-05&＃39;] │ [1,2,3,4,5,6,7,8,9,10] │
└──────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────┘
5&＃xff09;使用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&＃xff1b;

6&＃xff09;求出ctime和编号之差&＃xff0c;
并通过用户名和差dif 分组&＃xff0c;聚合求出次数
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&＃xff1b;

7&＃xff09;再分组聚合&＃xff0c;求出最大的连续消费天数
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;

本案例要点&＃xff1a;

• clickhouse中创建表一定要指定引擎。
• MergeTree表引擎&＃xff0c;需要指定主键&＃xff0c;本案例中&＃xff0c;指定主键为name和ctime&＃xff0c;排序字段为name和ctime&＃xff0c;方便后续数据处理。记住加上小括号&＃xff0c;否则报错。
• 在hive中编号函数为row_number() 加上窗口函数 over(partition by name order by ctime) 就可以对每个用户组的每行数据进行编号&＃xff1b;而在clickhouse中&＃xff0c;arrayEnumerate&＃xff08;arr&＃xff09;可以求出为数组中的每个元素编上号&＃xff0c;但结果也是个数组。groupArray(ctime) arr&＃xff0c;求出的也是时间的数组&＃xff0c;我们使用array join使其两个数组进行炸裂拼接&＃xff0c;得到多行对应数据。
• 编号不是目的&＃xff0c;目的是为了得到时间和编号之间的差&＃xff0c;方便依据其进行分组得到连续性数据。

使用漏斗函数 windowFunnel&＃xff0c;分析用户操作行为&＃xff0c;计算转化率

漏斗函数
现有用户行为采集日志数据

现需要对其进行初步采集整合到ODS原始数据层&＃xff0c;并且需要求出uv&＃xff08;单一用户访问量&＃xff09;和 用户 事件相关的一些数据统计和计算&＃xff0c;比如转化率&＃xff08;完成整个流程【最终交易】的人数占总人数的比例&＃xff09;
1&＃xff09;创建hive表&＃xff0c;导入数据
由于所采集静态数据为json格式&＃xff0c;不方便操作&＃xff0c;我们将其读取&＃xff0c;存入到hive中&＃xff0c;再将其导出为CSV格式
准备数据

启动hive元数据服务
hive --service metastore &

启动hive客户端
hive

开启hive2服务
hiveserver2

启动连接beeline客户端

创建hive表
create table tb_log(
log string
)
partitioned by(dt string)&＃xff1b;
导入数据到hive表中
load data local inpath "/data/log.log" into table tb_log partition(dt&＃61;&＃39;20201007&＃39;);
&＃43;----------------------------------------------------&＃43;------------&＃43;
| tb_log.log | tb_log.dt |
&＃43;----------------------------------------------------&＃43;------------&＃43;
| {"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 |
&＃43;----------------------------------------------------&＃43;------------&＃43;
2&＃xff09;初步处理数据、创建原始数据层表 tb_ods_log
create table tb_ods_log as
select
if(account&＃61;&＃39;&＃39; ,deviceId , account) as guid,
*
from
(select
json_tuple(
log ,
&＃39;account&＃39; ,&＃39;appId&＃39; ,&＃39;appVersion&＃39;,&＃39;carrier&＃39;,&＃39;deviceId&＃39;,&＃39;deviceType&＃39;,&＃39;eventId&＃39;,&＃39;ip&＃39;,&＃39;latitude&＃39;,&＃39;longitude&＃39;,&＃39;netType&＃39;,&＃39;osName&＃39;,&＃39;osVersion&＃39;,&＃39;properties&＃39;,&＃39;releaseChannel&＃39;,&＃39;resolution&＃39;,&＃39;sessionId&＃39; ,&＃39;timeStamp&＃39;)
as
(account ,appId ,appVersion,carrier,deviceId,deviceType,eventId,ip,latitude,longitude,netType,osName,osVersion,properties,releaseChannel,resolution,sessionId ,&＃96;timeStamp&＃96;)
from
tb_log)t
where account !&＃61; &＃39;&＃39; or deviceId !&＃61;&＃39;&＃39; ;
select * from tb_ods_log limit 2&＃xff1b;

3&＃xff09;导出数据到本地 tsv 格式属性之间以tab键符号隔开
insert overwrite local directory &＃39;/data/&＃39;
row format delimited fields terminated by &＃39;t&＃39;
select * from tb_ods_log;

4&＃xff09;创建clickhouse ods表
原始数据已经初步处理了一下&＃xff0c;将json串解析成为了字段&＃xff0c;并添加了账号标识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,
&＃96;timeStamp&＃96; String
)engine&＃61;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&＃xff09;创建需求主题表
CREATE TABLE funnel_test ( uid String, eventId String, eventTime UInt64)
ENGINE &＃61; MergeTree
PARTITION BY (uid, eventTime)
ORDER BY (uid, eventTime)&＃xff1b;
导入数据
clickhouse-client -q "insert into funnel_test FORMAT TSV" event.dada 数据如下&＃xff1a;
uid1 event1 1551398404
uid1 event2 1551398406
uid1 event3 1551398408
uid2 event2 1551398412
uid2 event3 1551398415
uid3 event3 1551398417
uid3 event4 1551398419
6&＃xff09;链条漏斗sql实践
select uid,
windowFunnel(4)(toDateTime(eventTime),eventId&＃61;&＃39;event1&＃39;,eventId&＃61;&＃39;event2&＃39;,eventId&＃61;&＃39;event3&＃39;) as funnel
from
funnel_test
group by uid;
┌─uid──┬─funnel─┐
│ uid3 │ 0 │
│ uid1 │ 3 │
│ uid2 │ 0 │
└──────┴────────┘
解析&＃xff1a;windowFunnel&＃xff08;n&＃xff09;表示在n毫秒内 执行了步骤事件1、事件2、事件3 的步数 &＃xff0c;如上述查询显示&＃xff0c;uid1用户在4毫秒内执行到了第四步&＃xff0c;其他人一步也没有执行&＃xff0c;这也叫做批处理、流处理过程。
可以用来分析商城注册用户从浏览广告、查看详情、加入购物车、提交订单、支付成功步骤的一个用户执行情况&＃xff0c;可以计算转换率&＃xff0c;从而为后期的运营进行一个指导。

7&＃xff09;创建clickhouse log2
create table tb_ods_log2 engine&＃61;MergeTree order by (guid,&＃96;timeStamp&＃96;)
as select * from tb_ods_log

8&＃xff09;执行漏斗函数
select &＃96;all&＃96;,
four,
four&＃43;three,
four&＃43;three&＃43;two,
four&＃43;three&＃43;two&＃43;one
from
(
select
count(1) as &＃96;all&＃96;,
sum(if(funnel&＃61;4,1,0)) as four,
sum(if(funnel&＃61;3,1,0)) as three,
sum(if(funnel&＃61;2,1,0)) as two,
sum(if(funnel&＃61;1,1,0)) as one
from(
select guid,
windowFunnel(1000000)(toDateTime(cast(&＃96;timeStamp&＃96; as Int64)),
eventId&＃61;&＃39;adShow&＃39; ,
eventId&＃61;&＃39;adClick&＃39;,
eventId&＃61;&＃39;adCart&＃39;,
eventId&＃61;&＃39;submitOrder&＃39;
) as funnel
from tb_ods_log2
group by guid));

更多学习、面试资料尽在微信公众号&＃xff1a;Hadoop大数据开发