Question 229987 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: gather the last entry for a specific metric from multiple fields

Context

I have this table:

Time Metric A Metric B Value
A A A 1
A A B 2
A A C 1
B A A 1
B A B 2
B A C 1

I want to get the last and summarize the table by Metric A, but sometimes Time goes bonkers and GROUP BY Time is not an option.

What I want is to get the last value of Metric B for each Metric A… Any tips on how to make sure I get the last inserted value for Metric B for Metric A?

Desired output

Time Metric A Metric B Value
A A A 1
A A B 2
A A C 1
Tags:

2

Answers


  1. 0

    You could use rank() or dense_rank() to achieve what you’re looking for.

    create table table1 (
  time timestamp, 
  metric_a varchar(1), 
  metric_b varchar(1), 
  value integer
  );
  
insert into table1 values 
(current_timestamp, 'A','A',1),
(current_timestamp, 'A','B',2),
(current_timestamp, 'A','C',1),
(current_timestamp - interval '10 minutes', 'A','A',1), 
(current_timestamp - interval '10 minutes', 'A','B',2), 
(current_timestamp - interval '10 minutes', 'A','C',1), 
(current_timestamp, 'B','A',2),
(current_timestamp, 'B','B',3),
(current_timestamp, 'B','C',4),
(current_timestamp - interval '10 minutes', 'B','A',2), 
(current_timestamp - interval '10 minutes', 'B','B',3), 
(current_timestamp - interval '10 minutes', 'B','C',4);

    select time, metric_a, metric_b, value 
from (
  select *, 
   dense_rank() over (partition by metric_a, metric_b order by time desc) as rnk
  from table1
  )z
where rnk = 1;
    time metric_a metric_b value
    2023-08-14T15:13:49.623Z A A 1
    2023-08-14T15:13:49.623Z A B 2
    2023-08-14T15:13:49.623Z A C 1
    2023-08-14T15:13:49.623Z B A 2
    2023-08-14T15:13:49.623Z B B 3
    2023-08-14T15:13:49.623Z B C 4

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    Using a subquery:

    select t.* from tbl t where t.time = (select max(t1.time) 
   from tbl t1 where t1.metric_a = t.metric_a and t1.metric_b = t.metric_b)

    See fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search