with tiaojian as
(
select uid,in_time as pday from 实录表
union all
select uid,out_time as pday from 实录表
)
select
t.pday,
sum(case when t.m=1 then 1 else 0 end),
round(
sum(case when t.m=1 then 1 else 0 end)/count(distinct t.uid),2)
from(
select
uid,
pday,
row_number()over(partition by uid order by pday asc) as m
from tiaojian
) as t
group by t.pday