Hive Sql 分析实例: 淘宝消费者行为分析

数分小本本

已于 2022-06-08 11:31:36 修改

阅读量2.5k

点赞数

文章标签： hive sql big data

于 2022-01-13 16:01:11 首次发布

本文链接：https://blog.csdn.net/licn188/article/details/122475986

版权

本文侧重用 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;

结果:

可以看到 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;

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)