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

# 请统计该公司各岗位员工平均工作时长？

# 注：如员工未打卡该字段数据会存储为NULL，那么不计入在内；

# 要求输出：员工岗位类别Post、平均工作时长work_hours（以小时为单位输出并保留三位小数），按照平均工作时长降序排序；round(,3)

# 先计算出每个员工的工作时长

# 写法一：

# select

# s.post,

# round(avg(a.hours),3) as work_hours

# from staff_tb s

# join (

# select *,

# round((unix_timestamp(last_clockin)-unix_timestamp(first_clockin))/3600,4) as hours

# from attendent_tb

# where first_clockin is not null and last_clockin is not null

# ) a

# on s.staff_id = a.staff_id

# group by s.post

# order by work_hours desc;

# 上面的可以通过，但是为什么用窗口函数就是错的，本地是对的，但在这上边就是无法通过，看到的能帮忙解析一下吗：

# select

# distinct s.post,

# round(avg(a.hours) over(partition by post),3) as work_hours

# from staff_tb s

# join (

# select *,

# round((unix_timestamp(last_clockin)-unix_timestamp(first_clockin))/3600,4) as hours

# from attendent_tb

# where first_clockin is not null and last_clockin is not null

# ) a

# on s.staff_id = a.staff_id

# order by work_hours desc;

# # 写法二：二三都是网友的

# select post, round(avg(timestampdiff(minute,first_clockin,last_clockin))/60,3) as work_hours

# from staff_tb

# join attendent_tb using(staff_id)

# where first_clockin is not null and last_clockin is not null

# group by post

# order by work_hours desc

# 写法三：

with t1 as(

select s.staff_id,

    post,

    -- 注意下这个函数的用法，精确到秒，如果是hour不准确

    TIMESTAMPDIFF(second,first_clockin,last_clockin) / 3600 total_hours

from

    staff_tb s

    join attendent_tb a on s.staff_id = a.staff_id

)

select

post,

round(avg(total_hours),3) work_hours

from t1

group by post

order by work_hours desc