I'm trying to build a map (using the 'Maps' feature) which will represent the number of unique device type that are deviating in a certain measurement from the average of this measurement in a certain area.
If the average is 70, all of the ones with measurement of 70+ will be considered a deviation.
The SQL syntax will be something like so:
SELECT AVG(measurement) AS overall_measurement FROM index1 INNER JOIN SELECT AVG(measurement) AS device_measurement FROM index1 GROUP BY device_type WHERE device_measurement > overall_measurement
From what I've found there are only simple Metrics aggregations in Maps.
Is there any way to get this information in the Maps feature? Or will I have to create a new index with the information?