实战：千万级新零售购买行为分析

环境启动

node1上启动hadoop集群：startha.sh（这是之前写的一个Hadoop Yarn高可用集群启动脚本）

关于脚本编写以及集群搭建可以参考Yarn资源调度器-CSDN博客

node3上开启hive并开启此次会话输出时打印内容包含标题的设置（hive安装下载参考Hive下载与安装_apache.hive下载-CSDN博客）

[root@node3 ~]# hive –hiveconf hive.cli.print.header=true

（这里可以不需要先使用hive –service metastore，该命令是客户端远程操作hive时候使用的，比如之前的node4。node3是直接数据库的。）

数据准备

文件
user_info_format1.csv存放的是用户的信息，文件中的字段：

user_id，age_range，gender。

用户建表脚本：

create table user_info(
id int comment "唯一标示id",
age_range int comment "年龄范围",
gender int comment "性别 0女 1 男 2保密"
)
row format delimited
fields terminated by ","
lines terminated by "\\n";

文件
user_log_format1.csv存放的是购买日志，由于数据量大不建议直接打开，电脑配置不好的容易开始。可以通过PowerShell（Win+X打开快捷菜单后选择终端/Windows PowerShell;也可以在进入到文件的文件夹后点击文件夹路径栏（顶部显示 “此电脑> D 盘 > data” 的位置），输入
powershell，按回车，这种方式不用切换文件目录
），切换到文件所在的目录后，使用以下命令查看文件内标题行。
# 切换到文件目录后执行
Get-Content "user_log_format1.csv" -TotalCount 1

标题字段如下：

user_id,item_id,cat_id,seller_id,brand_id,time_stamp,action_type

注意：本次练习中默认user_log中的数据都是购买记录，所以后续查询可以忽略action_type。

用户建表脚本：

create table user_log(
user_id int comment "买家id",
item_id int comment "产品id",
cat_id int comment "分类id",
seller_id int comment "卖家id",
brand_id int comment "品牌id",
time_stamp bigint comment "时间戳",
action_type int
)
row format delimited
fields terminated by ","
lines terminated by "\\n";

导入数据到表中

• 直接将数据文件user_info_format1.csv和 user_log_format1.csv拖到node3的/root目录下。需安装lrzsz（yum install lrzsz）
• 将user_info_format1.csv文件中的数据load到hive的表user_info表中

hive> load data local inpath '/root/user_info_format1.csv' into table user_info;

• 将user_log_format1.csv文件中的数据load到hive的表user_log表中

hive> load data local inpath '/root/user_log_format1.csv' into table user_log;

业务分析查询

查询用户的总个数

hive> select count(id) from user_info where id is not null;
OK
424170

查询购物记录的总条数

hive>select count(user_id) log_num from user_log where user_id is not null;
OK
log_num
54925330

查询卖家的总数量

数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换。

hive>select count(seller_id) seller_num
from (select seller_id from user_log where seller_id is not null group by seller_id) tmp;
OK
seller_num
4995

查询热卖商品前10名

最容易想到的sql语句

select item_id,count(user_id) num
from user_log
group by item_id
order by num DESC
limit 10;

全局排序，
hql
转换后的
mr
作业只有一个
reduce
任务。当数据量比较大时order by
就要慎用，很有可能导致
reduce需要较长的时间才能完成，或者完不成。需要对sql进行优化。上次采用了分而治之的思想（具体参考其中的基站掉话率分析
Hive核心SQL（基础）-CSDN博客
），这里采用窗口函数来进行优化：
窗口函数可以把 “单 Reduce 全局排序” 拆解为 “多 Reduce 并行计算 + 轻量全局筛选”，避开 ORDER BY 强制单 Reduce 的性能瓶颈。

— 窗口函数
with item_counts as (
select
item_id,
count(user_id) as cnt,
row_number() over(order by count(user_id) desc) as rn
from user_log
where user_id is not null
group by item_id
)
select item_id, cnt
from item_counts
where rn <= 10
order by cnt desc;

结果

OK
item_iditem_num
67897345905
783997178005
63686382480
63171442771
6151834801
55996728816
77066828431
9460923027
105989922242

查询热卖品牌前10名

with tem as (
select count(user_id) num,brand_id,
row_number() over(order by count(user_id) desc) as rank
from user_log
where user_id is not null
group by brand_id
)

select brand_id,num
from tem
where rank <=10
order by num desc;

查询结果：

brand_id num
3738 763345
1360 737545
1446 729555
1214 541075
5376 528003
82 503911
2276 491738
8235 400024
4705 363417
1662 332633

查询购买商品数量最多的前50名用户

with tem as(
select user_id,count(item_id) num,
row_number() over(order by count(item_id) desc) rn
from user_log
where user_id is not null
group by user_id
)

select user_id,num
from tem
where rn <=50
order by num desc;

结果

user_idnum
25426314468
27688711856
1092519173
231068370
1790748161
1120817271
4217966582
1314766523
646596132
2695796045
3566535875
3054535663
2642055473
3629785402
…

分析不同时间的消费趋势

select time_stamp,count(item_id) num
from user_log
group by time_stamp
cluster by time_stamp;

结果：

time_stamp num
NULL 0   #导入数据时首行标题数据导致的
512 20704
520 225198
528 214501
608 179133
616 179872
624 220395
704 163427
712 154343
720 131491
728 124547
…

注意：目前的时间戳不是完整的，如果是完整，还要考虑将时间戳进行转化。比如要分析按天分析趋势，需要将时间戳转换为年月日；要按小时分析趋势，需要将时间戳转换为年月日时。

扩展

按天分析消费数量的趋势

select time_stamp,count(item_id) num
from user_log
where time_stamp is not null
group by
from_unixtime(cast(time_stamp/1000 as
bigint),'yyyy-MM-dd')
cluster by time_stamp;

按天分析消费数量的趋势，指定的时间段（比如指定2025年3月30日到2025年6月30日）

select from_unixtime(cast(time_stamp/1000
as bigint),'yyyy-MM-dd'),count(item_id)
num
from user_log
where time_stamp is not null
and time_stamp >= unix_timestamp('2025-03-
30','yyyy-MM-dd')*1000
and time_stamp < unix_timestamp('2025-06-
30','yyyy-MM-dd')*1000
group by
from_unixtime(cast(time_stamp/1000 as
bigint),'yyyy-MM-dd')
cluster by time_stamp;

查询回购率排名前10的品牌

–计算每个用户对于每个品牌的购买次数
with user_brand_buy_count as(
select user_id,count(*) buy_times,brand_id
from user_log
where user_id is not null and brand_id is not null
group by user_id,brand_id
),

–计算每个品牌的总购买人数和复购人数
brand_repurchase as(
select brand_id,
count(distinct user_id) as total_buy_users,
sum(case when buy_times >= 2 then 1 else 0 end) repurchase_users
from user_brand_buy_count
group by brand_id
having total_buy_users>0
)

–计算复购率并排序，按复购率降序排名，如果顺序相同则按总购买人数降序
–整数/整数会得到整数，这里需要将其中一个数转化为浮点数：repurchase*1.0
select * from
(select brand_id,total_buy_users,repurchase_users,
round(repurchase_users*1.0/total_buy_users,4) repurchase_rate
from brand_repurchase
order by repurchase_rate desc,total_buy_users desc
limit 10
) as brand_repurchase_rank;

如果报错请参考：Hive执行复杂查询报错-CSDN博客

结果：

rank.brand_idrank.total_buy_usersrank.repurchase_usersrank.repurchase_rate
1034551.0000
8272441.0000
4319331.0000
6252221.0000
6150221.0000
4697221.0000
8362221.0000
1748221.0000
2189221.0000
6691221.0000

分析网购行为与性别关系

这里分析性别与网购总数量，人均购买数量的关系

注意：在 Hive 中，distribute by/sort by 只能引用 SELECT子句中已定义的列名，比如这里智能用gender,不能用ui.gender。

select
case ui.gender
when 0 then '女性'
when 1 then '男性'
when 2 then '保密'
else '未知'
end as gender,
count(ul.item_id) num,
round(count(ul.item_id)*1.0/count(distinct ul.user_id),2) avg_num
from user_log ul
join user_info ui
on ul.user_id = ui.id
where item_id is not null
group by ui.gender
distribute by gender
sort by num desc;

如果报错请参考：Hive执行复杂查询报错-CSDN博客

结果：

gendernumavg_num
女性40313178141.13
男性1213553099.74
保密2055127197.12
未知42149565.49

分析网购行为与年龄关系

select ui.age_range age_range,
count(item_id) num,
round(count(item_id)*1.0/count(distinct user_id),2) avg_num
from user_info ui
join user_log ul
on ui.id = ul.user_id
where ul.user_id is not null
group by ui.age_range
distribute by age_range
sort by age_range;

结果

age_rangenumavg_num
NULL12822357.84
09931162106.89
1172171.71
25385020101.85
314848637132.99
411802052147.54
56200000152.05
65413716152.65
71052265150.50
8162534128.38

分析每个品牌的销量前3名的商品

select brand_id,item_id,num,rn
from
(select brand_id,item_id,count(*) num,
row_number() over(partition by brand_id order by count(item_id) desc) rn
from user_log
where user_id is not null and brand_id is not null
group by brand_id,item_id
distribute by brand_id
sort by brand_id,item_id) tem
where rn<=3
cluster by brand_id,rn;

结果：这里只截取末尾的数据做展示，标题行分别是品牌id，产品id，销量，排名

8475103981231
8475977504202
847575280883
84763886019161
847687189014272
847610547933
8477852870491
8477201803462
8477179560403