CODEWITHSUNDEEP
postgresqltime-seriesaggregate-functions
Avi
Avi

Reputation: 21866

Conditional time-series data aggregation

I have a relational DB (postgres) with a table that holds time-series metrics. Each row is comprised of - obj_id, metric_id, timestamp, value

Let's assume I have 3 metrics of interest with codes - 1, 4 ,5. I would like to filter out all the objects that, for the same timestamp (let's assume the timestamp for all metrics are in fixed intervals), have metric 1 < 10 and (metric 4 + metric 5) < 10 with the specific timestamp that this event happened.

A more concrete example:

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

The expected result would be:

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

I'm trying to create an efficient query to do. That's what I had in mind in order to get the sum of 4 + 5 for the same timestamp but I'm not sure what would be the best way to glue these queries together:

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

I'm not sure how to add to this query metric 1 (which we should query separately) and then filter out the results by obj_id and timestamp.

I thought about maybe using a self join, joining two inner selects of the same table by the timestamp.

Upvotes: 2

Views: 354

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

SQL Fiddle

The cast from boolean to integer yields 0 or 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

Upvotes: 4

Robins Tharakan
Robins Tharakan

Reputation: 2473

Probably this could be done better, but this felt neat to comprehend (requires 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

Upvotes: 0

Related Questions