select it , round(sum(case when pd1+pd2 >=1 then 1 else 0 end)/count(*),2) from ( select distinct t1.uid , date_format(t1.in_time,'%Y-%m-%d') it, case when t2.id >0 then 1 else 0 end pd1, case when t3.id >0 then 1 else 0 end pd2, case when ...