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

前言

我把自己的知识分享到博客上主要的目的有两个：一个是督促自己的学习，可能因为疫情等原因把，我发现自己变得懒惰了，不像以前那样自律了，生活好像变得很颓废，健身的次数也变少了。我希望通过这种分享的方式，找回以前的我，也为自己的找工作充一充电；二是，我希望和大家一起交流讨论，发现自己的不足，不断拟补自身，也和大家一起进步。
写到这里，剩下的事情就是我们一起加油啦！

问题

3-统计男女的消费频次是否有差异
4-统计多次消费的用户，第一次和最后一次消费时间的间隔
5-统计不同年龄段的用户消费金额是否有差异
6-统计消费的二八法则，消费的top20%用户，贡献了多少额度

3-统计男女的消费频次是否有差异

消费频次

比如：男生消费次数/消费男生人数
这个就需要两个表做一个关联了，首先看一下USERINFO这个表

我们会发现，这个表的数据是有缺失的，我们需要的是性别，因此，我们首先要生成一个虚拟表，过滤掉null值，具体代码操作如下：

select userid,sex from USERINFO where sex <> '';
select userid,sex from USERINFO where sex is not NULL;

以上这两个操作都可以，我们将其名为为虚拟表a
两个表根据userid 进行连接，具体代码如下：

select * from ORDERINFO as o inner join (select userid,sex from USERINFO where sex <> '') as a on o.userid = a.userid;

得到的部分结果如下;

接下来我们需要按照性别，id进行分组（自己想一想为什么要多条件分组，需要性别和id-------因为我们这一步要的是每个用户的信息），也就是说我们这一步做到统计每个用户的性别和他的消费次数，具体代码如下：

select a.userid,a.sex,count(*) from ORDERINFO as o 
inner join 
(select userid,sex from USERINFO where sex <> '') as a 
on o.userid = a.userid 
GROUP BY a.userid,a.sex;

得到的部分结果如下：

那么，到了最后一步了，我们需要使用avg这个函数，求出平均值即为消费频率，具体代码如下：

SELECT b.SEX ,avg(ct) FROM(
select a.userid,a.sex,count(*) as ct from ORDERINFO as o 
inner join 
(select userid,sex from USERINFO where sex <> '') as a 
on o.userid = a.userid 
GROUP BY a.userid,a.sex) AS b GROUP BY b.SEX

4-统计多次消费的用户，第一次和最后一次消费时间的间隔

这个我们可以先分布来做，那么第一步，先检索出多次消费的用户，然后在时间上做一个加减法即可。
首先，多次消费用户，使用having和group by函数筛选。

SELECT * FROM orderinfo
WHERE ISPAID = '已支付' 
GROUP BY USERID HAVING count(1)>1;

得到结果如下：

那么，接下来我们在这个基础上提取，每个用户，时间上的第一次和最后一次，使用max和min即可，具体代码如下：

SELECT * ,MAX(PAIDTIME),MIN(PAIDTIME)FROM orderinfo
WHERE ISPAID = '已支付' 
GROUP BY USERID HAVING count(1)>1;

得到的结果如下：

接下，我们对统计出来的时间进行加减就可以了，这里需要使用datediff函数，具体代码如下：

SELECT * ,datediff(MAX(PAIDTIME),MIN(PAIDTIME) ) 间隔天数 FROM orderinfo
WHERE ISPAID = '已支付' 
GROUP BY USERID HAVING count(1)>1;

得到的结果如下：

5-统计不同年龄段的用户消费金额是否有差异

首先，我们需要统计不同的年龄段，这个需要对USERINFO表进行操作，首先使用year函数，统计出每个用户的年龄。代码如下：

select USERID,year(now())-left(BIRTH,4) age 
from USERINFO 
where BIRTH is not null

得到的结果：

接下来我们需要其作为一个虚拟表和ORDERINFO进行连接：

select * from  
(SELECT * FROM ORDERINFO where ISPAID = '已支付' )a
inner join 
(select USERID,year(now())-left(BIRTH,4) age from USERINFO where BIRTH is not null) b 
on a.USERID = b.USERID

得到的结果如下：

从这个表中看，我们已经得到了年龄的消费的情况了，接下来，我们就需要使用case when进行分组，下面的代码可能比较冗长，如果哪位大神可以简化，请在留言里面指出来：

select  a.ORDERID,A.USERID,cast(A.PRINCE as decimal(10, 2)) PRINCE,
case  when B.AGE between 10 and 19 then '10-19岁'  when B.AGE between 20 and 29 then '20-29岁' 
when B.AGE between 30 and 39 then '30-39岁' when B.AGE between 40 and 49 then '40-49岁' when B.AGE between 50 and 59 then '50-59岁' 
when B.AGE between 60 and 69 then '60-69岁'when B.AGE between 70 and 79 then '70-79岁'
else null end NLD,
B.AGE from 
(select * from  ORDERINFO where ISPAID = '已支付')a
inner join (select USERID,year(now())-left(BIRTH,4) age from USERINFO where BIRTH is not null) b on a.USERID = b.USERID

对上面的几个函数的一个说明：
cast，强制类型的转换。
得到的结果为：

剩下就是最后一步了，根据分组的情况，求平均数，也就是看不同年龄段，消费水平有没有差异，这里面，使用group by函数就可以完成，代码如下：

select aa.NLD,round(avg(aa.PRINCE),2) avg_prince from
(select  a.ORDERID,A.USERID,cast(A.PRINCE as decimal(10, 2)) PRINCE,
case  when B.AGE between 10 and 19 then '10-19岁'  when B.AGE between 20 and 29 then '20-29岁' 
when B.AGE between 30 and 39 then '30-39岁' when B.AGE between 40 and 49 then '40-49岁' when B.AGE between 50 and 59 then '50-59岁' 
when B.AGE between 60 and 69 then '60-69岁'when B.AGE between 70 and 79 then '70-79岁'
else null end NLD,
B.AGE from 
(select * from  ORDERINFO where ISPAID = '已支付')a
inner join (select USERID,year(now())-left(BIRTH,4) age from USERINFO where BIRTH is not null) b on a.USERID = b.USERID
)aa group by aa.NLD having aa.NLD is not null

得到的结果如下：

6-统计消费的二八法则，消费的top20%用户，贡献了多少额度

这一步，数据提取的难度是，top20%要如何表达
我们一步步来做，首先我们需要求出，每个用户消费了多少钱。
那么需要group by userid，代码如下：

SELECT *,sum(PRINCE) as total FROM orderinfo GROUP BY USERID ORDER BY total DESC;

得到结果：

分组之后，我们在根据上面的结果看一下，总count（userID）的0.2有多少：

SELECT count(a.USERID)*0.2,sum(a.total) FROM
(SELECT *,sum(PRINCE) as total FROM orderinfo GROUP BY USERID ORDER BY total DESC) a;

结果如下：

那么接下来，我们就可以结合limit得到我们需要的结果了

SELECT count(a.USERID),sum(a.total) FROM
(SELECT *,sum(PRINCE) as total FROM orderinfo GROUP BY USERID ORDER BY total DESC limit 20306) a ;

得到的结果

好了，到这里，我们已经上面的六个在工作中常用到的提取问题解决了

写到最后

人可以自律也可以颓废，取决于你现在过得是不是你想要的，和你要得到什么。我不能好高骛远，希望我脚踏实地的做好眼前的事情。
机遇是给又准备的人的，不是说你自己努力了，你就会成功，但是你努力了，会加大你成功的几率。
不要想着，世界为什么对你不公平，也不要想着美国宣言里面的人人生而平等。要得到，就需要靠自己的努力来争取。
很多事，看着容易，但是，实践中，才知道处处都有坑，这几个问题，开始我是以为很容易的，但是做的时候，才发现，不经意间，会有很多小坑出现，希望大家不要只看看，事必躬亲。
还有就是从小事做起，不积跬步无以至千里。
好了，就写到这里，一起加油啦！