/*按部门分组，百分数，保留1位，降序*/

with t1 as (
select staff_id,
    if( timestampdiff (second, first_clockin, last_clockin) / 3600 > 9.5,1,0) as is_time
from attendent_tb
)

select department,concat( round(100* avg(is_time),1),'%') as ratio
from t1
left join staff_tb s on s.staff_id	=t1.staff_id	
group by department 
order by ratio desc