MySQL的基本操作——练习

基础知识MySQL的基本操作_x qing的博客-CSDN博客

本文将完成以下练习：

• 统计不同月份的下单人数？
• 统计用户三月份的回购率和复购率？
• 统计男女用户的消费频次是否有差异？
• 统计多次消费的用户，第一次和最后一次消费间隔是多少?
• 统计不同年龄段，用户的消费金额是否有差异？
• 统计消费的二八法则，消费的top20%用户，贡献了多少额度 ？

不同月份的下单人数

SELECT month(paidTime), count(userId),count(distinct userId) FROM new_schema.order_info
where paid = "已支付"
group by month(paidTime);

男、女用户消费频次

select sex ,avg(t2) from (
select o.userId,sex,count(1) as t2 from new_schema.order_info as o
inner join (
SELECT * FROM new_schema.user_info
where sex <> "") as t
on o.userId=t.userId
group by userId,sex) as t1
group by sex;

20%的人贡献额度

select count(userId),sum(total)  from(
   SELECT userId,sum(price) as total FROM new_schema.order_info
   where paid ="已支付"
   group by userId
   order by sum(price) desc
   limit 17000) as t
;

不同年龄段消费金额差异 

select age,avg(t2) from (
   select o.userId,age,count(o.userId) as t2 from new_schema.order_info as o
 inner join (
   SELECT userId,ceil((year(now())-year(birth))/10) as age FROM new_schema.user_info
   where birth > "1901-00-00") as t
 on t.userId=o.userId
   group by userId,age) as t1
group by age；

第一次消费和最后一次消费间隔

SELECT userId,datediff(max(paidTime),min(paidTime)) FROM new_schema.order_info
where paid="已支付"
group by userId having count(1)>1；

回购率

select t1.m,count(t1.m),count(t2.m) from (
SELECT userId,date_format(paidTime,"%Y-%m-01") as m FROM new_schema.order_info
where paid ="已支付"
group by userId,date_format(paidTime,"%Y-%m-01")) as t1
left join (SELECT userId,date_format(paidTime,"%Y-%m-01") as m FROM new_schema.order_info
where paid ="已支付"
group by userId,date_format(paidTime,"%Y-%m-01")) as t2
on t1.userId=t2.userId and t1.m = date_sub(t2.m, interval 1 month)
group by t1.m；

复购率

select count(userId),count(if(ct>1,1,null)), count(if(ct>1,1,null))/count(userId) as fg  from(
SELECT userId, count(userId) as ct FROM new_schema.order_info
where month(paidTime) = 3 
group by userId) as t;