mmmy

show processlist;
 show status;
 
 alter table star.user_info change iidenx idenx int primary key;
 alter table star.order_info change iidenx idenx2 int primary key;

 desc star.order_info;
  -- 求分组条件下均值，逻辑实现   男总购买数/男性总数   女总购买数/女性总数
 select gender,avg(cc) 
 from(
     select userid,gender,count(*)as cc
     from star.order_info as t1
     left join
     (select * from
     star.user_info) as t2
     on t1.userid=t2.idenx
     where t2.gender is not null
           and paytype='已支付'
     group by userid,gender) as o
     group by gender;
 --消费间隔   
 select userid,date_format(max(paytime),'%Y%m%d'),date_format(min(paytime),'%Y%m%d'),datediff(max(paytime),min(paytime))
 from star.order_info
 where paytype='已支付'
 group by userid
 having count(1)>1;
 
 --不同年龄段消费金额
 select age,avg(summoney) from
 (    select userid,ceil((year(now()) - year(birth))/10) as age,sum(money)as summoney
     from
           (select * from
           star.order_info as t1
           inner join
           (select * from star.user_info) as t2
           on t1.userid=t2.idenx
           where birth > '1901-00-00' and paytype='已支付')as u
     group by userid) as o
 group by age;
 
 -----28法则  20% 用户消费
 select sum(total)from
   (select userid,sum(money)as total
   from
   star.order_info
   where paytype='已支付'
   group by userid
   order by total desc
   limit 17000) as t;