SQL项目实战练习：淘宝用户行为数据分析实战

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

3.2 数据层级的漏斗转化（基于漏斗分析）