1. 统计不同月份的下单人数
date_format(paidTime,'%Y-%M') count(distinct userId)
2016-April 43967
2016-March 54799
2016-May 6
selectdate_format(paidTime,'%Y-%M'),count(distinct userId) from data.orderinfo
where isPaid = '已支付'
group by date_format(paidTime,'%Y-%M')
2. 统计用户三月份的回购率和复购率
回购率
m count(t1.m) count(t2.m) count(t2.m)/count(t1.m)
2016/3/1 54799 13119 0.2394
2016/4/1 43967 4 0.0001
2016/5/1 6 0 0
select t1.m,count(t1.m),count(t2.m),count(t2.m)/count(t1.m) from (
select userId,date_format(paidTime,'%Y-%m-01') as m from data.orderinfo
whereisPaid ='已支付'
groupby userId,date_format(paidTime,'%Y-%m-01')) t1
left join(
selectuserId,date_format(paidTime,'%Y-%m-01') as m from data.orderinfo
whereisPaid ='已支付'
groupby 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
复购率
count(ct) count(if(ct>1,1,null)) count(if(ct>1,1,null))/count(ct)
54799 16916 0.3087
selectcount(ct),count(if(ct>1,1,null)),count(if(ct>1,1,null))/count(ct) from (
selectuserId,count(orderId) as ct from data.orderinfo
whereisPaid = '已支付'
anddate_format(paidTime,'%Y-%M') = '2016-March'
groupby userId)t
3. 统计男女用户的消费频次是否有差异
sex avg(m)
女 1.7827
男 1.8035
select sex,avg(m) from(
selectuserId,count(userId) as m from data.orderinfo
where isPaid ='已支付'
group byuserId)t1
inner join(
selectuserId,sex from data.userinfo
where sex = '男' or sex = '女'
group byuserId)t2
on t1.userId = t2.userId
group by sex
4. 统计多次消费的用户，第一次和最后一次消费间隔是多少？
avg(diff)
15.6484
select avg(diff) from (
selectuserId,datediff(max(paidTime),min(paidTime)) as diff from data.orderinfo
whereisPaid = '已支付'
groupby userId having count(*)>1) t
5. 统计不同年龄段，用户的消费金额是否有差异？
年龄分组 单次平均消费 消费金额 消费频次
1 896.1403 91406.31 102
2 804.2091 332138.4 413
3 1062.313 8802324 8286
4 1197.759 12024308 10039
5 1189.193 5409639 4549
6 1079.234 1830381 1696
7 1011.973 600100 593
8 907.9322 44488.68 49
9 32364.17 291277.5 9
selectceil((year(now())-year(birth))/10) asterm,sum(totalprice)/count(t1.userId),sum(totalprice),count(t1.userId) from(
selectuserId, sum(price) as totalprice from data.orderinfo
whereisPaid = '已支付'
groupby userId)t1
inner join(
selectuserId,birth from data.userinfo
where birth > '1901-01-01'
groupby userId)t2
on t1.userId=t2.userId
group by term
6. 统计消费的二八法则，消费的top20%用户，贡献了多少额度
sum(t.t_price)
2.72E+08
select sum(t.t_price) from(
selectuserId,sum(price) as t_price from data.orderinfo
whereisPaid = '已支付'
groupby userId
orderby t_price desc
limit17000)t
