网站用户行为分析

1.1 本地数据集上传到数据仓库Hive

1.2 Hive数据分析

1.3 Hive、MySQL、HBase数据互导

2.1 数据准备

第一步，通过samba服务共享文件

第二步，数据下载预处理

第三步，将数据上传至hdfs

第四步，在hive上创建数据库和表

第五步，查看创建表的数据类型与信息

2.2 Hive数据分析

（1）用户行为分析需求：2014-12-11~12号有多少条购买商品的记录

分析步骤

语句：select count(*) from bigdata_user where visit_date >\’2014-12-10\’ and visit_date <\’2014-12-13\’ and behavior_type=\’4\’ limit 10;
结果截图：运行或存为表格后的查询显示
（2）用户行为分析需求：分析每月1-31号购买情况
- 语句：select day(visit_date) from bigdata_user limit 10;
- 行为日期

按日期统计记录数、用户数
语句：select count(distinct uid),day(visit_date) from bigdata_user where behavior_type=\’4\’ group by day(visit_date) limit 10;

语句：select count(*),day(visit_date) from bigdata_user where behavior_type=\’4\’ group by day(visit_date) limit 10；

保存为表格
语句：create table day_count as select count(*),day(visit_date) from bigdata_user where behavior_type=\’4\’ group by day(visit_date);

语句：create table day_uid as select count(distinct uid),day(visit_date) from bigdata_user where behavior_type=\’4\’ group by day(visit_date);

12号+购买行为
语句：select * from bigdata_user where behavior_type=\’4\’and visit_date=\’2014-12-12\’ limit 10;

按用户编号分组
语句：select uid from bigdata_user where behavior_type=\’4\’and visit_date=\’2014-12-12\’ group by uid limit 10;

按用户分组统计
语句：select uid,count(*) from bigdata_user where behavior_type=\’4\’and visit_date=\’2014-12-12\’ group by uid limit 10;
- 12号，购买，4项以上
- 语句：select uid,count(*) from bigdata_user where behavior_type=\’4\’and visit_date=\’2014-12-12\’ group by uid having count(*)>4 limit 10;

语句：select uid,count(*) from bigdata_user where behavior_type=\’4\’and visit_date=\’2014-12-12\’ group by uid having count(behavior_type=\’4\’)>4 limit 10;

2014-12-12号当天广东购买商品数
语句：select count（*）from bigdata_user where visit_date=\’2014-12-12\’ and province=\’广东\’;

2014-12-12号当天的商品购买与浏览比例
语句：select c.*,c.c4/c.c1 c41 from (select uid,count(*)countall, sum(case when behavior_type=\’4\’ then 1 else 0 end)c4, sum(case when behavior_type=\’1\’ then 1 else 0 end)c1 from bigdata_user where visit_date=\’2014-12-12\’ group by uid)c order by c41 desc limit 10;

用户10001082在2014-12-12号当天活跃度：该用户点击行为占该天所有点击行为的比例
语句:
select \’10001082\’ uid,a.users,b.user_number,b.user_number/a.users rate from ( select count(*) users from bigdata_user where visit_date=\’2014-12-12\’ and behavior_type=\’1\’ ) a ,( select count(*) user_number from bigdata_user where visit_date=\’2014-12-12\’ and behavior_type=\’1\’ and uid=10001082 ) b;

2014-12-12号当天购买4件商品以上的用户
语句：select uid,count（*）from bigdata_user where behavior_type=\’4\’ and visit_date=\’2014-12-12\’ group by uid having count(*)>4;