题解 | 统计各岗位员工平均工作时长

# 各岗位 平均工作时长
with
t1 as(
    select
        staff_id,
        post,
        timestampdiff(second,first_clockin,last_clockin) / 3600 as worktime
    from
        attendent_tb left join staff_tb using(staff_id)
    where
        first_clockin is not null
)
,t2 as(
    select distinct
        post,
        avg(worktime)over(partition by post) as work_hours
    from
        t1
)

select * from t2 order by work_hours desc