这是我的数据.
username | building | action | timestamp |
---|---|---|---|
user-1 | building-1 | IN | 2024-04-10 01:00:00.000 |
user-1 | building-1 | OUT | 2024-04-10 02:00:00.000 |
user-1 | building-1 | IN | 2024-04-10 02:30:00.000 |
user-1 | building-1 | OUT | 2024-04-10 04:00:00.000 |
user-1 | building-1 | IN | 2024-04-11 10:00:00.000 |
user-1 | building-1 | OUT | 2024-04-11 11:00:00.000 |
user-2 | building-2 | IN | 2024-04-10 08:00:00.000 |
user-2 | building-2 | OUT | 2024-04-10 09:00:00.000 |
user-2 | building-3 | OUT | 2024-04-11 02:30:00.000 |
user-2 | building-4 | IN | 2024-04-11 04:00:00.000 |
user-2 | building-1 | IN | 2024-04-12 10:00:00.000 |
user-2 | building-1 | OUT | 2024-04-12 11:00:00.000 |
我需要计算刷卡和刷卡时间.有时我们收到部分记录,我们还需要包括这些记录.
我的预期输出是:
随后使用lead
、lag
等窗口功能.使用这些函数后,我无法继续获取此输出.
附表数据:
select 'user-1' as username,'building-1' as building,'IN' as action,'2024-04-10 01:00:00'::timestamp as timestamp
union all
select 'user-1' as username,'building-1' as building,'OUT' as action,'2024-04-10 02:00:00'::timestamp as timestamp
union all
select 'user-1' as username,'building-1' as building,'IN' as action,'2024-04-10 02:30:00'::timestamp as timestamp
union all
select 'user-1' as username,'building-1' as building,'OUT' as action,'2024-04-10 04:00:00'::timestamp as timestamp
union all
select 'user-1' as username,'building-1' as building,'IN' as action,'2024-04-11 10:00:00'::timestamp as timestamp
union all
select 'user-1' as username,'building-1' as building,'OUT' as action,'2024-04-11 11:00:00'::timestamp as timestamp
union all
select 'user-2' as username,'building-2' as building,'IN' as action,'2024-04-10 08:00:00'::timestamp as timestamp
union all
select 'user-2' as username,'building-2' as building,'OUT' as action,'2024-04-10 09:00:00'::timestamp as timestamp
union all
select 'user-2' as username,'building-3' as building,'OUT' as action,'2024-04-11 02:30:00'::timestamp as timestamp
union all
select 'user-2' as username,'building-4' as building,'IN' as action,'2024-04-11 04:00:00'::timestamp as timestamp
union all
select 'user-2' as username,'building-1' as building,'IN' as action,'2024-04-12 10:00:00'::timestamp as timestamp
union all
select 'user-2' as username,'building-1' as building,'OUT' as action,'2024-04-12 11:00:00'::timestamp as timestamp
)
select * from _data