MySQL案例分析（1）

MySQL 案例分析（1)—销售数据分析

本次案例分析的资源及部分思路来自以下博客添加链接描述，如有冒犯侵权请联系删除，感谢！

（一） 数据导入及主要问题

从上文中下载数据集，分别为如下两个文件：



在导入时存在的问题
本数据集中包含中文字符，第一次导入MySQL数据时频繁失败，经过数次尝试后才成功
（1）数据集的格式如果是CSV（UTF-8），在MySQL中设计表时，中文格式所对应的字段格式要选择UTF８
在接下来右击要新建的表格，选择导入向导，选择对应的CSV文件，在编码的选择栏中选择６５００１（ＵＴＦ－８）才不会导入失败

（２）数据集的格式如果是CSV（逗号分隔）**（注意这两种CSV是不同的类型）**在MySQL中设计表时，中文格式所对应的字段格式要选择ｇｂｋ格式

在接下来右击要新建的表格，选择导入向导，选择对应的CSV文件，在编码的选择栏中选择１０００８（Simplified　Chinese　GB２３１２）才不会导入失败
主要问题
1、统计每个月下单人数
2、统计3月份复购人数所占的百分比
3、统计男女用户的消费频次是否有差异
4、统计多次消费，第一次消费和最后一次消费的间隔是多少
5、统计不同年龄段，用户的消费金额是否有差异（每一单的平均消费金额）
6、统计消费的二八法则：消费top20%的用户贡献了多少额度

（二） 具体操作

（1）统计每个月下单人数
思路
1、“每个月”首先想到 GROUP BY 语句，对月份进行分类；
2、“下单”是限制条件，通过WHERE 语句进行限定；
3、COUNT 聚合函数进行统计
tips
1、MONTH（timestamp) 函数——返回一个整数，表示指定日期值的月份
2、GROUP BY 函数，MySQL升级5.7版本后，本函数前SELECT只能挑选 聚合函数（6个）或者GROUP BY 后的列名

SELECT MONTH(Paidtime) as 月份, COUNT(DISTINCT userId) as 用户数 FROM order_info WHERE isPaid = "已支付" GROUP BY MONTH(Paidtime)

（2）统计3月份复购人数所占的百分比
思路
1、统计3月份复购的人数数量
2、利用第一个操作得出的54799进行比例的计算
tips
1、“复购”的含义就是同一个userId（顾客）有多个Paidtime（购买次数），这里可以利用COUNT函数和WHERE条件进行筛选
2、CONCAT函数——用于将多个字符串连接成一个字符串
具体用法请见一下博客：添加链接描述

#2-统计3月份复购人数的数量
SELECT count(1)
FROM(SELECT count(1) as c, userId
FROM(SELECT userId FROM order_info WHERE MONTH(Paidtime)=3)a GROUP BY userId )b WHERE b.c>1

#统计3月份复购人数的百分比
SELECT concat((d.s/54799)*100,'%') as 三月份复购率
FROM(SELECT count(1) as s
FROM(SELECT count(1) as c, userId
FROM(SELECT userId FROM order_info WHERE MONTH(Paidtime)=3)a GROUP BY userId )b WHERE b.c>1)d

（3）统计男女用户的消费频次是否有差异
思路
1、消费数据储存在order_info_1的表中，性别和生日的数据储存在user_info的表中，考虑使用连表查询（join）
2、“男女用户”的意思其实是“每一种性别”即使用GROUP BY 性别
tips
1、连表查询的关键是清楚之间的联系：即消费者(userId)
2、“平均消费次数”—总消费次数/每个消费者，因此需要COUNT（消费次数)和COUNT(DISTINCT(消费者))
3、判断字段是否是NULL，不能用"="，只能用"is NULL “或"is not NULL”

SELECT u1.sex as 性别, (count(1)/count(DISTINCT(o1.userId))) as 平均消费次数
FROM order_info_1 o1 join user_info u1 on o1.userId=u1.userId and o1.isPaid ='已支付' GROUP BY u1.sex

问题：本次查询结果发现数据集中的问题，性别列中存在NULL值，因此接下来考虑将NULL值去除后进行GROUP BY，查询结果见下

SELECT u1.sex as 性别, (count(1)/count(DISTINCT(o1.userId))) as 平均消费次数
FROM order_info_1 o1 join user_info u1 on o1.userId=u1.userId and o1.isPaid ='已支付' GROUP BY u1.sex HAVING u1.sex is not NULL

（4）统计多次消费，第一次消费和最后一次消费的间隔是多少
思路
1、“多次消费”是前提，首先要挑选出购买次数超过1的消费者
2、找到第一次和最后一次购买的时间
3、利用最后一次减去第一次即可
tips
1、此次查询需要先GROUP BY(消费者)，再对消费者的消费次数进行COUNT，之后需要筛选次数大于1的。GROUP BY后的条件筛选只能利用HAVING
2、第一次”和“最后一次”对时间来说就是"min"和"max"
3、计算时间插值时的函数TIMESTAMPDIFF(interval,datetime1,datetime2)，interval表示计算的单位，返回datetime2-datetime1

SELECT userId,count(1) as 购买次数,min(Paidtime) as 最早时间, max(Paidtime) as 最晚时间, TIMESTAMPDIFF(MONTH,min(Paidtime),max(Paidtime)) as 两次消费的时间差
FROM order_info_1 WHERE isPaid='已支付' GROUP BY userId HAVING 购买次数>1

（5）统计不同年龄段，用户的消费金额是否有差异（每一单的平均消费金额）
思路
1、需要连表查询
2、要划分年龄段，去除一些异常数据
3、GROUP BY(年龄段)，之后AVG聚合函数运算即可
tips
1、本次查询包含多个限定条件（birth不为NULL；isPaid=‘已支付’；birth的范围在1920-2020之间）；因为前面需要连表查询使用JOIN ON；ON后面即可以接条件，此时可以不用再使用WHERE，直接用AND连接即可。
①查看数据集中最大和最小出生年龄

SELECT max(birth) as 最小年龄, MIN(birth) as 最大年龄 FROM user_info WHERE birth is not NULL

根据本次查询结果可以看出数据集存在问题，因此自定义：1920年以前；2020年以后出生的都算数据异常，先进行数据处理

SELECT * FROM user_info WHERE birth IS NOT NULL and '1920-01-01'<birth and birth<'2020-01-01'

②开始按十年一个阶段进行分类
这个感觉思路不太好，使用了CASE WHEN 函数，需要写10次，看起来很冗杂，以后可能会改进，先留个坑吧！

SELECT a.birth,
(case 
WHEN '1920-01-01'<a.birth and a.birth<'1930-01-01' then 100 
WHEN'1930-01-01'<a.birth and a.birth<'1940-01-01' then 90 
WHEN'1940-01-01'<a.birth and a.birth<'1950-01-01' then 80 
WHEN'1950-01-01'<a.birth and a.birth<'1960-01-01' then 70
WHEN'1960-01-01'<a.birth and a.birth<'1970-01-01' then 60 
WHEN'1970-01-01'<a.birth and a.birth<'1980-01-01' then 50 
WHEN'1980-01-01'<a.birth and a.birth<'1990-01-01' then 40 
WHEN'1990-01-01'<a.birth and a.birth<'2000-01-01' then 30 
WHEN'2000-01-01'<a.birth and a.birth<'2010-01-01' then 20
else 10 END) as 年龄
FROM (SELECT * FROM user_info WHERE birth IS NOT NULL and '1920-01-01'<birth and birth<'2020-01-01')a

③将语句嵌套起来进行查询

SELECT b.年龄 as 用户年龄段, avg(b.price) as 平均消费金额
FROM
(SELECT a.i as 用户ID,a.price,a.birth,
(case 
WHEN('1920-01-01'<a.birth and a.birth<'1930-01-01')then 100 
WHEN('1930-01-01'<a.birth and a.birth<'1940-01-01')then 90 
WHEN('1940-01-01'<a.birth and a.birth<'1950-01-01')then 80 
WHEN('1950-01-01'<a.birth and a.birth<'1960-01-01')then 70
WHEN('1960-01-01'<a.birth and a.birth<'1970-01-01')then 60 
WHEN('1970-01-01'<a.birth and a.birth<'1980-01-01')then 50 
WHEN('1980-01-01'<a.birth and a.birth<'1990-01-01')then 40 
WHEN('1990-01-01'<a.birth and a.birth<'2000-01-01')then 30 
WHEN('2000-01-01'<a.birth and a.birth<'2010-01-01')then 20
else 10 END) as 年龄
FROM (SELECT u1.userId as i, birth,price FROM user_info u1 JOIN order_info_1 o1 on u1.userId=o1.userId AND birth IS NOT NULL and isPaid='已支付' and birth>'1920-01-01' AND birth<'2020-01-01')a)b
GROUP BY b.年龄 

select age, avg(sp) from
    (select u.userId,age,sum(price) as sp from order_info_1 as u
  #这个select age 一开始都没看懂；这里其实是：order_info_1 as u JOIN ad 后提取出来的age
    inner join
        (select userId, ceil((year(now())-year(birth))/10) as age from user_info
        where birth > '1920-00-00' AND birth < '2020-00-00') as ad
    on u.userId = ad.userId
    where isPaid = '已支付'
    group by u.userId, age) as n
group by age

（6）统计消费的二八法则：消费top20%的用户贡献了多少额度
思路
1、统计20%用户是多少人
2、筛选出前20%的用户并统计消费金额
3、利用前20%用户的除以全部的即可
tips
1、筛选出前20%的用户—使用GROUP BY(userId) ORDER BY(price)
①统计20%用户人数

SELECT count(DISTINCT (userId))*0.2 as '20%用户数量' FROM order_info_1 WHERE isPaid='已支付' 

②筛选出前20%的用户并统计消费金额

SELECT sum(a.p) as 'top20%的消费总额'
FROM (SELECT userId,sum(price) as p 
FROM order_info_1 WHERE isPaid='已支付' GROUP BY userId ORDER BY p DESC LIMIT 17129)a

③利用前20%用户的除以全部的即可

SELECT concat((c.fp/b.zp)*100,'%') as 用户占总消费金额的百分比
FROM (SELECT sum(a.p) as fp
FROM (SELECT userId,sum(price) as p 
FROM order_info_1 WHERE isPaid='已支付' GROUP BY userId ORDER BY p DESC LIMIT 17129)a)c,(SELECT sum(price) as zp FROM order_info_1 WHERE isPaid='已支付') as b  

（三）总结

1、拿到数据后需要先了解数据的分布，不能直接开始做分析，不然会在分析中存在很多异常值进而影响判断
2、本次分析结果显示：（1）三月份购买次数明巷高于四月；（2）复购率在30%左右；（3）该产品无明显性别差异，男性顾客和女性顾客购买次数相差无几；（4）该产品符合“二八定律”，前20%用户贡献85%的购买力，以后的产品推广或者用户调查可能需要偏向前20%人群。