在data下面新建两张order和user表，将原数据导入mysql，csv的格式以逗号分隔。
load data local infile 'C:/Users/Administrator/Desktop/day1/user_info_utf.csv' into table data.user
fields terminated by ',';

order表部分如下：
select * from data.order

1.三月份复购率：
思路：在三月份的条件下，对userId进行分组再计数，筛选出购买次数大于1的人占三月份购买总人数的占比

select userId,count(userId) as paidcounts from data.order
where isPaid ='已支付'
and date_format(paidTime,'%Y%m')=201603
group by userId

先找出每个用户的购买次数：
select count(userId),count(if(paidcounts>1,1,null)) as repaidc
from(
select userId,count(userId) as paidcounts from data.order
where isPaid ='已支付'
and date_format(paidTime,'%Y%m')=201603
group by userId) t
通用方法：可求每个月的回购率
思路：先将每个用户所有的消费时间筛选出来，用left join，产生笛卡尔积效应，筛选出月份相差为1的记录，求出占比。
select userId,date_format(paidTime,'%Y-%m-01') m
from data.order
where isPaid='已支付'
group by userId,date_format(paidTime,'%Y-%m-01')

再此基础上对各个月份进行分组，计算占比
select t1.m,count(t1.m),count(t2.m) cback from(
select userId,date_format(paidTime,'%Y-%m-01') m from data.order
where isPaid='已支付'
group by userId,date_format(paidTime,'%Y-%m-01')) t1
left join(
select userId,date_format(paidTime,'%Y-%m-01') m from data.order
where isPaid='已支付'
group by userId,date_format(paidTime,'%Y-%m-01'))t2
on t1.userId=t2.userId and t1.m=date_sub(t2.m,interval 1 month)
group by t1.m

三月份回购率约为：23.9%

统计男女消费频次是否有差异 ：
思路：将order表和user表连接，条件是已支付且性别不为空。对userId分组，查询出对应的消费次数。再次嵌套groupby对性别分组，求平均值。
select sex,avg(c)
from(
select o.userId,u.sex,count(u.sex) as c
from data.order o
join data.user u
on o.userId=u.userId
where isPaid='已支付' and sex !=''
group by o.userId) t
group by sex

统计多次消费的用户，第一次和最后一次消费间隔是多少？
思路：对userId分组，过滤出消费大于1次的，再找出时间的最大和最小值、消费间隔。
select userId,max(paidTime),min(paidTime),
datediff(max(paidTime),min(paidTime)) deltaday
from data.order
where isPaid='已支付'
group by userId
having count(userId)>1

统计不同年龄段，用户的消费金额是否有差异
先将用户年龄按10的梯级划分，把大于100岁的去除。
select userId,sex,ceil(age/10) as level from(
select userId,sex,year(now())-year(user.birth) as age
from data.user) t1
where age<100
select t2.level,o.userId,count(o.userId) ct
from(
select userId,sex,ceil(age/10) as level from(
select userId,sex,year(now())-year(user.birth) as age
from data.user) t1
where age<100) t2
join data.order o
on o.userId=t2.userId and o.isPaid='已支付'
group by t2.level,o.userId) t3
group by level

根据结果可知，0-30年龄段的消费频次较低，中年段的消费频次相对低龄段有所增高且平均消费频次在所有年龄段中最高，到老龄段频次又逐渐降低。

统计消费的二八法则，消费的TOP20%用户，贡献了多少额度？
思路：先计算出前20%的用户数量，再算出前20%用户贡献额度总和。