postgresql - 每 5 分钟 "Online"个条目

Question

我有这个运动表:

| id | user_id | in_time             | out_time            |
-------------------------------------------------------------
| 1  | 152     | 2015-08-28 15:43:00 | 2015-08-28 15:50:00 |        
| 2  | 123     | 2015-08-28 15:44:00 | 2015-08-28 15:55:00 |        
| 3  | 156     | 2015-08-28 15:50:00 | (null)              |        
| 4  | 211     | 2015-08-28 16:00:00 | 2015-08-28 16:17:00 |        
| 5  | 176     | 2015-08-28 16:21:00 | (null)              |    

我需要知道每五分钟(15:40、15:45、15:50 等)有多少用户在线以绘制图表。这与他们解决的问题不同 here ，因为这里我有一个 in_time 和一个 out_time，这让我可以计算有多少“在线”条目。我知道如何通过使用多个查询轻松地做到这一点:

SELECT * FROM movimentations WHERE out_time > '2015-08-28 15:40:00' AND in_time < '2015-08-28 15:40:00' UNION 
SELECT * FROM movimentations WHERE out_time > '2015-08-28 15:45:00' AND in_time < '2015-08-28 15:45:00' UNION 
SELECT * FROM movimentations WHERE out_time > '2015-08-28 15:50:00' AND in_time < '2015-08-28 15:50:00' UNION 
SELECT * FROM movimentations WHERE out_time > '2015-08-28 15:55:00' AND in_time < '2015-08-28 15:55:00';

当然，我正在寻找更好的解决方案。有没有办法在单个查询中完成？

Answer 1

此查询生成时间点:

select t
from generate_series('2015-08-28 15:40:00', '2015-08-28 16:30:00', '5m'::interval) t

           t            
------------------------
 2015-08-28 15:40:00+02
 2015-08-28 15:45:00+02
 2015-08-28 15:50:00+02
 2015-08-28 15:55:00+02
 2015-08-28 16:00:00+02
 2015-08-28 16:05:00+02
 2015-08-28 16:10:00+02
 2015-08-28 16:15:00+02
 2015-08-28 16:20:00+02
 2015-08-28 16:25:00+02
 2015-08-28 16:30:00+02
(11 rows)

如果您使用 movimentations 和过滤行交叉连接此数据集，您将获得:

with timepoints as (
    select t
    from generate_series('2015-08-28 15:40:00', '2015-08-28 16:30:00', '5m'::interval) t
    )
select t, count(*)
from movimentations
cross join timepoints
where in_time <= t and coalesce(out_time, 'infinity') >= t
group by 1
order by 1;

           t            | count 
------------------------+-------
 2015-08-28 15:45:00+02 |     2
 2015-08-28 15:50:00+02 |     3
 2015-08-28 15:55:00+02 |     2
 2015-08-28 16:00:00+02 |     2
 2015-08-28 16:05:00+02 |     2
 2015-08-28 16:10:00+02 |     2
 2015-08-28 16:15:00+02 |     2
 2015-08-28 16:20:00+02 |     1
 2015-08-28 16:25:00+02 |     2
 2015-08-28 16:30:00+02 |     2
(10 rows)

此结果不包含空 时间点。如果你想要它们，你应该 left join timepoints with the result set:

with timepoints as (
    select t
    from generate_series('2015-08-28 15:40:00', '2015-08-28 16:30:00', '5m'::interval) t
    )
select t.t, c.count
from timepoints t
left join (
    select t, count(*)
    from movimentations
    cross join timepoints t
    where in_time <= t and coalesce(out_time, 'infinity') >= t
    group by 1
    order by 1
    ) c on c.t = t.t;

           t            | count 
------------------------+-------
 2015-08-28 15:40:00+02 |      
 2015-08-28 15:45:00+02 |     2
 2015-08-28 15:50:00+02 |     3
 2015-08-28 15:55:00+02 |     2
 2015-08-28 16:00:00+02 |     2
 2015-08-28 16:05:00+02 |     2
 2015-08-28 16:10:00+02 |     2
 2015-08-28 16:15:00+02 |     2
 2015-08-28 16:20:00+02 |     1
 2015-08-28 16:25:00+02 |     2
 2015-08-28 16:30:00+02 |     2
(11 rows)