Search code examples
influxdbchronograf

Influx: doing math the same fields in different groups

I have InfluxDB measurement currently set up with following "schema": 

+----+-------------+-----------+
| ts | cost(field) | type(tag) |
+----+-------------+-----------+
|  1 | 10          | 'a'       |
|  1 | 20          | 'b'       |
|  2 | 12          | 'a'       |
|  2 | 18          | 'b'       |
|  2 | 22          | 'c'       |
+------------------+-----------+

I am trying to write a query that will group my table by timestamp and get a delta between field values of two different tags. If I want to get delta between tag 'a' and tag 'b', it will give me following result (please not that I ignore tag 'c'):

+----+-----------+------------+
| ts | type(tag) | delta_cost |
+----+-----------+------------+
|  1 | 'a'       | 10         |
|  2 | 'b'       |  6         |
+----+-----------+------------+

Is it something Influx can do or am I using the wrong tool?

Solution

  • Just managed to answer my own question. While one of the obvious ways would be performing self-join, Influx does not support joins anymore. We can, however, use nested selects in a following format: 

    SELECT MEAN(cost_a) - MEAN(cost_b) as delta_cost
FROM
    (SELECT cost as cost_a, tag, tablename where tag='a'),
    (SELECT cost as cost_b, tag, tablename where tag='b')
GROUP BY time(60s)

    Since I am getting my data every 60 seconds anyway, and I have a guarantee of just one point per tag per 60 seconds, I can use GROUP BY and take MEAN without any problems