- 本文侧重用 Hive SQL 处理数据
1. 数据集说明
这次分析用的数据是来自阿里云天池:
数据集来源:user_behavior
数据采用csv格式保存, 未解压前905MB, 解压后3.41G
数据集包含了 2017 年 11 月 25 日至 2017 年 12 月 3 日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和 MovieLens-20M 类似，即数据集的每一行表示一条用户行为，由用户 ID、商品 ID、商品类目 ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下:
| 列名称 | 说明 |
|---|---|
| 用户 ID | 整数类型，序列化后的用户 ID |
| 商品 ID | 整数类型，序列化后的商品 ID |
| 商品类目 ID | 整数类型，序列化后的商品所属类目 ID |
| 行为类型 | 字符串，枚举类型，包括 ('pv', 'buy', 'cart', 'fav') |
| 时间戳 | 行为发生的时间戳 |
注意到，用户行为类型共有四种，它们分别是
| 行为类型 | 说明 |
|---|---|
| pv | 商品详情页 pv，等价于点击 |
| buy | 商品购买 |
| cart | 将商品加入购物车 |
| fav | 收藏商品 |
2. 数据处理思路, 整个过程以 Hive 为主
- 使用pandas做数据的初步处理, 只取日期 2017 年数据, 只取部分数据
- 导入 hive 清洗数据
- Hive 查询数据做分析
3. 数据处理
3.1 pandas 初步处理, 简单清洗
数据量过大, 导入 pandas 时分块
分块导入用 pd.read_csv 参数 chunksize, 以50万行为一块
import pandas as pd
data_path = r'D:\Downloads\Compressed\UserBehavior.csv'
chunks = pd.read_csv(data_path, chunksize=500000)
3.2 取 39块*50万条=1950万条 里, 满足条件 year==2017的数据
- 为了后续筛选方便, 指定了列名
- 后续 Hive 导入数据时一般没有表头, 所有导出时 header 参数设为 False
- 筛选后保存为 csv 时, 用 mode='a', 追加模式, 否则只有最后一块的数据
i = 1
for chunk_i in chunks:
chunk_i.columns = ['uid', 'itemid', 'catid', 'behavior', 'timestamp']
chunk_i['date'] = pd.to_datetime(chunk_i['timestamp'], unit='s').dt.date # dt. 是 pandas 中和时期相关的函数
chunk_i = chunk_i[pd.to_datetime(chunk_i['timestamp'], unit='s').dt.year==2017]
chunk_i.to_csv(r'D:\Downloads\Compressed\2017_1.csv', mode='a', encoding='utf-8', header=False, index=False) # a 模式是追加
print('已完成第{}个chunk!!!)'.format(str(i)))
i += 1
if i >=40:
break
3.3 Hive 建表
- 不能直接用 timestamp, datetime 做为字段名, 查询会报错
drop table if exists user_behavior;
create table user_behavior
(
`user_id` string comment '用户ID',
`item_id` string comment '商品ID',
`category_id` string comment '商品类目ID',
`behavior_type` string comment '行为类型，枚举类型，包括(pv, buy, cart, fav)',
`time_stamp` int comment '行为时间戳',
date_time string comment '行为时间'
)
row format delimited
fields terminated by ','
lines terminated by '\n';
加载数据
LOAD DATA LOCAL INPATH '/opt/module/hivedata/2017_1.csv' OVERWRITE INTO TABLE user_behavior;
3.4 数据清洗
- 删除重复数据
- 全字段分组方法
insert overwrite table
user_behavior
select
user_id,
item_id,
category_id,
behavior_type,
time_stamp,
date_time
from user_behavior
group by user_id, item_id, category_id, behavior_type, time_stamp, date_time;
查看去重前后数据条数 前:19499724 后:199499716
select count(*) from user_behavior;
- 查看时间是否有异常值
select date(date_time) as day from user_behavior group by date(date_time) order by day;
结果:
image.png
可以看到 11.01 至 12.06 这部分日期是连续的
- 选取 11.01 至 12.06 部分数据
insert overwrite table
user_behavior
select
user_id,
item_id,
category_id,
behavior_type,
time_stamp,
date_time
from user_behavior
where cast(date_time as date) between '2017-11-01' and '2017-12-06';
查看条数: 19499554, 只比之前少了约 50 万条, 上面的截取是合理的
select count(*) from user_behavior;
OK
_c0
19499554
Time taken: 0.078 seconds, Fetched: 1 row(s)
4. 数据分析
4.1 用户流量及购物情况
4.1.1 总访问量PV，总用户量UV
select
sum
(
case when behavior_type = 'pv' then 1 else 0 end
) as PV,
count(distinct user_id) as UV
from user_behavior;
result:
pv uv
17462837 193072
Time taken: 12.062 seconds, Fetched: 1 row(s)
4.1.2 日访问量, 日访客量
select
cast(date_time as date) as day,
sum
(
case when behavior_type = 'pv' then 1 else 0 end
) as PV,
count(distinct user_id) as UV
from user_behavior
group by cast(date_time as date)
order by day;
4.1.3 每个用户的购物情况, 加工到表 user_behavior_count
- 建表: 每个用户ID的点击数, 收藏数, 加购物车数, 购买数
drop table if exists user_behavior_count;
create table
user_behavior_count
as
select
user_id,
sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,
sum(case when behavior_type = 'fav' then 1 else 0 end) as fav,
sum(case when behavior_type = 'cart' then 1 else 0 end) as cart,
sum(case when behavior_type = 'buy' then 1 else 0 end) as buy
from user_behavior
group by user_id
4.1.4 复购率: 产生两次以上购买的用户占总购买用户的比例
select round(sum(case when buy > 1 then 1 else 0 end) / sum(case when buy > 0 then 1 else 0 end),2) as repur_rate
from user_behavior_count;
# 结果
repur_rate
0.66
Time taken: 3.885 seconds, Fetched: 1 row(s)
复购率 0.65, 一个月加6天的复购率相当高, 可以看到数据并不真实
4.2 用户行为转换率
- 点击/(购物车+收藏)/购买, 各环节转化率
select
t1.pv,
t1.fav,
t1.cart,
t1.buy,
round((t1.fav+t1.cart)/t1.pv,2) as pv2favcart,
round(t1.buy/(t1.fav+t1.cart),2) as favcart2buy,
round(t1.buy/t1.pv,2) as pv2buy
from
(
select
sum(pv) as pv,
sum(fav) as fav,
sum(cart) as cart,
sum(cart) as buy
from user_behavior_count
) t1
# 结果
t1.pv t1.fav t1.cart t1.buy pv2favcart favcart2buy pv2buy
17462837 561876 1080448 1080448 0.09 0.66 0.06
4.3 用户行为习惯
4.3.1 一天的活跃时段分布
- 注意用 from_unixtime 把时间戳转化为 datetime, 再用 hour() 取出小时数
- from_unixtime 不指定参数的话返回的是标准日期时间格式 '2017-11-29 07:36:11'
select
hour(from_unixtime(time_stamp)) as hour,
sum(case when behavior_type='pv' then 1 else 0 end) as pv,
sum(case when behavior_type='fav' then 1 else 0 end) as fav,
sum(case when behavior_type='cart' then 1 else 0 end) as cart,
sum(case when behavior_type='bay' then 1 else 0 end) as buy
from user_behavior
group by hour(from_unixtime(time_stamp))
order by hour asc;
# 结果:
hour pv fav cart buy
0 594554 20300 37359 0
1 727700 25338 45161 0
2 843907 29117 52355 0
3 821918 28716 50895 0
4 827374 27341 50162 0
5 908718 29583 54490 0
6 906076 28846 53930 0
7 937116 29168 55822 0
8 899795 29128 53910 0
9 813879 26529 49851 0
10 836029 25335 49278 0
11 1061073 31121 61742 0
12 1281650 37073 76848 0
13 1467984 43016 90998 0
14 1447740 44848 94745 0
15 1094537 37631 77633 0
16 594787 20715 35773 0
17 276324 9879 16967 0
18 152075 5576 9190 0
19 101803 3738 6488 0
20 87890 2919 5493 0
21 112117 3708 7232 0
22 236643 7812 15696 0
23 431148 14439 28430 0
Time taken: 16.276 seconds, Fetched: 24 row(s)
4.3.2 一周用户的活跃分布
- pmod(int a, int b) pmod(double a, double b) 返回 a 除 b 的余数的绝对值
- 低版本: Hive2.2.0前 没有dayofweek, 在些之前的实现方式 pmod(datediff('${date}', '1920-01-01') - 3, 7)
- 低版本写法:
select
pmod(datediff(date_time, '1920-01-01') - 3, 7) as weekday,
sum(case when behavior_type = 'pv' then 1 else 0 end) as pv, --点击数
sum(case when behavior_type = 'fav' then 1 else 0 end) as fav, --收藏数
sum(case when behavior_type = 'cart' then 1 else 0 end) as cart, --加购物车数
sum(case when behavior_type = 'buy' then 1 else 0 end) as buy --购买数
from user_behavior
where date(date_time) between '2017-11-27' and '2017-12-03'
group by pmod(datediff(date_time, '1920-01-01') - 3, 7)
order by weekday;
# 结果
weekday pv fav cart buy
0 2094177 66600 132406 46351
1 1745630 55989 105415 42242
2 1722171 56017 104236 41420
3 1791741 58662 108235 43361
4 1835891 58967 111709 43701
5 1949782 60964 125542 42104
6 2438566 79313 157069 51231
Time taken: 11.368 seconds, Fetched: 7 row(s)
- dayofweek 写法
select
dayofweek(date_time) as weekday,
sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,
sum(case when behavior_type = 'fav' then 1 else 0 end) as fav,
sum(case when behavior_type = 'cart' then 1 else 0 end) as cart,
sum(case when behavior_type = 'buy' then 1 else 0 end) as buy
from user_behavior
group by dayofweek(date_time)
order by dayofweek(date_time);
# 结果
weekday pv fav cart buy
1 1745764 55989 105415 42242
2 1722338 56017 104236 41420
3 1792162 58662 108235 43361
4 1837044 58967 111709 43701
5 2164021 67940 137930 45275
6 4269135 137972 267528 90002
7 3932373 126329 245395 88392
Time taken: 9.751 seconds, Fetched: 7 row(s)
4.4 基于 RFM 模型找出有价值的用户
- 近度 R-Rencecy 最近一次购买时间
- 频率 F-Frequency 消费频率
- M-Money 消费金额
- R R_rank 值越小, 用户越活跃
- 这里的窗口函数是用来添加排名的
- RANK() 排序相同时会重复，总数不会变. 如果第1名重复的时候就没有第2名,1,1,3,4...
- dense_rank 排序相同时会重复，总数会减少. 如果第1名重复的时候, 1,1,2,3...
- ROW_NUMBER() 会根据顺序计算. 重复位置也排名, 第1名和第2名分数相同时, 1,2,3,4...
- datediff(date_1, date_2) date_1 减去 date_2
- R
select
user_id,
datediff('2017-12-06', max(date_time)) as R,
dense_rank()over(order by datediff('2017-12-06', max(date_time)) ASC) as R_rank
from user_behavior
where behavior_type='buy'
group by user_id
limit 50;
- F
select
user_id,
count(1) as F,
dense_rank()over(order by count(1) DESC) as F_rank
from user_behavior
where behavior_type='buy'
group by user_id
limit 50,
- 对有购买行为的用户按照排名进行分组，共划分为 5 组，
- 前 - 1/5 的用户打 5 分
- 前 1/5 - 2/5 的用户打 4 分
- 前 2/5 - 3/5 的用户打 3 分
- 前 3/5 - 4/5 的用户打 2 分
- 前 4/5 - 的用户打 1 分
- 按照这个规则分别对用户时间间隔排名打分和购买频率排名打分，最后把两个分数合并在一起作为该名用户的最终评分
with t1 as
(
select
user_id,
datediff('2017-12-06', max(date_time)) as R,
dense_rank()over(order by datediff('2017-12-06', max(date_time)) ASC) as R_rank,
count(1) as F,
dense_rank()over(order by count(1) DESC) as F_rank
from user_behavior
where behavior_type='buy'
group by user_id
)
select
user_id,
R,
R_rank,
R_score,
F,
F_rank,
F_score,
R_score+F_score as score
from
(
select
*,
case ntile(5) over(order by R_rank)
when 1 then 5
when 2 then 4
when 3 then 3
when 4 then 2
when 5 then 1
end as R_score,
case ntile(5) over(order by F_rank)
when 1 then 5
when 2 then 4
when 3 then 3
when 4 then 2
when 5 then 1
end as F_score
from t1
) as t2
order by score desc
limit 20
# 结果
user_id r r_rank r_score f f_rank f_score score
702034 3 1 5 159 1 5 10
562220 3 1 5 4 59 5 10
490508 3 1 5 87 3 5 10
337305 3 1 5 93 2 5 10
107932 3 1 5 72 5 5 10
754360 3 1 5 71 6 5 10
834051 3 1 5 69 7 5 10
705488 3 1 5 4 59 5 10
381745 3 1 5 45 19 5 10
705460 3 1 5 4 59 5 10
128379 3 1 5 65 10 5 10
914956 3 1 5 43 20 5 10
675514 3 1 5 61 12 5 10
190873 3 1 5 61 12 5 10
242650 3 1 5 60 13 5 10
122504 3 1 5 69 7 5 10
9610 3 1 5 67 8 5 10
858271 3 1 5 53 16 5 10
165222 3 1 5 51 17 5 10
549770 3 1 5 66 9 5 10
Time taken: 14.839 seconds, Fetched: 20 row(s)
