Postgres - Different aggregations based on values of a column

Question

I have a table that allows users to stores different metrics for devices. devices can be different types (think IoT) and have different metrics.

e.g. a fleet tracking device might have speed, distanceTravelled, tripDuration whilst agricultural sensor might have temperature, humidity.

Note that there isn't necessarily the same amount of metrics within a single asset. e.g. tripDuration might only be updated a handful of times per day whereas speed could be updated twice a minute.

create table metric_history
(
  device_id     uuid not null,
  timestamp     timestamp,
  metric_name   text,
  value         double precision
);

I'm currently working on a system to allow users to run custom historical reports on their devices. These reports are aggregate reports.

For example, a fleet tracking customer might want to create a report showing for each of his devices for the current week:

Max Speed: MAX(speed)
Average Speed: AVG(speed)
Number of Trips: COUNT(tripDuration)
Average Trip Length: AVG(tripDuration)
etc

How will I go about querying something like this (hopefully somewhat efficiently)? Struggling to come up with a way to approach it.

So just SELECT device_id, whatever_aggreate(value) FROM metric_history WHERE metric_name = '…' GROUP BY device_id? — Bergi
– Bergi, Commented Nov 7, 2019 at 2:16

klin · Accepted Answer · 2019-11-07 02:59:30Z

2

Use the FILTER clause, e.g.:

select
    device_id,
    max(value) filter (where metric_name = 'speed') as max_speed,
    avg(value) filter (where metric_name = 'speed') as avg_speed,
    count(value) filter (where metric_name = 'tripDuration') as number_of_trips
from metric_history
group by device_id

answered Nov 7, 2019 at 2:59

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

William Prigol Lopes · Accepted Answer · 2019-11-07 02:48:46Z

0

If you want to get all values in same select you can do that:

SELECT MAX(CASE WHEN device_id=X THEN value ELSE 0 END) AS speed,
       AVG(CASE WHEN device_id=Y THEN value ELSE null END) AS tripDuration
  FROM metric_history

P.S.: When you discard a value in AVG, you need to set null, to not consider the element in average.

answered Nov 7, 2019 at 2:48

William Prigol Lopes

1,90519 silver badges33 bronze badges

2 Comments

Bergi Over a year ago

Better just MAX(value) FILTER (WHERE device_id=X) and AVG(value) FILTER (WHERE device_id=Y). Btw the advice you gave about avg would hold the same for other aggregates, wouldn't it?

William Prigol Lopes Over a year ago

Interesting approach, the filter function. About the advice, yes. Just supposed that the speed wouldn't be negative. But, looking for the generalist perspective, your perspective about the nulls is more reliable.

Collectives™ on Stack Overflow

Postgres - Different aggregations based on values of a column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related