MySQL案例分析(商品的用户消费行为数据分析)-分解式详细教程（一）

最新推荐文章于 2022-06-16 08:17:34 发布

夹着巧克力吃

最新推荐文章于 2022-06-16 08:17:34 发布

阅读量1.2k

点赞数 2

分类专栏：数据分析文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_43635231/article/details/105791610

版权

数据分析专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

前沿

我一直想找有关数据分析的相关工作，但是我专业和想找的工作偏差很多，所以一直没有找到理想的工作，最近内心也一直很浮躁。
输出是最好的学习方式，和其他人分享我的知识成果会让我进步。

数据分析–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)

结果运行如下：
在这里插入图片描述
还有四个数据提取的问题，放到下一篇文章讨论。

写作感想

一直在使用csdn学习，从这个知识社区学到了很多知识，我个人对编程十分感兴趣，但是，可惜我不是科班出身，基础是非常差的，但我现在也一直在努力的不断进步。
以前一直使用notebook，jupyter notebook ，markdown记自己的笔记，也一直想要分享出来，但是感觉很麻烦，今天是第一次使用csdn的文章功能，发现真的很好用，还能把我所学到的和大家一起交流，真的很不错。
我是希望在这里和大家一起进步，成长，感谢这个很棒的社区！
有什么不懂的，大家可以一起讨论哈！