I have sensor data that I’d like to quantize into discrete data, something like this:
0<x<10 = 1
10<x<15 = 2
15<x<18 = 3
And put the output of the quantization into a discrete panel to see how many hours X is a 1 or 2 or 3.
I have a feeling it could be done with something called subqueries, but I’m not certain. The examples subqueries I’ve read about all perform a math operation (like sum or mean) on the query data, rather than perform some quantization using a set of expressions.
Firstly, which back-end data store are you using? It might be possible to
create a query / function there which does what you want.
Secondly, is there no possibility to do this quantisation before the data gets
in to your data store (possibly as an extra field, so you keep the original
data as well, in case that’s useful)?
Teh reason for question two is that the process would then only need to be
done once per data point, whereas anything you can do in Grafana will almost
certainly repeat the process for every data point in each query, thus being
relatively computationally expensive.
@pooh, the data is coming from Influx. I was hoping to be able to do this somewhat on the fly, so that the query could be changed slightly to get new results rather than hard coding in the quantization straight into the database.
Okay, I take your point regarding modifying the query rather than having
something hard-coded in the front-end.
I don’t know if anyone else has a more elegant suggestion, but I can make it
work like this:
select sensor from
(select sensor0+1 from mqtt_consumer where sensor<10),
(select sensor0+2 from mqtt_consumer where sensor>=10 and sensor<15),
(select sensor*0+3 from mqtt_consumer where sensor>=15 and sensor<18)
where __$timeFilter
That should give you a useful pointer for something that works, even if it’s
not entirely efficient
Yeah, I think that’s what I was going for. Subquery syntax is a bit of a mystery to me still. Adapting what you have and simplifying the use case a bit, I wasn’t able to get this to work. The chart is empty for me.
SELECT sensor FROM
(select sensor0+1 from "default"."power" WHERE ("topic" = '/mqtt/power') AND sensor<10),
(select sensor0+2 from "default"."power" WHERE ("topic" = '/mqtt/power') AND sensor>=10)
WHERE __$timeFilter
I’m not understanding the “sensor0+1” and the “sensor<10” inequality in the subquery syntax. I thought it would be “value<10” for the first subquery, something like this:
SELECT sensor FROM
(select 0 as sensor from "default"."power" WHERE ("topic" = '/mqtt/power') AND "value"<10),
(select 1 as sensor from "default"."power" WHERE ("topic" = '/mqtt/power') AND "value">=10)
WHERE __$timeFilter
But that throws an error “field must contain at least one variable”. My end goal is to have a chart from start_time to end_time of 0’s and 1’s depending on if the data is less than 10 or greater than 10 for that /mqtt/power data point.
I’m not understanding the “sensor0+1” and the “sensor<10” inequality in the
subquery syntax. I thought it would be “value<10” for the first subquery,
I was simply assuming that your data values were in a field called “sensor”.
Also, something seems to have gone missing from the calaculation I posted. I
said “sensor * 0 + 1” (sensor times zero plus one).
If your data is in a field valed “value” then you would want:
select value from
(select value0+1 from mqtt_consumer where value<10),
(select value0+2 from mqtt_consumer where value>=10 and value<15),
(select value*0+3 from mqtt_consumer where value>=15 and value<18)
where __$timeFilter
Adapting this to what you say you want:
select value from
(select value0 from default.power where topic = ‘/mqtt/power’) and
value<10),
(select value0+1 from default.power where topic = ‘/mqtt/power’ and
