有志同道合的朋友，欢迎我们一起学习，互相帮助我的微信是tss2817411628.
这是我的一次学习数据分的项目实战，我在开始之前只掌握mysql的基本语句，然后就没看讲解视频直接自己按照问题写sql，结果废了很大功夫也没搞懂，所以我把这次六道题做一次总结，把我的代码发出来，老师的也发出俩，梳理两种代码的思路。自娱自乐，如果对你有帮助，不胜荣幸；如果你是前辈能指出我的问题，劳驾指出问题，我一定不胜感激！！！
咱们开始~
先看下表都有啥：
第一张表，订单信息。
第二张，顾客信息表
涉及表：
orderinfo 订单详情表 userinfo 用户信息表
| orderid 订单id | userid 用户id
| userid 用户id | sex 用户性别
| isPaid 是否支付 | birth 用户出生日期
| price 付款价格
| paidTime 付款时间
问题：1、统计不同月份的下单人数
2、统计用户三月份的回购率和复购率
3、统计男女用户消费频次是否有差异
4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
5、统计不同年龄段，用户的消费金额是否有差异
6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额
1、统计不同月份的下单人数
我的代码：
先按照月份进行分组，然后就可以统计订单数量了，注意同一用户可能会产生多个订单，所以要去重
select MONTH(paidTime),COUNT(DISTINCT userid) from orderinfo
WHERE paidTime<>'0000-00-00 00:00:00' AND isPaid ='已支付'
GROUP BY MONTH(paidTime) ;
老师代码
select year(paidTime), month(paidTime), count(distinct userid) as cons
from orderinfo
where isPaid="已支付"and paidTime<>'0000-00-00 00:00:00'
group by year(paidTime),month(paidTime);
老师的代码先过滤掉未支付和支付时间的脏数据，然后按照年月分组，在对年月分组中的客户进行去重聚合
错误代码
这是我写的一个错误代码
SELECT MONTH,COUNT(userid) as '订单人数'
FROM(
select *,MONTH(paidTime) as month
from orderinfo
WHERE paidTime<>'0000-00-00 00:00:00' AND isPaid='已支付'
GROUP BY userid)a
GROUP BY MONTH;
代码结果显示3月份是对得上号，但是四月五月不对，为啥呢？
因为我的子查询是对用户进行聚合，如果a用户3月份消费且在四月也消费，那么分组时候只显示他在3月份的消费，四月份就不显示出来，那么最外面的select自然也统计不上了。比如说这个id=18347的客户
2、统计用户三月份的回购率和复购率
回购率：上月购买用户中有多少用户本月又再次购买
我的代码
SELECT COUNT(DISTINCT userid)/(
SELECT count(DISTINCT userid)
FROM orderinfo
WHERE isPaid="已支付" AND MONTH(paidTime)=3)#这里是3月份消费人数
FROM orderinfo
WHERE userid IN(
SELECT DISTINCT userid
FROM orderinfo
WHERE isPaid="已支付" AND MONTH(paidTime)=3)#这里是3月份消费的用户名单
AND isPaid="已支付" AND MONTH(paidTime)=4;#3月份消费的用户名单知道了，带进去4月份取比对，会选出3月份和4月份都消费的用户
最后用3月和4月都消费的人数和除以三月份消费人数（我觉得我的代码太长了，而且感觉笨笨的，能有人指点就好了）
老师的代码
#1、每个用户，每月的消费
#2、两个表联结，通过userid和b表的时间偏移（往前偏一个月）来联结
#3、然后在按月份分组前提下，汇总左边总次数和右边有次数，求回购率
SELECT MONTH(atime),COUNT(*),COUNT(userid2),COUNT(userid2)/COUNT(*) as huigou
FROM(
SELECT *
FROM( #1、每个用户，每月的消费
SELECT userid,DATE_FORMAT(paidTime,'%Y-%m-%01') as atime
FROM orderinfo
WHERE isPaid <>'未支付' AND paidTime <>'0000-00-00 00:00:00'
GROUP BY userid,atime)a
LEFT JOIN (
SELECT userid as userid2,DATE_FORMAT(paidTime,'%Y-%m-%01') as time
FROM orderinfo
WHERE isPaid <>'未支付' AND paidTime <>'0000-00-00 00:00:00'
GROUP BY userid2,time)b
ON a.userid=b.userid2 AND atime=DATE_SUB(time,INTERVAL 1 MONTH)
#2、两个表联结，通过userid和b表的时间偏移（往前偏一个月）来联结
)c
GROUP BY atime;#3、然后在按月份分组前提下，汇总左边总次数和右边有次数，求回购率
#复购率：
#3月份的消费情况，显示每个用户的消费次数
SELECT *,COUNT(userid)
FROM orderinfo
WHERE isPaid ='已支付' AND MONTH(paidTime)=3
GROUP BY userid; #三月每个用户的购买情况以及次数
#用消费1次以上的用户除以消费总数
SELECT MONTH(paidTime),
SUM(IF(cs>1,1,0)),#如果买的次数大于1，记1
COUNT(cs),SUM(IF(cs>1,1,0))/COUNT(cs) as fugou
FROM(
SELECT *,COUNT(userid) as cs
FROM orderinfo
WHERE isPaid ='已支付' AND MONTH(paidTime)=3
GROUP BY userid
)a; #用上一条结果作为表
老师的代码
select
count(1) as userid_cons,
sum(if(cons>1,1,0)) as fugou_cons,
sum(if(cons>1,1,0))/count(1) as fugou_rate
from (select
userid,
count(1) as cons
from orderinfo
where isPaid="已支付"
and month(paidTime)="03"
group by userid
) a;
3、统计男女用户消费频次是否有差异
我的代码
SELECT COUNT(*),
SUM(IF(sex='男',1,0)) AS '男性购买数量',
SUM(IF(sex='女',1,0)) AS '女性购买数量',
SUM(IF(sex='男',1,0))/COUNT(*) AS '男生购买率',
SUM(IF(sex='女',1,0))/COUNT(*) AS '女生购买流率'
FROM(
SELECT a.userid,sex
FROM orderinfo AS a
INNER JOIN userinfo AS b ON a.userid=b.userid
WHERE isPaid <>'未支付' AND sex <>''
GROUP BY a.userid ##筛选出每个用户消费记录
)c;
老师的代码
select
sex,
avg(cons) as avg_cons
from (select
a.userid,
sex,
count(1) as cons
from orderinfo a
inner join (select * from userinfo where sex<>'') b
on a.userid=b.userid
group by a.userid,sex) a
group by sex;
4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
我的代码
SELECT userid,
MIN(paidTime),
MAX(paidTime),
DATEDIFF(MAX(paidTime),MIN(paidTime))
FROM orderinfo
WHERE isPaid <>'未支付'#筛选出有效支付记录
GROUP BY userid HAVING COUNT(userid)>1;#过滤掉消费一次的用户
老师的代码
select
userid,
min(paidTime),
max(paidTime),
datediff(max(paidTime), min(paidTime))
from orderinfo
where isPaid="已支付"
group by userid
having count(1)>1;
我和老师的代码差不多
5、统计不同年龄段，用户的消费金额是否有差异
#通过计算各年龄段人数，然后求各年龄段消费金额，然后求平均消费金额
#计算每个人年龄，然后根据userid聚合orderinfo
#筛掉脏数据并且对用户进行分组，同时计算每个用户的消费总金额！！！！（这个点卡了我好久）
#然后将上步结果作为子查询，计算各年龄段消费金额
SELECT age,avg(prices)'每个年龄段平均消费金额',AVG(ci)'每个年龄段平均消费次数'
FROM(
SELECT a.userid,SUM(price) as prices,COUNT(a.userid) AS ci,age#要在这里进行聚合每个用户的消费金额
FROM(
SELECT *,CEIL(TIMESTAMPDIFF(YEAR,birth,NOW())/10) as age
FROM userinfo
WHERE birth>'1901-00-00'
)a
INNER JOIN orderinfo AS b
ON a.userid=b.userid
WHERE isPaid<>'未支付'
GROUP BY a.userid
)c
GROUP BY age;
老师的代码
select
age,
avg(cons),
avg(prices)
from (select
a.userid,
age,
count(1) as cons,
sum(price) as prices
from orderinfo a
inner join (select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00') b
on a.userid=b.userid
group by a.userid,age) a
group by age;
老师代码的结果和我有点细小差别，但总体不影响
6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额
最后一题老师都是分开算的比较简单
我的代码
SELECT SUM(zj)/(
SELECT SUM(zzj)
FROM(
SELECT *,SUM(price) AS zj
FROM orderinfo
WHERE isPaid<>'未支付'
GROUP BY userid
ORDER BY SUM(price) DESC
)b
)
FROM(
SELECT *,SUM(price) AS zj
FROM orderinfo
WHERE isPaid<>'未支付'
GROUP BY userid
ORDER BY SUM(price) DESC
LIMIT 17000)a
老师的代码
1、统计每个用户的消费金额，并进行一个降序排序
select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid;
2、统计一下一共有多少用户，以及总消费金额是多少
select
count(1) as cons,
sum(total_price) as all_price
from (select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid) a;
3、取出前20%的用户进行金额统计
select
count(1) as cons,
sum(total_price) as all_price
from (
select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid
order by total_price desc
limit 17000) b ;
项目一前前后后一共做了三遍，三遍才能能掌握的比较透。每个题目我错在哪里，或者思路哪里不对，也学到了许多细节。很累，才开始也很难，尤其之前课程代码都非常简单，自己第一次独立做题，突然觉得好难，难得进行不下去，但是还是硬着头皮做，如今6道终于全部吃透，雨过天晴的感觉真好。
下面我罗列一下，我在这6道题中的一点收获。可能不对，抱着怀疑态度看吧。
取别名
在select 后面可以取别名，但是在参与筛选时候不可以使用别名。
比如你计算总消费金额 sum(price) as zj，如果后面用where筛选，只能写sum(price)>***,写sum(zj)>***，就会报错
有子查询的注意事项
做子查询时候的表中不能有命名相同的列
不然会报1060 - Duplicate column name 'userid'的错误
前路漫漫，继续加油
