postgrehow使用sql查找人员的活动空闲时间

Question

worker表，用于跟踪员工在某一天所做的所有工作。你如何查询那个表来找出他没有任何工作的时间——时间和他空闲时的时间戳。

因此，当他拿起第一件作品，然后又拿起另一件，然后把它们都放下时，桌子上可能会有一个记录。过了一会儿，他又拿起了一些，这些又记在了表上。

那么，当他没有任何工作时，你如何找到数据

表：worker

worker_id | task_id |      start_ts       |       end_ts        
-----------|---------|---------------------|--------------------- 
1 |       1 | 2019-27-10 03:14:07 | 2019-27-10 03:20:07 
1 |       2 | 2019-27-10 03:19:07 | 2019-27-10 03:25:07 
1 |       3 | 2019-27-10 03:18:07 | 2019-27-10 05:20:07 
2 |       1 | 2019-27-10 06:14:07 | 2019-27-10 06:20:07 
3 |       2 | 2019-27-10 06:19:07 | 2019-27-10 06:25:07 
4 |       3 | 2019-27-10 06:18:07 | 2019-27-10 07:20:07 

Answer 1

您可以将数据拆分为各个时间戳。然后将+1指定为开始时间，将-1指定为结束时间(加一秒(。这个值的累计和告诉你一个人在那个时候开始了多少任务。

所以最后一步是过滤值为0:的地方

with t as (
select worker_id, start_ts as ts, 1 as inc
from work
union all
select worker_id, end_ts + interval '1 second' as ts, -1 as inc
from work
),
t2 as (
select t.worker, t.ts,
sum(sum(t.inc)) over (partition by t.worker_id) as num_tasks,
lead(t.ts) over (partition by t.worker, t.ts) as next_ts
from t
group by t.worker, t.ts
)
select t2.*
from t2
where num_tasks = 0;