How to aggregate date by minutes in postgres having latest row-CodePudding

I am trying to aggregate data by minute while having multiple records for certain minute, but I am interested in only last record. For example :

 id |        date         |          data                  
---- --------------------- ----------------------
 1  | 2021-12-22 16:14:03 | {"x": 1, "y": 2}
 2  | 2021-12-22 16:14:13 | {"x": 5, "y": 4}
 3  | 2021-12-22 16:14:23 | {"x": 6, "y": 5}
 4  | 2021-12-22 16:15:33 | {"x": 7, "y": 6}
 5  | 2021-12-22 16:15:48 | {"x": 8, "y": 10}

What I want to achieve is :

 id |        date      |          data                  
---- ------------------ ---------------------
 3  | 2021-12-22 16:14 | {"x": 6, "y": 5}
 5  | 2021-12-22 16:15 | {"x": 8, "y": 10}

I would like to have only last record from certain minute. I tried grouping by "formatted" date, but I don't know how to get only the latest row :

SELECT to_char(date, 'YYYY-MM-DD HH24:MI') as date_formatted ... FROM table GROUP BY 1

CodePudding user response：

You can use a window function to achieve this:

select id, 
       to_char("date", 'yyyy-mm-dd hh24:mi') as date, 
       data
from (
   select id, "date", data, 
          dense_rank() over (partition by date_trunc('minute', "date") order by "date" desc) as rnk
   from the_table
) t
where rnk = 1
order by id;

The date_trunc() puts all rows with the same minute into the same partition (group) and the order by "date" desc is then used to pick the latest from those.

Online example