发布日期：2021-11-29
VIP内容
用户消费行为分析：消费习惯分析
下面我们演示一个使用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;
统计男女用户的消费频次是否有差异
思路：将order_info表与user_info表进行内部联结inner join，对'用户id'和对应的'性别sex'进行分组，并统计每个用户的消费次数，最后再对性别进行分组，求出'男'&'女'对应的平均消费次数。
select sex as `性别`, round(avg(ct),4) as `平均消费次数`
from (select o.userid,sex,count(o.userid) as ct
from orderinfo as o
inner join (SELECT * FROM userinfo where sex <> '') as s
on o.userid = s.userid
where ispaid = '已支付'
group by o.userid,sex
) as b
group by sex;
执行以上代码，查询结果如下：
性别 平均消费次数 女 1.7829 男 1.8035
统计多次消费，第一次消费和最后一次消费的间隔是多少
思路：先筛选出'已支付'的'userid'，并对用户进行分组group by,再挑选出消费次数>1的用户id，最后得出第一次消费和最后一次消费的时间点及时间差。
select userid as `用户`,
max(paidate) as `最后一次消费`,
min(paidate) as `第一次消费`,
datediff(max(paidate),min(paidate)) as `两次消费的时间差`
from (select *, from_unixtime(unix_timestamp(paidtime,"yyyy/MM/dd HH:mm"),"yyyy-MM-dd") as paidate
from orderinfo
where ispaid='已支付') as t
group by userid
having count(userid) > 1;
执行以上代码，查询结果如下(部分)：
...... 87889 2016-03-27 2016-03-26 1 8789 2016-04-20 2016-03-01 50 87892 2016-03-27 2016-03-25 2 87895 2016-04-29 2016-03-25 35 87896 2016-03-26 2016-03-26 0 879 2016-04-23 2016-04-21 2 87909 2016-04-14 2016-03-26 19 87911 2016-04-14 2016-03-26 19 87914 2016-03-26 2016-03-26 0 8792 2016-04-11 2016-04-11 0 87921 2016-04-05 2016-03-26 10 87926 2016-03-26 2016-03-26 0 87927 2016-03-27 2016-03-26 1 87942 2016-03-26 2016-03-26 0 87945 2016-03-28 2016-03-27 1 87948 2016-03-27 2016-03-26 1 8795 2016-04-22 2016-04-17 5 87951 2016-03-26 2016-03-26 0 87959 2016-03-26 2016-03-26 0 8796 2016-04-06 2016-03-04 33 87962 2016-03-28 2016-03-26 2 87975 2016-04-25 2016-03-26 30 ......
计算平均消费间隔
select round(avg(jg),4) as `平均消费间隔`
from(
select userid as `用户`,datediff(max(paidate),min(paidate)) as jg
from (select *, from_unixtime(unix_timestamp(paidtime,"yyyy/MM/dd HH:mm"),"yyyy-MM-dd") as paidate
from orderinfo
where ispaid='已支付') as t
group by userid
having count(userid) > 1
) t2;
执行以上代码，查询结果如下：
平均消费间隔 15.6484
统计不同年龄段，用户的消费金额是否有差异
思路：先对数据进行筛选，再以10年为时间间隔进行划分各个年龄段，最后再挑出各用户、年龄段及各年龄段的人数，最后再进行统计分析。
select j.age, round(avg(j.sp),2) as `平均消费金额`
from(
select o.userid, u.age, sum(o.prince) as sp
from orderinfo as o
inner join(select t.userid, ceil((year(CURRENT_DATE()) - year(t.birthday))/10)*10 as age
from (select *, from_unixtime(unix_timestamp(birth,"yyyy/MM/dd"),"yyyy-MM-dd") as birthday from userinfo) t
where t.birthday is not null
) as u
on o.userid=u.userid
where o.ispaid='已支付'
group by o.userid, u.age
) j
group by j.age;
执行以上代码，查询结果如下：
10 1320.7 20 819.67 30 1007.79 40 1158.24 50 1202.3 60 1107.06 70 1043.28 80 948.13 90 747.55 100 57804.91 130 699.0
统计消费的二八法则：消费top20%的用户贡献了多少额度
思路：先统计全部的消费用户数，计算出其20%的用户数大致是多少。
select (count(distinct userid) * 0.2) as `百分之20的用户数` from orderinfo where ispaid = '已支付';
执行以上代码，查询结果如下：
百分之20的用户数 17129.8
思路：根据上一步得到的20%的用户数，计算用户的消费总额并进行排序。
select userid, cast(sum(prince) as decimal(18,2)) as total from orderinfo where ispaid = '已支付' group by userid order by total desc limit 17130;
执行以上代码，查询结果如下：
11211 231167990.00 57282 457232.00 62590 223636.00 14427 221821.00 53616 203981.00 68226 162415.29 25263 108717.00 14271 108385.00 44050 100229.00 57018 96293.00 68215 77072.82 53527 66345.00 54166 61648.00 5775 56393.00 17507 54415.00 64012 47553.00 68229 46474.52 21442 45302.00 25291 42519.60 ......
思路：根据上步得到的结果，算出前20%的用户的消费总额。
select cast(sum(t.total) as decimal(12,2)) as `top20的消费总额`
from (
select userid, sum(prince) as total
from orderinfo
where ispaid = '已支付'
group by userid
order by total desc
limit 17130
) t;
执行以上代码，查询结果如下：
top20的消费总额 272203771.46
思路：计算全部的已支付用户的消费总额
select cast(sum(prince) as decimal(12,2)) as `全部消费总额` from orderinfo where ispaid='已支付';
执行以上代码，查询结果如下：
全部消费总额 318503081.55
综合上面几步，一次性计算top 20用户消费占比。
with
top as (
select cast(sum(t.total) as decimal(12,2)) as t20
from (
select userid, sum(prince) as total
from orderinfo
where ispaid = '已支付'
group by userid
order by total desc
limit 17130
) t
),
total as (
select cast(sum(prince) as decimal(12,2)) as tt
from orderinfo
where ispaid='已支付'
)
select top.t20 as `top20消费金额`, total.tt as `总消费金额`, round(top.t20/total.tt,4) as `top20消费占比`
from top, total;
执行以上代码，查询结果如下：
top20消费金额 总消费金额 top20消费占比 272203771.46 318503081.55 0.8546
分析总结
通过以上分析，可以得出以下结论：
- 1. 男女的平均消费频次为1.8035、1.7827，可见男女在消费频次上并不会存在较大的差异；
- 2. 由消费间隔的统计可知，多次消费的用户的平均消费间隔为15天左右；
- 3. 由"不同年龄段的平均消费金额"结果可知，青年及中年的消费力度较高，而少年及中老年可能因为经济等相关原因而导致消费力度相对较低；
- 4. 由消费的二八法则统计得知：消费top20%的用户贡献了近272203771.46元的消费额度，占到了总消费额度的85.46%，是值得该公司重点维护的主要客户。