建立表格
create table `taobao`.`taobao_data` (
`user_id` varchar(255) ,
`item_id` varchar(255) ,
`behavior_type` varchar(255) ,
`user_geohash` varchar(255) ,
`item_category` varchar(255) ,
`date` varchar(10) ,
`hour` varchar(3)
)
clustered by(user_id) into 3 buckets
row format delimited fields terminated by ','
stored as orc tblproperties('transactional'='true');
创建导出数据表
create table `taobao`.`taobao_result` (
`key` varchar(255) ,
`value` varchar(255)) ;
4.导入数据
先启动hive --service metastore -p 9083
(这个端口号要配置到flume文件中,可用netstat -tulpn | grep 9083查看端口是否监听)
hive --service metastore -p 9083
再去启动flume
flume-ng agent --conf conf --conf-file file_hive.properties -name agent3 -Dflume.hadoop.logger=INFO,console
然后把文件数据导入到,之前创建的data文件夹里面就完成了自动导入
mv /home/hadoop/12yue.csv /home/hadoop/taobao/data/
5.数据分析
5.1 把总访问量查询出来,导入到结果表
insert into taobao_result
(select "PV", u. `总访问量` FROM
(select count(\*) AS `总访问量` FROM taobao_data) u);
INSERT INTO taobao_result
(SELECT " UV", u. `用户数量` FROM
(SELECT COUNT(DISTINCT user_id) AS `用户数量` FROM taobao_data) u);
可以查看一下
select \* from taobao_result;
5.2 – 浏览页跳失率:用户仅仅有pv行为,没有其它的收藏、加购、购买行为
INSERT INTO taobao_result
(SELECT "跳失率", u. `总访问量` FROM
(
SELECT b.`仅pv用户` / a.`总用户` AS `总访问量` FROM
(SELECT count( DISTINCT user_id ) AS `总用户` FROM taobao_data) a ,
(SELECT
count( DISTINCT user_id ) AS `仅pv用户`
FROM taobao_data
WHERE
user_id NOT IN ( SELECT DISTINCT user_id FROM taobao_data WHERE behavior_type = '2' ) AND
user_id NOT IN ( SELECT DISTINCT user_id FROM taobao_data WHERE behavior_type = '3' ) AND
user_id NOT IN ( SELECT DISTINCT user_id FROM taobao_data WHERE behavior_type = '4' )) b
) u);
注意在hive里面不可以使用in not in查询,所以这里要用连接查询解决这个问题
经过大量的测试,我自己写了一个sq语句,也可以达到以上的效果
首先把要插入的信息,查询出来
SELECT "跳失率", u.`总访问量` FROM
(
SELECT b.`仅pv用户` / a.`总用户` AS `总访问量` FROM
(SELECT count( DISTINCT user_id ) AS `总用户` FROM taobao_data) a,
(SELECT count( DISTINCT user_id ) AS `仅pv用户` from (select \* from taobao_data) as c LEFT JOIN (SELECT DISTINCT user_id as `id` FROM taobao_data WHERE behavior_type = '2' or behavior_type='3' or behavior_type='4') as d on c.user_id=d.id WHERE d.id is NULL ) as b
) as u;
插入
INSERT INTO taobao_result
(SELECT "跳失率", u.`总访问量` FROM
(
SELECT b.`仅pv用户` / a.`总用户` AS `总访问量` FROM
(SELECT count( DISTINCT user_id ) AS `总用户` FROM taobao_data) a,
(SELECT count( DISTINCT user_id ) AS `仅pv用户` from (select \* from taobao_data) as c LEFT JOIN (SELECT DISTINCT user_id as `id` FROM taobao_data WHERE behavior_type = '2' or behavior_type='3' or behavior_type='4') as d on c.user_id=d.id WHERE d.id is NULL ) as b
) as u);
优化代码类型(思路类似)
SELECT count(DISTINCT user_id) from datas left JOIN
(select DISTINCT user_id as t from datas WHERE behavior_type ='2' or behavior_type ='3' or behavior_type ='4') as a on user_id=a.t WHERE a.t is null
5.3
– 有购买行为的用户数量、用户的购物情况、复购率分别是多少?
SELECT COUNT( 1 ) FROM (SELECT u.user_id, SUM( CASE u.behavior_type WHEN "4" THEN 1 ELSE 0 END ) AS buy FROM taobao_data u GROUP BY u.user_id HAVING buy > 0 ) t;
SELECT COUNT(1) AS `总数`, SUM(CASE u.behavior_type WHEN "1" THEN 1 ELSE 0 END ) AS `点击行为`,SUM(CASE u.behavior_type WHEN "2" THEN 1 ELSE 0 END ) AS `收藏行为`,SUM(CASE u.behavior_type WHEN "3" THEN 1 ELSE 0 END ) AS `加购物车行为`,SUM(CASE u.behavior_type WHEN "4" THEN 1 ELSE 0 END ) AS `购买行为` FROM taobao_data u;
5.4 – 复购率 购买次数大于2的人占总的人数的比率
必须要输入下面代码
set hive.mapred.mode=nonstrict;
注意这个sq语句里面的4,必须也要用单引号括起来
SELECT t2.repeat_buy/t1.total AS `复购率` FROM
(SELECT COUNT(DISTINCT u1.user_id) AS total FROM taobao_data u1) t1 ,
(SELECT COUNT(1) AS repeat_buy FROM
(SELECT u.user_id, SUM(CASE u.behavior_type WHEN '4' THEN 1 ELSE 0 END ) AS buy FROM taobao_data u GROUP BY u.user_id HAVING buy>1) t) t2;
5.5 分析用户在哪个时间段最为活跃,包括日期和时间
SELECT `date`,count(\*) as `t` from taobao_data GROUP BY `date` ORDER BY `t` DESC;
SELECT `hour`,count(\*) as `t` from taobao_data GROUP BY `hour` ORDER BY `t` DESC;
导出数据
去自己的Navicat里面执行去试试也可以
CREATE DATABASE taobao;
create table `taobao`.`taobao_result` (
`key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
虚拟机执行也可以
show create table taobao_result
在终端界面运行
sqoop export --connect jdbc:mysql://localhost:3306/taobao --username root -P --table taobao_result --export-dir /user/hive/warehouse/taobao.db/taobao_result -m 1 --input-fields-terminated-by '\001'
查询一下数据是否导入成功
OK!
可视化展示——基于Python里面的pyecharts库
1.有不同的用户访问,按照IP地址来确定,对比之后用户点击率还是比较高的,和用户人数形成了极差,说明该电商还是比较吸引人,有大量的浏览量,说明网站还是比较吸引人,流量价值比较高,可以加大对广告的投入赚取利益。
2.只看不买不收藏的用户占比总人数约1/3,说明还是比较可观,可以加大对网站信息化的建设以及,吸引更多的人,同时减少跳失率
3.通过对用户的购买行为数据分析可知,用户主要对其内容感兴趣,访问量比较的大说明网站的种类还是比较的丰富,用户喜欢逛,但综合下来购买占比相对较少,但网站的流量比较大,可以发挥该优势,提高用户的收藏率和购买率,以及加入购物车行为,这就需要增加产品的质量,吸引更多的人愿意一次性购买(淘宝的特点,都喜欢逛)
有图可知,复购率约占比4/25,说明二次购买的人数还是不够多,需要加强质量管理,同时增加一些二次购买福利,留住顾客
分析可得用户喜欢在活动前后大幅度浏览网站,同时大部分人喜欢在6点(下班之后)浏览网页,到了晚上9点和10点带到高峰,建议在这段时间加强对网站的维护和广告的投入,达到相关的作用
项目总结
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
对网站的维护和广告的投入,达到相关的作用
项目总结
[外链图片转存中…(img-aAGeLGBS-1714523588635)]
[外链图片转存中…(img-t3rdXrSv-1714523588636)]
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!