用户消费行为分析：复购率和回购率计算
下面我们演示一个使用Hive来统计某电商用户消费行为的案例。主要包括内容：
- 创建Hive表结构，并加载数据；
- 统计不同月份的下单人数；
- 统计每个月的复购率；
- 统计每个月的回购率。
数据集说明
在PBLP平台的~/data/hive/user_order/目录下，我们提供了两个数据文件。一个是订单数据文件order_info_utf.csv，一个是用户数据文件user_info_utf.csv。其中：
order_info_utf.csv数据格式如下：
订单id,用户id,支付状态,支付价格,日期 1,11211,已支付,833,2016/3/1 0:04 2,11211,已支付,487,2016/3/1 0:03 3,11211,未支付,794, 4,74553,已支付,737,2016/3/1 0:02 5,37799,已支付,647,2016/3/1 0:01 ...
user_info_utf.csv数据格式如下：
用户id,性别,出生日期 1,女,1969/1/21 2,女,1978/9/6 3,男,1973/9/3 4,女,1982/4/27 5,男,1970/7/30 ...
创建Hive表结构，并加载数据
首先创建订单信息表orderinfo：
CREATE TABLE IF NOT EXISTS ORDERINFO (
ORDERID string,
USERID string,
ISPAID string,
PRINCE string,
PAIDTIME string
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
然后创建用户信息表userinfo：
CREATE TABLE IF NOT EXISTS USERINFO (
USERID string,
SEX string,
BIRTH string
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
执行下面两个load语句，分别导入两个数据文件到相应的Hive表中：
load data local inpath "/home/hduser/data/hive/user_order/order_info_utf.csv" into table ORDERINFO; load data local inpath "/home/hduser/data/hive/user_order/user_info_utf.csv" into table USERINFO;
测试数据导入是否成功：
select * from orderinfo limit 5; select * from userinfo limit 5;
统计不同月份的下单人数
这里我们需要做两个处理。一是将日期格式化为yyyy-MM的形式，二是过滤已下单的订单记录。
select paidmonth, count(1) as `下单人数`
from (
select *, from_unixtime(unix_timestamp(paidtime,"yyyy/MM/dd HH:mm"),"yyyy-MM") as paidmonth
from orderinfo
where ispaid="已支付"
) t
group by paidmonth;
执行以上Hive QL查询语句，计算结果如下所示：
2016-03 238474 2016-04 223324 2016-05 7
统计用户3月份的复购率
什么是复购率？
复购率指的是在本月消费中消费一次以上的占比。
也就是说，我们需要统计，userid分组之后，按月，消费次数大于1的统计。
select sum(t.`复购用户`) as `复购用户数`,count(t.userid) as `总用户数`, ROUND(sum(t.`复购用户`)/count(t.userid), 4) as `复购率`
from (
select userid,case when count(userid)>1 then 1 else 0 end as `复购用户`
from ORDERINFO
where ispaid = '已支付' and from_unixtime(unix_timestamp(paidtime,"yyyy/MM/dd HH:mm"),"yyyy-MM")='2016-03'
group by userid
) as t;
在上面的代码中，我们使用case when语句标记消费次数大于1次的用户为1，否则为0。然后再对标记1求和，就得到复购用户数。拿复购用户数去除以总用户数，就得到了复购率。
执行以上Hive QL查询语句，计算结果如下所示：
16916 54799 0.3087
可以看到，2016年3月份的复购率是30.87%。
统计用户各个月份的回购率
什么是回购率？
所谓回购，指的是用户本月购买和并且下个月也购买。因此回购率指的是用户下月进行回购的比率。
也就是说，我们需要统计，userid分组之后，按月，消费次数大于1的统计。
这里实现的思路是，我们以本月的记录为左表，以下个月的记录为右表，然后执行一个左表连接，这样连接以后的表中就包含了本月消费用户和下月回购用户，接着执行group by分组统计即可。
这里有个小托雷斯，在执行left join时，on的条件中，虚拟表1的对应月份字段为虚拟表2对应月份字段+1就可以。
select a.paidmonth as `月份`,count(distinct a.userid) as `本月消费的用户数量`,count(b.userid) as `本月回购的用户数` , cast(round(count(b.userid)/count(distinct a.userid),4) as decimal(10,4)) as `回购率`
from (
select userid, month(from_unixtime(unix_timestamp(paidtime,"yyyy/MM/dd HH:mm"),"yyyy-MM-dd")) as paidmonth
from ORDERINFO
where ISPAID = '已支付'
group by userid,month(from_unixtime(unix_timestamp(paidtime,"yyyy/MM/dd HH:mm"),"yyyy-MM-dd"))
) a left join (
select userid, month(from_unixtime(unix_timestamp(paidtime,"yyyy/MM/dd HH:mm"),"yyyy-MM-dd")) as paidmonth
from ORDERINFO
where ISPAID = '已支付'
group by userid,month(from_unixtime(unix_timestamp(paidtime,"yyyy/MM/dd HH:mm"),"yyyy-MM-dd"))
) b
on a.userid = b.userid and a.paidmonth = b.paidmonth+1
group by a.paidmonth;
执行以上Hive QL查询语句，计算结果如下所示：
3 54799 0 0.0000 4 43967 13119 0.2984 5 6 4 0.6667
因为3月份是第一个月，没有上个月的消费记录，所以回购率是0。而4月份的回购率是29.84%，5月份的回购率是66.67%。