Calculate ratio between tags

InfluxDB 2 Dashboards
,
1

Hi,

I just started implementing Influxdb with Grafana for our business KPIs. Is there any way to make calculation between tags on the same value?

My data looks like this:
data, type=valid value=10
data type=fraud value=3

I would like to get the Fraud/Valid ratio (0.3) in Grafana Single Stat.

Is there any option to do this or should I store the data in different format like fraud=3 and valid=10?

Thanks in advance.

2

Issue is Solved and data structure changed.

3

How did you solve it?

I am trying to find a way to calculate percent between to fields, maybe your solution can help me

4

I turned my tags to fields. So I can query them and calculate with subquery. TL;DR InfluxDB Tech Tips - All About the New Subqueries! | InfluxData

Original (normal query):
SELECT sum("field1") as "sum_field1", sum("field2") as "sum_field2" FROM ... WHERE ... GROUP BY ..

And then you can use it as a subquery:

SELECT "sum_field1" / "sum_field2" FROM ( SELECT sum("field1") as "sum_field1", sum("field2") as "sum_field2" FROM ... WHERE ... GROUP BY ... )

5

Thanks, will try tomorrow and get back

6

I now understand that my query is little bit more complicated.
I want to select data from a measurement then compare it with same measurement but where I have filtered out data that is between certain numbers.

data = all values
datafiltered = values between 10 and 100

SELECT "data" / "datafiltered" FROM
(

(
SELECT count("peak") AS "data"  FROM "autogen"."CPU" WHERE ("host" =~ /^$Host$/)  AND $timeFilter GROUP BY  "Host"
)
,
(
SELECT count("peak") AS "datafiltered" FROM "autogen"."CPU" WHERE ("host" =~ /^$SHost$/) AND ("peak"< 100 OR "peak">10) AND $timeFilter GROUP BY  "Host"
)

)
1 Like
7

Does your query work for you?

I tried something similar but it doesn’t allow me to SELECT FROM two subqueries.

My query looks like this

SELECT (("exA_spread"+"exB_spread")/2 )

AS "average"

FROM(
	SELECT (("askPrice1" - "bidPrice1") * 200 / ("askPrice1" + "bidPrice1")) 
	AS "exA_spread"  
	FROM "three_month_data"."orderbook_exA" 
	WHERE("instrument" = 'exA:btc_usdt') AND $timeFilter 
	GROUP BY "instrument"  fill(previous)
	,
	SELECT(("askPrice1" - "bidPrice1") * 200 / ("askPrice1" + "bidPrice1"))
	AS "exB_spread" 
	FROM "three_month_data"."orderbook_exB" 
	WHERE( "instrument" = 'exB:btc_usdt') AND $timeFilter
	GROUP BY "instrument"  fill(previous)
)

The two subqueries works fine by themselves alone as a query, but when I try to perform join-measurement as above, it seems grafana won’t let me to SELECT FROM two subqueries.

8

No it doesn’t work for me