postgresql在分钟之间获取视图数字

Question

我有一张类似的表：

CREATE TABLE test (
   id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass), 
   my_time timestamp without time zone,
  CONSTRAINT test_pkey PRIMARY KEY (id)
);
INSERT INTO test(my_time) values 
 ('2017-04-05 16:09:10'),('2017-04-05 16:09:13'),
 ('2017-04-05 16:09:18'),('2017-04-05 16:10:44'),
 ('2017-04-05 16:11:52'),('2017-04-05 16:14:09'),
 ('2017-04-05 16:19:13'),('2017-04-05 16:25:28'),
('2017-04-05 16:25:58'),('2017-04-05 16:26:07'),
('2017-04-05 16:29:12'),('2017-04-05 16:35:15'),
('2017-04-05 16:39:19'),('2017-04-05 16:40:06'),
('2017-04-05 16:41:05'),('2017-04-05 16:43:14'),
('2017-04-05 16:46:07'),('2017-04-05 16:49:35'),
('2017-04-05 16:53:18'),('2017-04-05 16:58:28'),
('2017-04-05 17:07:50');

我会看到一分钟的开始与my_time之间的数字在哪里计算：

：

my_time             | nb_in_the minute | nb_in_the_2_minutes|
2017-04-05 16:09:10 | 1                | 1                  |
2017-04-05 16:09:13 | 2                | 2                  |
2017-04-05 16:09:18 | 3                | 3                  |
2017-04-05 16:10:44 | 1                | 4                  |
2017-04-05 16:11:52 | 1                | 2                  |

我尝试过，但失败了，我不知道该怎么做

Answer 1

与第五行中的预期不同：

t=# with g(s) as (select generate_series('2017-04-05 16:00:00','2017-04-05 18:07:50','2 minutes'::interval) s)
select my_time,row_number() over (partition by date_trunc('minute',my_time)) m1
, row_number() over (partition by s) m2
from test
join g on s >= date_trunc('minute',my_time) and s< date_trunc('minute',my_time) + '2 minutes'::interval;
       my_time       | m1 | m2
---------------------+----+----
 2017-04-05 16:09:10 |  1 |  1
 2017-04-05 16:09:13 |  2 |  2
 2017-04-05 16:09:18 |  3 |  3
 2017-04-05 16:10:44 |  1 |  4
 2017-04-05 16:11:52 |  1 |  1
 2017-04-05 16:14:09 |  1 |  1
 2017-04-05 16:19:13 |  1 |  1
 2017-04-05 16:25:28 |  1 |  1
 2017-04-05 16:25:58 |  2 |  2
 2017-04-05 16:26:07 |  1 |  3
 2017-04-05 16:29:12 |  1 |  1
 2017-04-05 16:35:15 |  1 |  1
 2017-04-05 16:39:19 |  1 |  1
 2017-04-05 16:40:06 |  1 |  2
 2017-04-05 16:41:05 |  1 |  1
 2017-04-05 16:43:14 |  1 |  1
 2017-04-05 16:46:07 |  1 |  1
 2017-04-05 16:49:35 |  1 |  1
 2017-04-05 16:53:18 |  1 |  1
 2017-04-05 16:58:28 |  1 |  1
 2017-04-05 17:07:50 |  1 |  1
(21 rows)

Answer 2

可能不是优雅，但它是工作的：

SELECT a0.my_time,m1 nb_in_min,m2 nb_ni_2_min FROM (
select aa0.my_time
,count(bb0.t)m1
from test aa0
join (select my_time t from test )bb0
on bb0.t BETWEEN date_trunc('minute',aa0.my_time) AND aa0.my_time 
group by aa0.my_time
)as a0
JOIN (
select aa.my_time
,count(bb.t)m2
from test aa
join (select my_time t from test )bb
on bb.t BETWEEN date_trunc('minute',aa.my_time)-'2 minutes'::INTERVAL AND aa.my_time 
group by aa.my_time
)as a1
ON a0.my_time=a1.my_time    
order by a1.my_time
;