Influxdb: Issue with operating on multiple subqueries

[csconnell]

I have the following query, where I have two different measures that I want to determine a percentage difference between. The problem seems to be that in the top level select, I can only operate on one of the subquery returns. My explanation is likely subpar, so an example:

SELECT (("oneday" - "current")/"oneday") as "difference" FROM
(SELECT "StaleConnections"/"TotalConnections" as current FROM "missions" WHERE "measure"='loading' AND $timeFilter),
(SELECT "StaleConnections"/"TotalConnections" as oneday FROM "missions_lookback_1d" WHERE "measure"='loading' AND $timeFilter)

It returns nothing at all in Grafana, however, if I replace one of the aliases with an actual number like below, then it works.

(when I make the call straight to influx, I get back two sets of results, one for "missions" and one for "missions_lookback_1d", but not the calculated difference between the two that I want.)

SELECT ((.067-current)/.067) as "difference"
FROM (SELECT "StaleConnections"/"TotalConnections" as current FROM "missions" WHERE "measure"='loading' AND $timeFilter),
(SELECT "StaleConnections"/"TotalConnections" as oneday FROM "missions_lookback_1d" WHERE "measure"='loading' AND $timeFilter)

Then I'll get a value.

If, as in the second query example (that works) I use "current", which comes from mission, my result will be named "missions.difference".

If instead I flip it so that I'm not using "current", but instead use "oneday", which comes from missions_lookback_id, I'll get a result that will be named "missions_lookback_1d.difference"

I've used several different versions of Influx for this, although 1.5.2 is the most recent I've tried.

Is there another way to make this work? Oh, and the examples above come from Grafana, which is why the queries have those timefilters. I can give you the raw queries if needed.

[csconnell]

Did a little more digging here and showing some data too:

If I query a single series for what I call the current data, I get this:

SELECT sum("StaleConnections")/sum("TotalConnections") AS "current" FROM "monitor"."autogen"."missions" WHERE "measure"='loading' AND time > now() - 1h GROUP BY time(5m) FILL(null)

name: missions

time | current
------------ | -------------
2018-06-21T13:50:00Z | 0.07766990291262135
2018-06-21T13:55:00Z | 0.07686084142394822
2018-06-21T14:00:00Z | 0.08333333333333333
2018-06-21T14:05:00Z | 0.08009708737864078
2018-06-21T14:10:00Z | 0.07928802588996764
2018-06-21T14:15:00Z | 0.07928802588996764
2018-06-21T14:20:00Z | 0.0784789644012945
2018-06-21T14:25:00Z | 0.07766990291262135
2018-06-21T14:30:00Z | 0.07766990291262135
2018-06-21T14:35:00Z | 0.07766990291262135
2018-06-21T14:40:00Z | 0.07766990291262135
2018-06-21T14:45:00Z | 0.07686084142394822

I can also select from the other series, the result of which I call "lookback":
SELECT sum("StaleConnections")/sum("TotalConnections") AS "lookback" FROM "monitor"."autogen"."missions_lookback_1d" WHERE "measure"='loading' AND time > now() - 1h GROUP BY time(5m) FILL(null)

name: missions_lookback_1d

time | lookback
------------ | -------------
2018-06-21T13:50:00Z | 0.07253463732681337
2018-06-21T13:55:00Z | 0.07253463732681337
2018-06-21T14:00:00Z | 0.07905460472697637
2018-06-21T14:05:00Z | 0.07579462102689487
2018-06-21T14:10:00Z | 0.07579462102689487
2018-06-21T14:15:00Z | 0.07579462102689487
2018-06-21T14:20:00Z | 0.07579462102689487
2018-06-21T14:25:00Z | 0.07497962510187449
2018-06-21T14:30:00Z | 0.07416462917685411
2018-06-21T14:35:00Z | 0.07334963325183375
2018-06-21T14:40:00Z | 0.07253463732681337
2018-06-21T14:45:00Z | 0.07253463732681337

When I make the full request to try and operate on the subselects (actually, just return them), it doesn't work. I'm scoped to whatever the first subselect is:

SELECT sum(lookback) as "lb", sum(current) as "cur" FROM (SELECT sum("StaleConnections")/sum("TotalConnections") AS "lookback" FROM "monitor"."autogen"."missions_lookback_1d" WHERE "measure"='loading' AND time > now() - 1h GROUP BY time(5m) FILL(null)),(SELECT sum("StaleConnections")/sum("TotalConnections") AS "current" FROM "monitor"."autogen"."missions" WHERE "measure"='loading' AND time > now() - 1h GROUP BY time(5m) FILL(null)) WHERE time > now() - 1h GROUP BY time(5m) FILL(null)

name: missions_lookback_1d

time | lb | cur
------------ | ------------- | -------------
2018-06-21T13:45:00Z | 0.07253463732681337
2018-06-21T13:50:00Z | 0.07253463732681337
2018-06-21T13:55:00Z | 0.07253463732681337
2018-06-21T14:00:00Z | 0.07905460472697637
2018-06-21T14:05:00Z | 0.07579462102689487
2018-06-21T14:10:00Z | 0.07579462102689487
2018-06-21T14:15:00Z | 0.07579462102689487
2018-06-21T14:20:00Z | 0.07579462102689487
2018-06-21T14:25:00Z | 0.07497962510187449
2018-06-21T14:30:00Z | 0.07416462917685411
2018-06-21T14:35:00Z | 0.07334963325183375
2018-06-21T14:40:00Z | 0.07253463732681337

[ezeheinke]

Hi! I am having the same issue, did you came back with a solution? Is it maybe because the subqueries give you same time, and the influx just pick one of them ?

[csconnell]

@ezeheinke I did not come up with a very elegant solution. What I ended up doing to solve the problem was creating three more measurements. So previously I had a measure called missions, now I have

• missions
• missions_lookback_1d
• missions_lookback_7d
• missions_lookback_30d

I get those simply by projecting the timestamp 1,7, and 30 days forward with the exact same data that I passed in for today (so, same data for 4 measures / series). Then I have another process that pulls that data back out of Influx using the following queries that perform the percentage processing:

SELECT last("StaleConnections")/ last("TotalConnections") AS "connections" FROM "monitor"."autogen"."missions" WHERE time > now()- 5m GROUP BY time(5m) FILL(null);

SELECT last("StaleConnections")/ last("TotalConnections") AS "connections_1d" FROM "monitor"."autogen"."missions_lookback_1d" WHERE time > now()- 1m GROUP BY time(5m) FILL(null);

SELECT last("StaleConnections")/ last("TotalConnections") AS "connections_7d" FROM "monitor"."autogen"."missions_lookback_7d" WHERE time > now()- 1m GROUP BY time(5m) FILL(null);

SELECT last("StaleConnections")/ last("TotalConnections") AS "connections_30d" FROM "monitor"."autogen"."missions_lookback_30d" WHERE time > now()- 1m GROUP BY time(5m) FILL(null)

and then saves that back into Influx in a new measure called "staleness".

Not elegant at all, requires too much data storage and a second set of processing I wish I didn't have, but it works.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[PyvesB]

When I make the full request to try and operate on the subselects (actually, just return them), it doesn't work. I'm scoped to whatever the first subselect is

Unfortunately, I've bumped into the same problem. Trying to use values from the second or any other subquery does not work.

[mnicky]

Same with me.

E.g. this query returns results for the first field (a) and nulls for the second field (b):

SELECT sum("a"), sum("b") FROM
(SELECT "count" AS "a" FROM "foo" WHERE $timeFilter),
(SELECT "count" AS "b" FROM "foo" WHERE ("prop" = 'value') AND $timeFilter)
GROUP BY time($bin) fill(none)

Interestingly enough, similar query works for a different measurement (table).

[kennyli92]

I'm getting the same results as @mnicky