MySQL+Excel数据分析项目：淘宝母婴购物分析

项目简介：

随着互联网的发展和人口日益增长，人们对于母婴产品的需求不断增加，淘宝作为中国最大的电子商务平台之一，在母婴商品领域拥有大量的用户和丰富的购物数据。深入分析这些数据可以帮助商家了解用户行为和需求，优化产品和服务，提升用户体验和市场竞争力。

1.分析目的

通过数据分析和可视化展示，充分挖掘数据的价值，让数据更好地为业务服务，可作如下分析：
销量分析：年/季度/月/日的商品销量关系
产品分析：商品销量和种类的关系
用户分析：不同用户的购买行为关系

2.数据获取

数据集来自：淘宝母婴购物数据可视化分析_数据集-阿里云天池 (aliyun.com)，得到两个表且包含数据说明：
1、 用户基本信息表： tianchi_mum_baby

字段	字段说明	说明
user_id	用户标识	抽样&字段脱敏
birthday	婴儿出生日期	由user_id填写，有可能不真实,格式:YYYYMMDD
gender	婴儿性别（0 男孩，1 女孩，2性别不明）	由user_id填写，有可能不真实

2、商品交易信息表： tianchi_mum_baby_trade_history

字段	字段说明	说明
user_id	用户标识
auction_id	交易ID
category_1	商品一级类目ID
category_1	商品二级类目ID
buy_amount	购买数量
auction_id	订单发生日期	格式：YYYYMMDD

3.数据处理

首先将得到的两个表上传
创建数据库且使用该数据库：

#创建数据库taobao
create database taobao;
use taobao;

然后根据获取到的数据说明建立两个表：

#建表
create table sam_tianchi_mum_baby
(
user_id bigint,
birthday varchar(255),
gender bigint
);
create table sam_tianchi_mum_baby_trade_history
(
user_id bigint,
auction_id bigint,
category_2 bigint,
category_1 bigint,
buy_mount int,
tra_day varchar(255)
);

 建好表后导入数据集：

#导入数据集
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/tianchi_mum_baby.csv'
into table sam_tianchi_mum_baby
fields terminated by ','
lines terminated by '\n'
ignore 1 lines;
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/tianchi_mum_baby_trade_history.csv'
into table sam_tianchi_mum_baby_trade_history
fields terminated by ','
lines terminated by '\n'
ignore 1 lines;

此处 ignore 1 lines 是忽略csv中第一行，因为第一行不是数据而是表头.
（PS：如果此处运行代码发现sam_tianchi_mum_baby_trade_history表导入不进去，查看Response:Error Code: 1264. Out of range value for column 'XXX' at row 1 是因为定义表时数据类型给小了(给小了是因为避免占用内存过多)，此时只需修改表的数据类型到满足大小即可）

导入成功后，初步审视导入的表数据是否正常：




观察发现数据导入正常，此时查看数据量级：

#查看条目数量
select count(*) as trade_num from sam_tianchi_mum_baby_trade_history;
select count(*) as baby_num from sam_tianchi_mum_baby;
select count(distinct category_2) as trade_num from
 sam_tianchi_mum_baby_trade_history;
select count(distinct category_1) as trade_num from
 sam_tianchi_mum_baby_trade_history;

得到 sam_tianchi_mum_baby 有953条，sam_tianchi_mum_baby_trade_history 有29971 条数据.
category_2有662项，category_1有6项
此时按照数据清洗的步骤进行：

①选择子集：
审视已有的数据，按需保留数据列，因为总数目比较少，所以此次只分析二级类目，所以category_1 列可删除.

#删除多余列
alter table sam_tianchi_mum_baby_trade_history drop column category_1;

②列名重命名：
处理需要重命名的列名，此表列名正常，不需要重命名.

③删除重复值：
查找重复值发现无重复值：

#查找重复值
select user_id,user_num 
from(
	select user_id, count(user_id) as user_num from sam_tianchi_mum_baby
	group by user_id) as tb_count_baby
where user_num>1;

④处理缺失值：
查找缺失值发现无缺失值：

#缺失值检查
select count(user_id) from sam_tianchi_mum_baby_trade_history where user_id is null;
select count(auction_id) from sam_tianchi_mum_baby_trade_history where auction_id is null;
select count(category_2) from sam_tianchi_mum_baby_trade_history where category_2 is null;
select count(buy_mount) from sam_tianchi_mum_baby_trade_history where buy_mount is null;
select count(tra_day) from sam_tianchi_mum_baby_trade_history where tra_day is null;
select count(user_id) from sam_tianchi_mum_baby_trade_history where user_id is null;
select count(birthday) from sam_tianchi_mum_baby_trade_history where birthday is null;
select count(gender) from sam_tianchi_mum_baby_trade_history where gender is null;

⑤一致化处理：
观察发现tra_day 列和 birthday 列为日期，且格式规范，但定义为 varchar(255) 型，需改为 date 型以便后续分析：

#一致化处理
alter table sam_tianchi_mum_baby_trade_history
 modify column tra_day date;
alter table sam_tianchi_mum_baby
 modify column birthday date;

⑥异常值处理：

需删除异常性别：

#异常值处理
delete from sam_tianchi_mum_baby 
where gender not in (0,1);
#不能执行是因为MySql运行在safe-updates模式下，
#该模式会导致非主键条件下无法执行update或者delete命令
#查看以及关闭该模式即可
show variables like 'SQL_SAFE_UPDATES';
SET SQL_SAFE_UPDATES = 0;

此时得到干净数据：

4.数据分析
（一般情况可以将在数据库中得到的干净数据导入到Excel进行数据分析，会更加方便，但此项目作为练习项目练习在数据库中使用SQL语句分析）

先审视数据范围，以便进一步解析时间与销量关系

销量分析：年/季度/月/日的商品销量关系

每年​​​​​​销量为：

得到年销量值，排除开始年和末尾年的不完整，可以得到年销量是逐年上升的

年季度销量为：

从季度销量可以看出每年具体哪个季度卖的最好

每年月销量为：

每年最高销量月份为：

得到每年最高销量月份，可以是初步业务规划的目标

每年每月日销量总计：

每年日销量前二：

产品分析：商品销量和种类的关系

每个产品的销量以及排名：

排名是为了取数方便，且对有并列的也可同时取到.

用户分析：不同用户的购买行为关系
针对用户可以对用户的性别和年龄维度分析销售占比，本次只分析性别维度

先查看此次用户总数男女分别有多少，得到男生489，女生438：

#对用户表中男女计数
select count(gender) as 男 from sam_tianchi_mum_baby where gender=0;
select count(gender) as 女 from sam_tianchi_mum_baby where gender=1;

对男女分别购买频次和总数计数

对男女分别的商品类目购买总数计数

#男
select category_2 as 商品类目,sum(buy_mount) as 男生购买总数
from(select 
    user_id,auction_id,category_2,buy_mount,tra_day,gender
    from sam_tianchi_mum_baby_trade_history as t
    join (select user_id as b_id,gender from sam_tianchi_mum_baby) as b
    on t.user_id=b_id) as tb_connect
where gender=0
group by category_2
order by sum(buy_mount) desc;
#女
select category_2 as 商品类目,sum(buy_mount) as 女生购买总数
from(select 
    user_id,auction_id,category_2,buy_mount,tra_day,gender
    from sam_tianchi_mum_baby_trade_history as t
    join (select user_id as b_id,gender from sam_tianchi_mum_baby) as b
    on t.user_id=b_id) as tb_connect
where gender=1
group by category_2
order by sum(buy_mount) desc;

 

此时分析完成，只需要将结果呈现(可视化)以及结果应用(输出文档等)即可完成

4.数据可视化
将MySQL中经过数据处理的干净数据连接到Excel(或其他可视化软件:power bi/tableau等)做出可视化图表并呈现到结果应用即可

创建Excel，数据 - 获取数据 - 选择从MySQL数据库

然后输入数据库用户名和密码，选择 database:taobao
之后出现导航器，点加载就直接导入该表

点转换数据就会打开Power Query

此处点击转换数据，打开Power Query后检查数据格式是否有误，检查完成后点击 关闭并上载 导入即可

导入后针对分析目的对数据进行可视化处理，包括制作报表，可视化图表等，此次只做Excel从MySQL提取数据.

5.结果应用
结合实际业务将分析得到的结果输出为文档等，此处简单输出结果
销量分析：
- 根据现有数据得到每年销量在逐步上升，表明销售情况趋势良好.
- 根据季度销量得到每年的第四个季度销量最高，可以结合实际业务情况重点运营该时段.
- 根据每年最高销量月份得到集中在11，12月，为淘宝双十一双十二活动期间，可以得出该活动实- - 际提高了销量，也可继续优化该活动提高销量.

产品分析
- 根据产品销量排名可得到产品50018831的销量是其他产品的三倍以上，可针对该产品进行优化提高销量，如对该产品的价格和质量等再优化.

用户分析
- 根据男女分别购买频次和总数计数可以得到男生购买的总数比女生多了接近一半.
- 根据男女分别的商品类目购买总数计数得到男生最喜好的商品为50018831，且该商品购买量远超其他商品，女生的为50013636，且购买的商品种类比较平均，可以结合实际业务进行针对性优化.

（PS：本文由个人总结，有任何问题欢迎指出）