sql根据类别拆分上下级_运用SQL对黑五销售数据进行分析

数据来源：https://www.kaggle.com/mehdidag/black-friday

• 提出问题：

1. 有多少名用户在黑五这天购买？
2. 哪个年龄段的人群消费力最高，哪个年龄消费力最低？
3. 不同岁数人群（已婚，未婚）消费力如何？多数购买什么产品？
4. 哪些职业人群消费力高？哪些职业对哪些产品消费力最高？
5. 什么产品的受欢迎程度最高，卖的最多？

• 理解数据

1. 数据来源：KAGGLE
2. 数据意义：黑色星期五的消费者购买数据，可以从中分析出如何优化业务，促销搭配，以及给后续广告投放运营提供思路，希望后续最终能达到给每个用户推送它个人喜欢的想买的东西。
3. 字段意思

User_ID——用户ID

Product_ID——商品ID

Gender——性别

Age——年龄

Occupation——职业

City_Category——城市

Stay_In_Current_City_Years——居住多少年

Marital_Status——是否婚配

Product_Category_1——产品类别1

Product_Category_2——产品类别2

Product_Category_3——产品类别3

Purchase——花费

• 数据导入

从KAGGLE下载的是CSV，但是这回要用到的工具是MYSQL：

直接导入，这里比官方的方法（https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html），要快很多。

• 数据清洗

1. 这里把列名重命名，以方便理解

ALTER TABLE blackfriday CHANGE Purchase 花费 varchar(255)

1. 首先重复值删除，但是这里多半都是一个用户ID购买多个产品，然后进行记录（应当是系统后台自动记录），所以不用清洗。

1. 其次就是缺失值处理，看看哪列是确实的，这里看到作为唯一主键，是唯一标识的用户ID列，为537577。

这里可以看到只有最后两个缺失。

查询语句：

SELECT COUNT(用户ID),COUNT(商品ID),COUNT(性别),COUNT(年龄),COUNT(职业),COUNT(城市),COUNT(居住多少年),COUNT(是否婚配),COUNT(产品类别1),COUNT(产品类别2),COUNT(产品类别3),COUNT(花费) FROM blackfriday

4.这里是产品类别，理应用0填充(单独摘出不影响其他结果)，不过为防止其他类别占用0，这里先查下

这是查询结果：

6 14

14

2

……

左侧为产品类别2，右侧为产品类别3.为节省版面，大部分数据进行隐藏处理。

SQL语句：

SELECT DISTINCT 产品类别2,产品类别3 FROM blackfriday

0未被占用，用0代替NULL。

SQL语句：

UPDATE blackfriday SET 产品类别2 = 0 WHERE 产品类别2 IS NULL

UPDATE blackfriday SET `产品类别3` = 0 WHERE `产品类别3` IS NULL;

筛选NULL要用IS。

• 一致化命名

1. 字段命名以重命名（前面已完成）
2. 数据格式进行统一：

例如0-17这样的，改成

推算最小年龄 推算最大年龄

0 17

步骤1——这里先新建两列,在年龄列后面

SQL语句：

ALTER TABLE blackfriday ADD COLUMN 推算最小年龄 VARCHAR(20) NOT NULL AFTER 年龄

ALTER TABLE blackfriday ADD COLUMN 推算最大年龄 VARCHAR(20) NOT NULL AFTER 推算最小年龄

注：

对SQL尚未炉火纯青时，最好小心为上，执行代码前检查，并且预先备份SQL文件。

并且实际工作中最好不要用汉字命名列名。

步骤2——复制和文本处理

SQL语句：

UPDATE blackfriday SET `推算最小年龄` = (SELECT SUBSTRING_INDEX(`年龄`,'-',1))

可以看到还不完美，有 + 符号，还得接着对列内文字进行处理。

SQL语句：

UPDATE blackfriday SET `推算最小年龄` = REPLACE(`推算最小年龄`,'+','')

OK，在接下来对着推算最大年龄列也是一致化处理操作。

SQL语句：

UPDATE blackfriday SET `推算最大年龄` = (SELECT SUBSTRING_INDEX(`年龄`,'-',-1))

同样，对 + 进行处理

SQL语句：

UPDATE blackfriday SET `推算最大年龄` = REPLACE(`推算最大年龄`,'+','')

OK，还剩最后一步。

步骤3——删除无用列

ALTER TABLE blackfriday DROP 年龄

OK，清洗数据完毕，开始解决问题！

1. 有多少名用户在黑五这天进行购买？

SQL代码：

SELECT COUNT(DISTINCT `用户ID`) FROM blackfriday

一共有5891名用户在黑五进行购买。

1. 哪个年龄段的人群消费力最高，哪个年龄消费力最低？

SQL代码：

SELECT `推算最小年龄`,`推算最大年龄`,SUM(`花费`) FROM blackfriday

GROUP BY `推算最小年龄`,`推算最大年龄`

ORDER BY SUM(`花费`)

可以看到26~35年龄段消费能力最强，猜测是处于事业已经发展起来，所以购买力最强。

其次是36~45中年人，再其次是18~25的年轻人群。

建议后续广告投放细致化分类化，针对购买力最强的三类年龄层次的人进行定制化优惠，广告资源对其进行倾斜，分类对其投放对应年龄段的广告资源。

1. 不同岁数人群（已婚，未婚）消费力如何？多数购买什么产品？

未婚消费数据：

SQL代码：

SELECT `推算最小年龄`,`推算最大年龄`,SUM(`花费`) FROM blackfriday

WHERE `是否婚配` = 0

GROUP BY `推算最小年龄`,`推算最大年龄`

ORDER BY SUM(`花费`)

以婚消费数据：

SQL代码：

SELECT `推算最小年龄`,`推算最大年龄`,SUM(`花费`) FROM blackfriday

WHERE `是否婚配` = 1

GROUP BY `推算最小年龄`,`推算最大年龄`

ORDER BY SUM(`花费`)

左侧为未婚人群消费数据，右侧为已婚，婚姻果然是单身生活的坟墓。

已经结婚的年龄端人群（26~35，36~45，18~25，）消费力远低于未结婚的同年龄段人群。

当然，也有反例。已婚的（46~50，51~55，55+）的年龄人群购买力超过同年龄段未婚人群。

那么，可以对未婚的（26~35，18~25，36~45）年龄人群进行分类广告投放，进行资源倾斜。这类人群购买力比较高。

当然，已婚的（46~50，51~55，55+）的年龄人群也可以对其进行广告资源倾斜，这类购买力比同年龄段的未婚人群高。

0~17岁人群喜好产品分析：

SQL代码：

SELECT `推算最小年龄`,`推算最大年龄`,`商品ID`,COUNT(`商品ID`) FROM blackfriday

WHERE `推算最小年龄` = 0

GROUP BY `推算最小年龄`,`推算最大年龄`,`商品ID`

ORDER BY COUNT(`商品ID`) DESC

0-17消费力偏低，从箱型图来看，大多数商品销量在10以下，可以考虑对销量8以上的货品，根据是否有历史浏览记录搜索记录，进行对这一年龄人群的广告投放。

18~25岁人群喜好产品分析：

SQL代码：

SELECT `推算最小年龄`,`推算最大年龄`,`商品ID`,COUNT(`商品ID`) FROM blackfriday

WHERE `推算最小年龄` = 18

GROUP BY `推算最小年龄`,`推算最大年龄`,`商品ID`

ORDER BY COUNT(`商品ID`) DESC

18-25消费力稍高，从箱型图来看，大多数商品销量在40以下，可以考虑对销量40以上的货品，根据是否有历史浏览记录搜索记录，进行对这一年龄人群的广告投放。

26~25岁人群喜好产品分析：

SQL代码：

SELECT `推算最小年龄`,`推算最大年龄`,`商品ID`,COUNT(`商品ID`) FROM blackfriday

WHERE `推算最小年龄` = 26

GROUP BY `推算最小年龄`,`推算最大年龄`,`商品ID`

ORDER BY COUNT(`商品ID`) DESC

26~35消费力最高，从箱型图来看，大多数商品销量在90以下，可以考虑对销量90以上的货品，根据是否有历史浏览记录搜索记录，进行对这一年龄人群的广告投放。

36~45岁人群喜好产品分析：

SQL代码:

SELECT `推算最小年龄`,`推算最大年龄`,`商品ID`,COUNT(`商品ID`) FROM blackfriday

WHERE `推算最小年龄` = 36

GROUP BY `推算最小年龄`,`推算最大年龄`,`商品ID`

ORDER BY COUNT(`商品ID`) DESC

36~45消费力走低，从箱型图来看，大多数商品销量在40以下，可以考虑对销量40以上的货品，根据是否有历史浏览记录搜索记录，进行对这一年龄人群的广告投放。

55岁以及55岁以上人群喜好产品分析：

36~45消费力走低，从箱型图来看，大多数商品销量在40以下，可以考虑对销量40以上的货品，根据是否有历史浏览记录搜索记录，进行对这一年龄人群的广告投放。

综合分析：

SQL代码：

SELECT

`推算最小年龄`,

`推算最大年龄`,

COUNT(DISTINCT `用户ID`) AS 购买用户数量,

SUM(`花费`) AS 购买用户金额,

SUM(`花费`) / COUNT(DISTINCT `用户ID`) AS 客单价

FROM

blackfriday

GROUP BY

`推算最小年龄`,

`推算最大年龄` -- ORDER BY COUNT(`商品ID`) DESC

从图标中看出，18~25，26~35，36~45的购买用户金额和客单价远高于其他用户，用户数量也是这三个年龄层次段的占据了大多数。

广告资源倾斜以及其他配套方案应着重于这三个年龄端人群。

客单价存疑，应该不会有这么高的客单价。尤其是黑五折扣日。

1. 哪些职业人群消费力高？哪些职业对哪些产品消费力最高？

SQL代码：

SELECT

`职业`,

SUM(`花费`) AS 职业消费金额

FROM

blackfriday

GROUP BY `职业`

ORDER BY SUM(`花费`) DESC

代码为4的职业消费情况最高，其次为0，即没有设置职业的用户。

箱型图来看的话，大部分都在职业都在总消费水平在200000000以下，可以根据客单价情况以及职业人群总消费情况进行分类推送不同客单价的产品。

1. 什么产品的受欢迎程度最高，卖的最多？

SQL代码：

SELECT

`商品ID`,

COUNT(`商品ID`) AS 商品销售件数,

SUM(`花费`) AS 商品销售额,

SUM(`花费`)/COUNT(`商品ID`) AS 商品价格

FROM

blackfriday

GROUP BY `商品ID`

ORDER BY COUNT(`商品ID`) DESC