InfluxDB mathematics across measurements

Question

I have two measurements in my InfluxDB, say, mem_used and mem_ available.
I tried to query across those measurements and do a mathematics with

SELECT mean("mem_used_value") / mean("mem_available_value") FROM 
    (
     SELECT mean("value") AS "mem_used_value", 
            mean("value") AS "mem_available_value"
     FROM "dbname"."autogen"."mem_used",
          "dbname"."autogen"."mem_available"
     GROUP BY time(1m)
    )
GROUP BY time(1m)

The result of the query is very weird, and I was wondering if it’s possible for InfluxDB to perform a mathematics across measurements.

I have did some research about this feature and found the issue 3552 Mathematics across measurements is still opening. However, it was requested three years ago.

Is there any approach to do this? any advice is welcome.

Yuri G Yuri G · Accepted Answer · 2018-12-07T16:57:58

There's no JOINs in Influx QL.

Remember pls: that's NOT a relational DB, the query language may look familiar, but it is a totally different thing.

Here's what you can do.

1) The smartest & legit-iest way: shape your measurement properly.

Currently you didn't: there should not be two measurements, but one, like (in line protocol notation)

memusage,host=yourhost,othertag=something,yetanotertag=anything mem_used=123,mem_available=321 yourtimestamp

2) Use Kapacitor to join your measurements altogether.

There, you can do math right in Kapacitor, or simply write the result of the join back into a single measurement and later do your aggregations in plain InfluxQL.

InfluxDB mathematics across measurements

1 Answers