Combine separate signal streams into one table with an aggregated value for each signal per time interval

1
votes

I have an InfluxDB receiving data points from devices separately for each measurement, i.e. for GPS positions, latitude/longitude/altitude are three different streams and have to be queried separately. This part of the application I cannot change.

Now I want to create a simple table in Grafana showing the combined GPS data set for each timestamp. I have managed to merge all these signals into one table, but now each signal is its own row: Screenshot

My query looks like this:

SELECT 
  median(Latitude) as "Lat", 
  median(Longitude) as "Long", 
  median(Altitude) as "Alt", 
  median(TheSpeed) as "Speed" 
FROM
  (SELECT value as "Latitude" FROM "signal-1" WHERE $timeFilter  AND deviceId =~/$DeviceID/),
  (SELECT value as "Longitude" FROM "signal-2" WHERE $timeFilter  AND deviceId =~/$DeviceID/),
  (SELECT value as "Altitude" FROM "signal-5" WHERE $timeFilter  AND deviceId =~/$DeviceID/),
  (SELECT value as "TheSpeed" FROM "signal-4" WHERE $timeFilter  AND deviceId =~/$DeviceID/)
WHERE $timeFilter 
GROUP BY time(1s)

How can I merge the four separate signals into one table row?

Thank you for your help, I am stuck on this.

1
grafanainfluxdb

1 Answers

0
votes

I found the solution:

Changed the query like this:

SELECT 
median(value)
FROM
(SELECT value FROM "signal-1" WHERE $timeFilter  AND deviceId =~/$DeviceID/),
(SELECT value FROM "signal-2" WHERE $timeFilter  AND deviceId =~/$DeviceID/),
(SELECT value FROM "signal-5" WHERE $timeFilter  AND deviceId =~/$DeviceID/),
(SELECT value FROM "signal-4" WHERE $timeFilter  AND deviceId =~/$DeviceID/),
WHERE $timeFilter 
GROUP BY time($timegap), "description" 
FILL(previous)

And formatted the table as Time Series (instead of "Table") and used the "description field" as the ALIAS BY.

Now it looks just how I needed it: enter image description here

I'm a little surprised that nobody answered this, it seems like a noob question in retrospect. :(