MySQL 案例分析(1)—销售数据分析
(一) 数据导入及主要问题
从上文中下载数据集,分别为如下两个文件:
在导入时存在的问题
本数据集中包含中文字符,第一次导入MySQL数据时频繁失败,经过数次尝试后才成功
(1)数据集的格式如果是CSV(UTF-8),在MySQL中设计表时,中文格式所对应的字段格式要选择UTF8
在接下来右击要新建的表格,选择导入向导,选择对应的CSV文件,在编码的选择栏中选择65001(UTF-8)才不会导入失败
(2)数据集的格式如果是CSV(逗号分隔)**(注意这两种CSV是不同的类型)**在MySQL中设计表时,中文格式所对应的字段格式要选择gbk格式
在接下来右击要新建的表格,选择导入向导,选择对应的CSV文件,在编码的选择栏中选择10008(Simplified Chinese GB2312)才不会导入失败
主要问题
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%人群。