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

/*请统计该公司各岗位员工平均工作时长？
注：如员工未打卡该字段数据会存储为NULL，那么不计入在内；
要求输出：员工岗位类别、平均工作时长（以小时为单位输出并保留三位小数），按照平均工作时长降序排序；
*/

with c1 as
(select
s.post
,round(timestampdiff(second, first_clockin, last_clockin)/3600,3) as work_time
from  attendent_tb a left join  staff_tb s
on a.staff_id = s.staff_id
where a.first_clockin is not null and a.last_clockin is not null
)

select
post
,avg(work_time) as work_hours
from c1
group by post 
order by work_hours desc

timestampdiff!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!