postgresql - 从开/关事件日志计算总事件时间

Question

假设我有一个支持系统。当代理可以处理票证时，他们会自行启动。当它们不再可用时，它们会自行关闭。很简单。每次有人打开或关闭自己时，操作都会存储在数据库表中。像这样:

USERID    ACTION     CREATED
1         ON         2016-01-10 12:00
2         ON         2016-01-10 13:00
2         OFF        2016-01-10 15:00
1         OFF        2016-01-10 17:00
1         ON         2016-01-11 10:00
1         OFF        2016-01-11 11:00

在上面的例子中，用户 1 总共活跃了 6 小时。用户 2 总共活跃了 2 小时。如何编写一个给我这些数据的查询查询，如下所示:

USERID     TOTAL
1          6 hours
2          2 hours

查询还需要处理用户已开启并仍处于事件状态的情况(仅记录了开启操作，没有相应的关闭)。

老实说，我什至不知道从哪里开始。我在想我可能需要创建一系列时间戳，然后用它做一些事情。又或许这只是那种写代码更容易应对的情况。无论如何，任何帮助表示赞赏。

Answer 1

设置:

create table a_table (userid int, action text, created timestamp);
insert into a_table values

(1, 'ON', '2016-01-10 12:00'),
(2, 'ON', '2016-01-10 13:00'),
(2, 'OFF', '2016-01-10 15:00'),
(1, 'OFF', '2016-01-10 17:00'),
(1, 'ON', '2016-01-11 10:00'),
(1, 'OFF', '2016-01-11 11:00'),
(1, 'ON', '2016-01-11 20:00');-- added

想法:

select distinct on (a1.userid, a1.created)
    a1.userid, a1.action, a1.created, 
    a2.userid, a2.action, a2.created, 
    coalesce(a2.created, '2016-01-11 24:00:00')- a1.created as total -- '2016-01-11 24:00:00' = the end of reported period
from a_table a1
left join a_table a2
on a1.userid = a2.userid and a1.action = 'ON' and a2.action = 'OFF' and a1.created < a2.created
where a1.action = 'ON'
order by a1.userid, a1.created, a2.created;

 userid | action |       created       | userid | action |       created       |  total   
--------+--------+---------------------+--------+--------+---------------------+----------
      1 | ON     | 2016-01-10 12:00:00 |      1 | OFF    | 2016-01-10 17:00:00 | 05:00:00
      1 | ON     | 2016-01-11 10:00:00 |      1 | OFF    | 2016-01-11 11:00:00 | 01:00:00
      1 | ON     | 2016-01-11 20:00:00 |        |        |                     | 04:00:00
      2 | ON     | 2016-01-10 13:00:00 |      2 | OFF    | 2016-01-10 15:00:00 | 02:00:00
(4 rows)    

查询:

select userid, sum(total)
from (
    select distinct on (a1.userid, a1.created) 
        a1.userid, 
        coalesce(a2.created, '2016-01-11 24:00:00')- a1.created as total
    from a_table a1
    left join a_table a2
    on a1.userid = a2.userid and a1.action = 'ON' and a2.action = 'OFF' and a1.created < a2.created
    where a1.action = 'ON'
    order by a1.userid, a1.created, a2.created
) s
group by 1
order by 1;

 userid |   sum    
--------+----------
      1 | 10:00:00
      2 | 02:00:00
(2 rows)