1、数据的简介:天池淘宝用户数据
2、数据导入和清洗
这次使用的SQL界面工具-SQL workbench
2.1 导入数据
create table userbehavior(
userID int,
itemID int,
categoryID int,
bahaviortype text,
timestamp int
);
load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/UserBehavior.csv"
into table userbehavior
fields terminated by ','
lines terminated by '\n';
我们看一下这个数据大概的样子:
select * from userbehavior limit 10;
2.2 数据清洗
根据字段内容进行分析后,将UserID,ItemID,TimeStamp设置成联合主键,经验证,不存在数据重复。
select UserID,ItemID,TimeStamp from userbehavior
GROUP BY UserID,ItemID,TimeStamp having count(1)>1;
2.3 查看数据的缺失值
select count(userID), count(itemID), count(categoryID), count(bahaviortype), count(timesstamp)
from userbehavior;
这里数据实在太大,达到了100150807,为了快速的完成,这里只采用了500000
2.4 数据的时间处理
这里的数据是一个时间戳,我们需要将这个数据转换到我们日常常用的时间格式
create table usertest2(
select * from userbehavior limit 500000
);
# 时间进行转换到日常格式
UPDATE usertest set datee = FROM_UNIXTIME(timesstamp,'%Y-%m-%d'), Timee = FROM_UNIXTIME(timesstamp,'%h');
#增加两个列
alter table userbehavior change Date datee date;
alter table userbehavior change Timee Timee varchar(10);
2.5 异常值处理
select max(datee), min(datee) from usertest;
这里的时间是2017-12-03年到2017-09-11的之间的数据
3、数据分析
3.1 这里先看一下用户行为的分布
create view user_p as
select userID, itemID,
sum(case when bahaviortype = 'pv' then 1 else 0 end) as click,
sum(case when bahaviortype = 'fav' then 1 else 0 end) as favor,
sum(case when bahaviortype = 'buy' then 1 else 0 end) as buy,
sum(case when bahaviortype = 'cart' then 1 else 0 end) as buycar from usertest group by userID, itemID
;
SELECT * FROM day01.user_p;
这里是创建了一个视图,方便数据的简化和操作,关于视图的知识可以看下面两篇文章:
视图1:https://www.w3school.com.cn/sql/sql_view.asp
SQL视图的作用:https://blog.csdn.net/weixin_34037515/article/details/92609031