【无标题】

 

SELECT a2.dt,IFNULL(ROUND(a1.newp/a2.num,2),0) uv_left_rate from (SELECT dt,COUNT(dt) newp FROM (SELECT date(min(a.in_time)) dt FROM tb_user_log a join tb_user_log b WHERE a.uid=b.uid and date(a.in_time) = date(b.out_time) -1 GROUP BY a.uid) aa GROUP BY dt) a1 RIGHT JOIN (SELECT date(in_time) dt,count(uid) num FROM tb_user_log where date(in_time) in (SELECT date(min(in_time)) FROM tb_user_log GROUP BY uid) GROUP BY dt) a2 on a1.dt=a2.dt

 大致思路：先找出每日的活跃用户记为A，再找出每日的总人数其中如果没有包含活跃人数则丢弃记为B,最后A/B获得结果

SELECT order_num,prod_id,quantity FROM orderitems where quantity >=100 and prod_id in ("BR01","BR02","BR03")