postgresql - Postgres-基于列的值的不同聚合

我有一个表，允许用户存储devices的不同度量。devices可以是不同的类型（考虑物联网），并且具有不同的度量。
例如，车队跟踪装置可能有speed，distanceTravelled，tripDuration，而农业传感器可能有temperature，humidity。
请注意，在单个资产中不一定有相同数量的度量。例如，tripDuration可能每天只更新几次，而speed可能每分钟更新两次。

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

我目前正在开发一个允许用户在其设备上运行自定义历史报告的系统。这些报告是聚合报告。
例如，车队跟踪客户可能希望创建一个报告，显示当前周内他的每个设备：
最大速度：MAX(speed)
平均速度：AVG(speed)
出行次数：COUNT(tripDuration)
平均行程长度：AVG(tripDuration)
等
我将如何查询这样的东西（希望有点效率）？努力想办法接近它。

最佳答案

使用FILTER clause

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