文章目录
- 前沿
- 数据分析--MySQL技能
- 数据的导入
- 该部分数据库使用的相关知识
- 所要完成的数据提取任务
- 提数思路
- 创建表的结构说明
- 1-统计不同月份的下单人数
- 2-统计用户三月份的回购率和复购率
- 复购率
- 回购率
前沿
我一直想找有关数据分析的相关工作,但是我专业和想找的工作偏差很多,所以一直没有找到理想的工作,最近内心也一直很浮躁。
输出是最好的学习方式,和其他人分享我的知识成果会让我进步。
数据分析–MySQL技能
数据分析的需要的基本工作技能就是MySQL的增删改查的掌握吧,下面的案例我当年在京东工作的一个总结吧,数据来源于网络。我也提供了下载。
下载链接:
链接:https://pan.baidu.com/s/1AzGmPLT3ydWQ5xqeSxFBmQ
提取码:kri2
下面是我个人使用MySQL进行数据提取的一些心得,仅供个人学习用,不作任何商业用途使用。
数据的导入
我练习使用的数据库为Navicat,网上有很多免费的版本,大家可以自行下载。
该部分需要创建两个数据库
该部分数据库使用的相关知识
在你接入数据库之后,如何查看你使用的数据库
show databases;
查看使用数据库
select database();
选择数据库
use 数据库名字
-- 例如
use jingdong;
删除表的操作
drop table if exists 表的名字;
-- 例如
drop table if exists ORDERINFO;
创建表的操作
create table if exists 表名字{
列名 类型...}
-- 例如,接下来我们要创建的两个表
CREATE TABLE IF NOT EXISTS ORDERINFO (
ORDERID varchar(10) NULL,
USERID varchar(10) NULL,
ISPAID varchar(10) NULL,
PRINCE varchar(10) NULL,
PAIDTIME varchar(10) NULL
)
CREATE TABLE IF NOT EXISTS USERINFO (
USERID varchar(10) NULL,
SEX varchar(10) NULL,
BIRTH varchar(10) NULL
)
对于数据的导入,我选择使用Navicat·本身自带的导入功能
这里就不先细说了,如果有机会,我会在我的其他文章里面做一个详细的操作说明。
所要完成的数据提取任务
1-统计不同月份的下单人数
2-统计用户三月份的回购率和复购率
3-统计男女的消费频次是否有差异
4-统计多次消费的用户,第一次和最后一次消费时间的间隔
5-统计不同年龄段的用户消费金额是否有差异
6-统计消费的二八法则,消费的top20%用户,贡献了多少额度
接下来,是我提取数据的思路和想法
提数思路
使用SQL进行数据提取,一定要分清,你提取的数据主题是什么、从哪个表进行提取,需要哪些筛选条件
创建表的结构说明
我们创建了两个表ORDERINFO、USERINFO
ORDERINFO里面有用户的ID,支付状态,支付价格。日期
USERINFO里面有用户的id和出生日期,性别
1-统计不同月份的下单人数
可以看到我们需要从ORDERINFO按月份进行提取
按月提取,需要使用group by函数
下单人数,需要提取已支付
下面我们就开始解决上面的两个问题
对于第一个问题按月提取
这里面需要我们使用SQL里面的字符串切分的方法
substring_index(被截取字段,关键字,关键字出现的次数)
为什么使用这个办法,因为工作中的话,很多时候month函数会失效
select * from 表名字 group by substring_index(被截取字段,关键字,关键字出现的次数)
select * from ORDERINFO group by substring_index(被截取字段,关键字,关键字出现的次数)
对于第二个问题,使用where 函数即可
select * from ORDERINFO where ispaid = '已支付';
两个问题都解决了,我们现在将他们组合到一起就可以了
select userid,substring(paidtime,1,4) 年,substring(paidtime,6,1) as 月份 from ORDERINFO
where ispaid = '已支付'
group by substring(paidtime,1,4),substring(paidtime,6,1);
2-统计用户三月份的回购率和复购率
那么首先,我们要了解一下什么是回购率,什么是复购率
复购率
复购率是在本月消费中多少人消费一次以上的占比,也就是说,一个消费一次以上
怎么处理呢,也就是说,我们需要统计,userid分组之后,按月,大于1的次数
这里呢,可以使用having,但是我推荐使用case when这个函数
关于casewhen函数的使用,看这个教程就很好了
case when语法学习
case when condition then 输出 else 输出2 end as 列名;
这条语句的含义就是,根据不同的情况,把结果输出到as 后面跟着的列里面
学习完上面的知识后,我们看看这个数据应该如何提取
首先,提取三月份的数据,使用where即可
select * from ORDERINFO
where ispaid = '已支付'
and substring(paidtime,6,1) = 3;
之后,在使用分组,useid大于1即可,这里面我不使用having函数了,使用case when 函数比较直观
select userid,case when count(userid)>1 then 1 else null end as 复购率
from ORDERINFO
where ispaid = '已支付'
and substring(paidtime,6,1) = 3
group by userid ;
结果截图如下,复购的使用1来表示,未复购的使用null来表示
我们最后,将三月总购买用户和复购用户提取出来,我们使用上面的结果,将他作为一个表as t
然后,在对这个表进行操作,这样做的一个好处就是,不需要重新创建新的表
使用虚拟表的语法如下:
select t.* from () as t;
select sum(t.`复购率`),count(t.userid) from
(select userid,case when count(userid)>1 then 1 else null end as 复购率
from ORDERINFO
where ispaid = '已支付'
and substring(paidtime,6,1) = 3
group by userid ) as t;;
结果的截图如下:
当然一个题目是有很多种解法,下面我在使用另一种的解法:
我先把代码放到下面,下面大家自己先思考一下语法的结构
select count(tt.USERID) '三月购买的总用户数',count(tt.USERID_count) '多次购买用户数'from
(select
t.USERID USERID,
case when t.USERID_count>1 then 1 else null end USERID_count
from
(select USERID ,count(USERID) USERID_count from ORDERINFO
where ISPAID = '已支付' and substring(paidtime,6,1) = '3'
group by USERID ) T
)tt
结果截图如下:
不知道大家,看懂了没有,这个相当于建立了一个表中表,一个tt和t,工作中这样的场景很多,可以先掌握这种思路。
回购率
接下来,我们在看下什么叫做回购率
回购率是三月份购买的人数四月份依旧购买
也就是说,本月购买和下月都购买,这个就需要使用join了
虚拟表1,提取用户的id,group by 月份 left join 虚拟表2
这边的难点是,如果是吧下一个月复购的提取出来,其实很简单,on的条件中,虚拟表2的对应月份字段为虚拟表1对应月份字段+1就可以,我好像没表达明白,使用代码说明吧。
具体代码细节如下:
第一步:虚拟表1的建立,这一部分,我们需要按用户和月份进行分组。
select userid,substring(paidtime,1,4) 年,substring(paidtime,6,1) as 月份 from ORDERINFO
where ispaid = '已支付'
group by userid,substring(paidtime,6,1) ORDER BY USERID
第二步:我们连接表,思路上本月和下月为on的条件,那么,这里我么要记住,a表中月份=b表中的数据-1语法如下:
SELECT * FROM
(select userid,paidtime from ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) a
left join (select userid,paidtime from ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) b on a.userid = b.userid and substring(a.paidtime,6,2) = substring(b.paidtime,6,2)-1
结果如下:
接下来,我们就需要对这个表使用分组统计了, 对其进行统计了
代码如下:
select substring(a.paidtime,1,7) '年月',count(a.USERID) '本月消费的用户数量',count( b.USERID)'本月回购的用户数' from
(select userid,paidtime from ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) a
left join (select userid,paidtime from ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) b on a.userid = b.userid and substring(a.paidtime,6,2) = substring(b.paidtime,6,2)-1
group by substring(a.paidtime,1,7)
代码的结果如下:
注意,这个代码是错误的,这个工作中经常会出错的一个地方,错误的地方在于,我们count里面的数据是有重复的,这个时候,需要我们使用去重的函数distinct
正确的代码如下:
select substring(a.paidtime,1,7) '年月',count(distinct a.USERID) '本月消费的用户数量',count(distinct b.USERID)'本月回购的用户数' from
(select userid,paidtime from ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) a
left join (select userid,paidtime from ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) b on a.userid = b.userid and substring(a.paidtime,6,2) = substring(b.paidtime,6,2)-1
group by substring(a.paidtime,1,7)
结果运行如下:
还有四个数据提取的问题,放到下一篇文章讨论。