配置单元查询中的行减法/循环

wsxa1bj1  于 2021-06-24  发布在  Hive
关注(0)|答案(1)|浏览(332)

我在下面的表格里有一个数据

ID  status      timestamp
ABC login       1/1/2020 12:00
ABC lock        1/1/2020 13:19
ABC unlock      1/1/2020 13:52
ABC Disconnect  1/1/2020 15:52
ABC Reconnect   1/1/2020 15:55
ABC lock        1/1/2020 16:25
ABC unlock      1/1/2020 16:30
ABC logoff      1/1/2020 17:00
ABC login       2/1/2020 12:00
ABC lock        2/1/2020 13:19
ABC unlock      2/1/2020 13:52
ABC lock        2/1/2020 16:22
ABC logoff      2/1/2020 17:00

我需要找出一个雇员在某个特定日期的有效工作时间,而他确实为之工作过。表示状态为“锁定”、“断开连接”时的总时间减去计时的总和。
例如:对于2020年1月1日的员工,他的系统在13:19-13:52(33分钟)和15:52-15:55(3分钟)之间是理想的。因此,在总工作时间中,即5小时(从登录到注销之间的时间),他的有效时间为5小时-36分钟=4小时24分钟。同样适用于2020年2月1日。

2guxujil

2guxujil1#

可以使用窗口函数,然后使用聚合:

select 
    id,
    to_date(timestamp) timestamp_day,
    sum(case when status in ('lock', 'disconnect') then - duration else duration end) / 60 / 60 hours_worked
from (
    select t.*, 
        lead(timestamp) over(partition by id order by timestamp)
            - unix_timestamp(timestamp) status_duration
    from mytable t
) t
group by id, to_date(timestamp)
order by id, to_date(timestamp)

在子查询中,我们使用 lead() 检索“下一步”操作的时间戳,以便计算当前步骤的持续时间。外部查询按雇员和日期进行聚合,并根据业务规则最终计算工作时间。

相关问题