CODEWITHSUNDEEP
postgresqlaggregate-functions
NRaf
NRaf

Reputation: 7539

Postgres - Different aggregations based on values of a column

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:

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

Upvotes: 0

Views: 82

Answers (2)

klin
klin

Reputation: 121494

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

Upvotes: 2

William Prigol Lopes
William Prigol Lopes

Reputation: 1889

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.

Upvotes: 0

Related Questions