数据表：
（由于习题的原数据量过大，节选头部10条，查看基础信息，会写sql语句即可）
userinfo：
用户信息表
orderinfo：
订单信息表
数据字典：
orderinfo 订单详情表
| orderid 订单id
| userid 用户id
| isPaid 是否支付
| price 付款价格
| paidTime 付款时间
userinfo 用户信息表
| userid 用户id
| sex 用户性别
| birth 用户出生日期
要求：
1、统计不同月份的下单人数
2、统计用户三月份的回购率和复购率
3、统计男女用户消费频次是否有差异
4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
5、统计不同年龄段，用户的消费金额是否有差异
6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额
基本信息了解：
1.用户信息表通过userid(一)与订单详情表的userid(多)相连接
2.用户信息表是信息表，订单详情表是流水表
参考答案：
1、统计不同月份的下单人数：
select year(paidTime),month(paidTime),count(distinct userid) as cons
from orderinfo
where isPaid ='已支付'
group by year(paidTime),month(paidTime)
2、统计用户3月份的回购率和复购率
- 复购率：当月购买了多次的用户占当月用户的比例
3月的复购率 =3月多次购买的用户数/3月购买的总用户数 - 解题思路：
根据题意，数据表确定是订单详情表，然后考虑如下：
1.筛选3月购买情况
select userid
from orderinfo
where isPaid="已支付"and month(paidTime)="03"
group by userid
2.统计每个用户在3月的购买次数
select userid,count(userid) as cons
from orderinfo
where isPaid="已支付"and month(paidTime)="03"
group by userid
3.用case when判断并筛选3月多次购买客户，并统计复购率
select count(userid) as userid_cons, #总购买人数
sum(case when cons>1 then 1 else 0 end)as fugou_cons #复购人数
sum(case when cons>1 then 1 else 0 end)/count(userid) as fugou_rate #复购率 =复购人数/ 总购买人数
from
(select userid,count(userid) as cons
from orderinfo
where isPaid="已支付"and month(paidTime)="03"
group by userid) as a; #子表要有命名否则执行报错
- 回购率：本月购买用户中有多少用户下个月又再次购买
3月份的回购率 = 3月用户中4月又再次购买的人数 / 3月的用户总数 - 解题思路：
根据题意，数据表确定是订单详情表，然后考虑如下：
1.统计每年每月的一个用户消费情况 （拿到3月、4月的每个用户购买次数）
select userid,date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01');
2.相邻月份进行关联，能关联上的用户说明就是回购用户
select *
from
(select userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) as a
left join
(select userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) as b
on a.userid = b.userid
and date_sub(b.month_dt,interval 1 month) =a.month_dt
PS：注意date_sub用法，以及 left join on 之后 and 和where用法的区别！
3.统计每个月份的消费人数情况即可得到回购率
select
a.month_dt, #各月份
count(a.userid), #购买人数
count(b.userid), #回购人数（例如：3月购买用户中4月又再次购买的用户）
count(b.userid)/count(a.userid) as huigou_rate #回购率=回购人数/购买人数
from
(select userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) as a
left join
(select userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) as b
on a.userid = b.userid
and date_sub(b.month_dt,interval 1 month) =a.month_dt
3、统计男女用户消费频次是否有差异
select c.sex,avg(cons) as avg_cons
from
(select a.userid,b.sex,count(1) as cons
from orderinfo as a
inner join
(select *
from userinfo
where sex<>'') as b
on a.userid =b.userid
group by a.userid,b.sex) as c
group by c.sex
4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
select userid,min(paidTime),max(paidTime),datediff(max(paidTime),min(paidTime)) as dt
from orderinfo
where isPaid ='已支付'
group by userid
having count(userid)>1;
5、统计不同年龄段，用户的消费金额是否有差异
1.计算每个用户的年龄，并对年龄进行分层:0-10:1,11-20:2,21-30:3...依次类推间隔10为一类
select userid,birth,now(),ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00'
2.关联订单信息，获取不同年龄段的一个消费频次和消费金额
select a.userid,b.age,count(1)as cons,sum(a.price) as price
from orderinfo as a
left join
(select userid,birth,now(),ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-0') as b
on a.userid = b.userid
group by b.age
3.再对年龄分层进行聚合，得到不同年龄层的消费情况
select age,avg(cons) ,avg(sums)
from
(select a.userid,b.age,count(1)as cons,sum(a.price) as sums
from orderinfo as a
inner join
(select userid,birth,now(),ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-0') as b
on a.userid = b.userid
group by b.age) as c
group by age
6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额
1.计算每个用户的消费金额，并进行一个降序排序
select userid,sum(price) as sums
from orderinfo
where ispaid='已支付'
group by userid
2.统计一下一共有多少用户，以及总消费金额是多少
select count(userid),sum(sums) as total_price
from
(select userid,sum(price) as sums
from orderinfo
where ispaid='已支付'
group by userid) as a
3.取出前20%的用户进行金额统计
select count(userid),sum(sums) as total_price
from
(select userid,sum(price) as sums
from orderinfo
where ispaid='已支付'
group by userid
order by sums desc
limit 20%(用户数) as a #用户数从2中count(userid)中可以得出数据
