MySQL对口红销售数据分析
近期从公众号「猴子聊人物」后台资料处获得一份口红销售数据，想从几个方面通过分析口红销售情况，以了解人们对口红的需求。
一、研究问题
1.销售量前10%的口红在评分、功效上有什么特点？
2.售价对销售量的影响如何？
3.对于国内外口红的需求情况有什么偏好？
二、数据清洗
打开Excel表格，删除重复项，指定数据类型，删除暂时不用的商品描述，另外价格处有显示为「￥19.00～￥55.00」的进行分列处理。因为MySQL中列名如果是中文不太方便，故在Excel中更改列名，最终列名对应中文如下图。
接下来在MySQL中创建资料库lipstick并导入数据。
首先对于店名为空的数据可以删除，另外查询出不存在描述分、价格分、质量分、服务分为空值的数据。同时，评论数和销售量为空的数据更新为0。
select * from lipstick where `name` = ;
DELETE from lipstick where `name` = ;
UPDATE lipstick set num_evaluate = 0 where num_evaluate = ;
UPDATE lipstick set num_sold = 0 where num_sold = ;
其余数据还存在空值，为保护数据的真实性不再做修改。
三、MySQL分析
1.销售量前10%的口红在评分、功效上有什么特点？
- 评分
数据量共1657条，我们选取销售量前165条数据，并统计其各项评分的最高分和平均分。另外选取所有数据各项评分的最高分、最低分、平均分作为参考对比。代码如下：
SELECT round(MAX(score_dis),2), round(AVG(score_dis),2),
round(MAX(score_price),2), round(AVG(score_price),2),
round(MAX(score_quality),2), round(AVG(score_quality),2),
round(MAX(score_service),2), round(AVG(score_service),2) FROM
(SELECT * FROM lipstick ORDER BY num_sold DESC LIMIT 165) a;
SELECT ROUND(MAX(score_dis),2), ROUND(MAX(score_price),2),
ROUND(MAX(score_quality),2), ROUND(MAX(score_service),2)
FROM lipstick;
SELECT ROUND(MIN(score_dis),2), ROUND(MIN(score_price),2),
ROUND(MIN(score_quality),2), ROUND(MIN(score_service),2)
FROM lipstick;
SELECT ROUND(AVG(score_dis),2), ROUND(AVG(score_price),2),
ROUND(AVG(score_quality),2), ROUND(AVG(score_service),2)
FROM lipstick;
对于评分结果分析如下 ：
销售量前10%的口红各项评分平均为4.5左右，除服务分最高分为4.8外，其余各项评分最高分均为4.7。
从所有数据中得到的各项评分最高分除服务分为4.85外，其余各项评分最高为4.7。最低评分都没有低于4分，平均分值比销量前10%的平均分值要高。
根据以上数据分析，各项评分最高分者有进入前10%，服务分最高的口红销量却没有进入前10%，总体平均评分高于销量前10%的口红平均评分，可以看出销量高评分都很高，不过评分并没有绝对的影响销量情况，说明消费者对口红的选择上还有别的关注点。
- 功效
数据量共1657条，我们选取销售量前165条数据，大致选取几个口红功效描述词，统计其中出现频次。代码如下：
SELECT skin, effect, sunscreen, crowd FROM
(SELECT * FROM lipstick ORDER BY num_sold DESC LIMIT 165) a;
SELECT
COUNT(CASE WHEN effect LIKE %易上色% THEN 1 END) as 易上色,
COUNT(CASE WHEN effect LIKE %防脱妆% THEN 1 END) as 防脱妆,
COUNT(CASE WHEN effect LIKE %遮瑕% THEN 1 END) as 遮瑕,
COUNT(CASE WHEN effect LIKE %保湿% THEN 1 END) as 保湿,
COUNT(CASE WHEN effect LIKE %滋润% THEN 1 END) as 滋润,
COUNT(CASE WHEN effect LIKE %补水% THEN 1 END) as 补水,
COUNT(CASE WHEN effect LIKE %不掉色% THEN 1 END) as 不掉色,
COUNT(CASE WHEN effect LIKE %持久% THEN 1 END) as 持久,
COUNT(CASE WHEN sunscreen LIKE %是% THEN 1 END) as 防晒
FROM
(SELECT * FROM lipstick ORDER BY num_sold DESC LIMIT 165) a;
SELECT crowd, COUNT(crowd) FROM (SELECT * FROM lipstick ORDER BY num_sold DESC LIMIT 165) a
GROUP BY crowd;
对于功效结果分析如下：
对于销量前10%的口红来说，其中集中描述词和出现频率如下图：
如果我们认为防脱妆=不掉色=持久，保湿=滋润=补水，那么重新进行整理得到：易上色 65 39.39%；防脱妆 13 7.88%；保湿 60 36.36%；遮瑕 1 0.61%；防晒 9 5.45%。其中数字代表绝对量，百分数表示在销售前10%中的比例。
可以看出口红功效需求上，对于易上色和保湿的需求比较多，对于防晒、遮瑕、防脱妆的需求没有特别多。
毕竟，对于口红来说，防晒不是最重要的功能；而遮瑕也有遮瑕棒等更专业的化妆品替代；口红本来就是比较容易脱妆的，毕竟吃东西是难免不脱妆的，大部分人会选择随时补妆，所以没有强求不脱妆。
但是显色是对于口红的基本要求，所以易上色的需求较高，另外对于皮肤的保护也是人们选择化妆品时必然会考虑的。
最后一个是关于适用人群的统计，其中大部分是没有特别说明，除此之外是通用和女用，男士专用的只有一个。
2.售价对销售量的影响如何？
这里查询了售价的最高值和最低值，以确认一个价格的区间。同时通过大致浏览价格水平，可以确定制作直方图的区间长度。代码如下：
SELECT price, num_sold FROM lipstick;
SELECT max(price), min(price) FROM lipstick;
SELECT count(CASE WHEN price <= 20 THEN 1 END) as a1,
count(CASE WHEN price <= 40 and price > 20 THEN 1 END) as a2,
count(CASE WHEN price <= 60 and price > 40 THEN 1 END) as a3,
count(CASE WHEN price <= 80 and price > 60 THEN 1 END) as a4,
count(CASE WHEN price <= 100 and price > 80 THEN 1 END) as a5,
count(CASE WHEN price > 100 THEN 1 END) as a6
FROM lipstick;
对于售价和销量的关系，我们可以看到40到60的价格水平销量最多，其次是60-80的水平，较少的是80到100的价格水平。这些口红的主要属于平价口红，可以看出口红的价格在60以下为一个阶段，在60-80为一个阶段，当口红价格超过80时，消费者很可能会选择100以上质量更好一点的口红。
有了这个猜测，我们选取100元以上的口红再进行细分讨论。代码如下：
SELECT price FROM lipstick WHERE price > 100;
SELECT count(CASE WHEN price <=200 THEN 1 END) as b1,
count(CASE WHEN price <=300 AND price >200 THEN 1 END) as b2,
count(CASE WHEN price <=400 AND price >300 THEN 1 END) as b3,
count(CASE WHEN price <=500 AND price >400 THEN 1 END) as b4,
count(CASE WHEN price >600 THEN 1 END) as b5
FROM
(SELECT price FROM lipstick WHERE price > 100) b;
从上图中可以看出，100-300元的口红销量在100元以上的口红销量中比较多，而大于300元的口红销量骤减，很可能是因为大于300元的口红已经接近于专柜价格，人们更倾向于直接去专柜试色购买而非网上购买。
3.对于国内外口红的需求情况有什么偏好？
我们考虑对哪些国家的口红需求较多，这些国家的口红都是在什么价位的。
SELECT nation, count(nation) as c FROM lipstick GROUP BY nation ORDER BY c DESC;
SELECT nation, price, rank() over(PARTITION BY nation ORDER BY price DESC)
from lipstick WHERE nation in (韩国,法国,泰国);
SELECT nation, ROUND(AVG(price),2) FROM lipstick WHERE nation in (韩国,法国,泰国) GROUP BY nation;
其中，除了国内销量最高以外，对于国外的口红销量最高的前三名为法国，韩国和泰国。
法国的口红最贵的是625.98，最便宜的是22.5元；韩国口红最贵的是999有4条记录，最便宜的是9.9；泰国口红最贵的是240，最便宜的是24.5元。平均来看，法国口红均价为237.06元，韩国均价为66.12元，泰国均价为40.96元。
这里我们看到法国口红的均价普遍较贵，韩国的最高价口红999元的销量也有4个，那么我们猜测，是否国外的口红普遍较贵，这里我们选取了价格排名前10%的口红，按国家汇总，代码如下：
SELECT nation, count(nation) FROM
(SELECT price, nation FROM lipstick ORDER BY price DESC LIMIT 165) a
GROUP BY nation ORDER BY count(nation) DESC;
结果中可以看到，在价格最贵的前10%的口红中，法国、韩国、日本的数量排名前三，法国是著名的奢侈化妆品大国，韩国和日本的化妆品也早已享有盛誉，所以高价位的口红消费者都会倾向于选择这三个国家而非中国本土的口红。
四、结论
通过探究以上三个问题，我们可以得出以下结论：
1.销量靠前的口红在评分上也是高的，但是评分并没有完全决定了销量水平，大部分消费者更在意的口红功效为易上色和保湿。目前口红的受众还是以女性或者男女通用为主，专门的男士口红还是很少，几乎没有。
2.40到80元口红的销量较高，而超过100元的口红中消费者更青睐300以下的口红，因为300以上的口红完全可以通过专柜购买更方便。
3.对于国外口红的偏好上，消费者更青睐法国、韩国和泰国的口红，其中法国、韩国、日本的口红价格普遍偏贵。法国是奢侈化妆品大国，韩国和日本的化妆品也享有盛誉。泰国的化妆品价格更为平价。
五、建议
对于如果想提高口红销量，口红产品需要注意：
1.在产品价格上，消费者普遍喜欢平价口红。100到300元的口红是提高一个价位阶层的首选项。
2.在功能上必备的功能是易上色和保湿，同时，防脱妆可以作为加分项。
3.在进口方面，高价位的口红可以选择法国，平价的可以选择泰国，如果想提高一点价格，可以选择韩国和日本的口红。
推荐阅读：
