条件时间序列数据聚合

Question

我有一个关系数据库（postgres），其中包含一个保存时间序列指标的表。每行由 - obj_id, metric_id, timestamp, value

假设我有 3 个感兴趣的代码指标 - 1、4 、5。我想过滤掉所有对象，对于相同的时间戳（假设所有指标的时间戳都是固定间隔的），指标 1 <10 和（指标 4 + 指标 5）<10 具有此事件发生的特定时间戳。

一个更具体的例子：

obj_id       metric_id         timestamp        value
------------------------------------------------------
1             1                83827             9
1             4                83827             2
1             5                83827             1
2             1                73261             11
2             4                73261             2
2             5                73261             5
1             1                92381             24
1             4                92381             10
1             5                92381             100
2             1                38239             7
2             4                38239             3
2             5                38239             4

预期结果将是：

obj_id     timestamp
---------------------
  1         83827
  2         38239

我正在尝试创建一个有效的查询来做。这就是我的想法，以便获得相同时间戳的 4 + 5 的总和，但我不确定将这些查询粘合在一起的最佳方法是什么：

SELECT obj_id, timestamp, sum(value) AS x
FROM metric
WHERE metric_id = 4 OR metric_id = 5
group by obj_id, timestamp

我不确定如何将指标 1（我们应该单独查询）添加到此查询中，然后按 obj_id 和 timestamp 过滤结果。

我想过也许使用自连接，通过时间戳连接同一表的两个内部选择。

Answer 1

SQL Fiddle

从 boolean 到 integer 的铸造产生 0 或 1

select obj_id, timestamp
from metric
where metric_id in (1,4,5)
group by obj_id, timestamp
having
    sum(value * (metric_id in (4,5))::integer) < 10
    and
    sum(value * (metric_id = 1)::integer) < 10

Answer 2

也许这可以做得更好，但这感觉很整洁（需要 9.4+）：

WITH x AS(
  SELECT 
    obj_id,
    timestamp,
    min(value) FILTER (WHERE metric_id = 1) as metric1,
    min(value) FILTER (WHERE metric_id = 4) as metric4,
    min(value) FILTER (WHERE metric_id = 5) as metric5
  FROM metric
  GROUP BY obj_id, timestamp
)
  SELECT obj_id, timestamp
  FROM x
  WHERE metric1 < 10
    AND (metric4 + metric5) < 10