mysql自检很慢_【MYSQL】练习题自测

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