u8bom展开到采购件sql_利用SQL进行数据分析-以美国黑五数据集为例

之前用Excel对美国黑屋数据集进行了数据分析，Excel对于10万条以下的数据非常易于操作，但是当数据集超过10万条以上，Excel就不能满足我们数据处理的速度和要求，因此数据分析需要用sql语句进行分析，SQL基本操作也是数据分析师必须掌握的一项基础技能。以下，我们就仍然以美国黑五数据集为例，运用SQL语句进行数据分析。

一、项目背景

黑五类似于中国的双11，是美国厂家发起的年底之前的最大规模的促销打折活动。美国圣诞节大采购一般是从感恩节之后开始的。感恩节是每年11月的第四个星期四。因此它的第二天，也就是美国人大采购的第一天。在这一天，美国的商场都会推出大量的打折和优惠活动，以在年底进行最后一次大规模的促销。因为美国的商场一般以红笔记录赤字，以黑笔记录盈利，而感恩节后的这个星期五人们疯狂的抢购使得商场利润大增，因此被商家们称作黑色星期五。商家期望通过以这一天开始的圣诞大采购为这一年获得最多的盈利。

二、数据来源

https://www.kaggle.com/mehdidag/black-friday​www.kaggle.com

三、导入数据到Navicat

导入数据具体流程详见：

猴子：Excel数据导入Mysql常见问题汇总​zhuanlan.zhihu.com

我们新建了一个名叫BlackFriday的表，从下载下来的数据源导入到Navicat中，并添加User_ID和Product_ID为主键。具体信息如下图所示。

四、提出问题

1.消费者群体用户性别差异对购买力的影响

2.消费者群体用户各个年龄段购买力的差异

3.消费者群体用户中婚姻状况是否会对购买力产生影响

4.消费者群体用户中各个职业差异对购买力是否产生影响

5.在各个城市居住年限时长是否会影响消费者购买力

五、理解数据

（1）消费者群体信息

User_ID：用户编码

Product_ID：产品编码

Gender：性别 （M为男性，F为女性）

Age：年龄（0-17，18-25，26-35，36-45,46-50，51-55，55+ 7种）

Occupation：职业（用数字代表具体职业，一共有20种职业）

City_Category：城市分类（分为ABC三类城市）

Stay_In_Current_City_Years：在目前城市的居住的年数 （0，1,2，3,4+，5种）

Marital_Status：婚姻状况 （0代表未婚，1代表已婚）

（2）产品信息

Product_Category_1：产品分类为1，不可为空

Product_Category_2：产品分类为2

Product_Category_3：产品分类为3

（3）消费信息

Purchase：购买金额 （单位为美元）

六、数据清洗

（1）删除重复值

对Product_ID,User_ID查找重复值，输出结果是NULL值，因此没有重复值。

（2）缺失值处理

查找Product_ID,User_ID,Gender,Age和Product_Category_1是否有缺失值，查找结果输出为NULL值，因此不存在缺失值，不需要删除。

七、数据分析

（1）消费者用户整体销售情况

消费者用户整体销售情况主要从消费者数量，销售产品件数以及销售总金额进行分析。

黑五期间，消费者用户整体销售金额超过了50亿，同时有5891位消费者参与消费，销售产品种类3623件。

（2）消费者用户群体特征分析

用户画像主要包括了消费者用户群体的性别、年龄段、职业、城市类别、在当前城市居住的年数、婚姻状况的分布情况。

2.1消费者用户性别分布

从输入结果可以看出，男性消费者用户占据主导地位，是女性消费者用户群体的三倍多，为75.41%，女性消费者用户为24.59%

SELECT Gender '年龄',

CONCAT(ROUND(COUNT(Gender)/(SELECT COUNT(Gender) FROM BlackFriday)*100,2), '%') '百分比'

FROM BlackFriday

GROUP BY Gender

2.2 消费者用户年龄段分布

从输出结果可知，在所有年龄段中，26-35岁之间的消费者用户最多，占比接近40%，其次是36-45岁年龄段，占比20%，超过80%的消费者用户群体在18-45岁之间。

2.3 消费者用户群体职业分布

从输出结果可知，在所有职业中，消费者用户群体占比数量最大的是4，达到13.18%,其次是0，占比12.67%，第三是7，占比10.75%；所有职业中，数量最少的是8，仅仅只有0.28%

2.4 消费者群体城市分布

黑五期间的消费者群体在ABC三个城市的分布是不均匀的，B城市消费者群体占据主体地位，占比为42.13%；C城市消费者群体用户占比30.96%；A城市占比26.91%。

2.5 消费者在当前城市的居住年数分布

黑五期间消费者群体居住年限主要集中在1，占比35.19%，其次是2，占比18.50%，3和4+消费者群体用户的数量大体相同。

2.6 消费者的婚姻状况分布

黑五期间，未婚消费者群体用户在黑五期间消费大于已婚消费者群体，未婚消费者群体占比59.12%，已婚消费者人数占比40.88%。

2.7 总结

1）从输入结果可以看出，男性消费者用户占据主导地位，是女性消费者用户群体的三倍多，为75.41%，女性消费者用户为24.59%

2）在所有年龄段中，26-35岁之间的消费者用户最多，占比接近40%，其次是36-45岁年龄段，占比20%，超过80%的消费者用户群体在18-45岁之间。

3）在所有职业中，消费者用户群体占比数量最大的是4，达到13.18%,其次是0，占比12.67%，第三是7，占比10.75%；所有职业中，数量最少的是8，仅仅只有0.28%

4）黑五期间的消费者群体在ABC三个城市的分布是不均匀的，B城市消费者群体占据主体地位，占比为42.13%；C城市消费者群体用户占比30.96%；A城市占比26.91%。

5）黑五期间消费者群体居住年限主要集中在1，占比35.19%，其次是2，占比18.50%，3和4+消费者群体用户的数量大体相同。

6）黑五期间，未婚消费者群体用户在黑五期间消费大于已婚消费者群体，未婚消费者群体占比59.12%，已婚消费者人数占比40.88%。

(3)产品销售情况分析

黑五期间，产品销售额排名前三

黑五期间销售额排名前三名的一级产品种类是1，5，8，最高的是1，占比37.52%，其次是5和8，分别占比18.47%和16.75%；销售额排名最后三名分别是17，12，13，占比都在0.1%

（4）消费者用户分析

4.1不同性别消费者用户消费情况分析

黑五期间，男性消费者用户群体消费额比女性消费者用户多两倍以上，占据主导地位，为76.79%，女性消费者用户消费额仅占23.21%。

4.2 不同年龄段消费者消费能力分析

黑五期间，消费者用户群体金额主要集中在18-45这个年龄阶段，其中26岁-35岁年龄段消费者群体消费额最多，高达39.85%，其次是36-45岁消费者用户，为20.14%,其次是18-25岁年龄段消费者用户，为17.97%。

4.3不同职业消费者消费能力分析

黑五期间，职业编号为4的消费者用户群体消费能力最高，为13.10%,其次是7，占比10.95%；职业编号为8的消费者用户消费能力最低，仅0.29%。

4.4各个城市定居年限不同的消费者消费能力分析

黑五期间，各个城市定居年限为1的消费者用户群体最多，占比35，14%，0，2，3，4+消费者用户消费能力都相差不多，总体上，各个城市定居年限不影响消费者用户消费能力。

4.5已婚和未婚消费者消费能力分析

在黑五期间，已婚和未婚消费者群体之间的消费能力相差不大，因此婚姻状况并不影响消费者消费能力差异。

4.6 小结

1)黑五期间，男性消费者用户群体消费额比女性消费者用户多两倍以上，占据主导地位，为76.79%，女性消费者用户消费额仅占23.21%。

2)黑五期间，消费者用户群体金额主要集中在18-45这个年龄阶段，其中26岁-35岁年龄段消费者群体消费额最多，高达39.85%，其次是36-45岁消费者用户，为20.14%,其次是18-25岁年龄段消费者用户，为17.97%。

3)黑五期间，职业编号为4的消费者用户群体消费能力最高，为13.10%,其次是7，占比10.95%；职业编号为8的消费者用户消费能力最低，仅0.29%。

4)黑五期间，各个城市定居年限为1的消费者用户群体最多，占比35，14%，0，2，3，4+消费者用户消费能力都相差不多，总体上，各个城市定居年限不影响消费者用户消费能力。

5)在黑五期间，已婚和未婚消费者群体之间的消费能力相差不大，因此婚姻状况并不影响消费者消费能力差异。