postgresql - 如何在 Postgres 中每 5 分钟获取 1 行？

Question

我有一个表，有三列。

id、时间和值

在 Postgres 中，我如何获得每 5 分钟范围内只有一个数据的每个 Id？

例如:

id    time    value 

1     03:11    test
1     07:13    test
1     12:11    test
2     02:11    test
2     07:11    test

这是我的版本，但只有一小时......

select
  distinct on("id")
  "id",
  time::time,
  value
from (
select
 "id",
 time::time,
 value,
dense_rank() over (
  partition by "id"
  order by
  to_char(time,'HH')::int,
  (
    to_char(time,'MI')
  )::int/5
)
from my_table
where time between '2018-07-03 00:00:00' and '2018-07-03 01:00:00' and "id" = 'XXXXX'
) as res
order by "id";

结果:

id   time  value
1     03:05   14

但结果是 10 行.. 可能更多

Answer 1

下面的查询将产生所需的结果。

with res(id,time,value,rnk) as (select id, time, value,dense_rank() over (  partition by id  order by   to_char(time,'HH24'),  (    to_char(time,'MI')  )::int/5)from your_table)select  distinct on(id,rnk)  id,  to_char(time,'HH24:MI'),  valuefrom resorder by id,rnk