Compare multiple data from different fields on same query

1

Hello,

I need to get difference from recorder (1001,1002,1003,1004…) and recorder 535, i.e:

I wrote this query:

from(bucket:"sensoring")
  |> range(start:-1h)
  |> filter(fn:(r) => r._measurement == "readings" and r._field == "value" and r.recorder == "535" or r.recorder == "1001")
  |> limit(n:5, offset: 0)

And got this:

#group FALSE FALSE TRUE TRUE FALSE FALSE TRUE TRUE TRUE
#datatype string long dateTime:RFC3339 dateTime:RFC3339 dateTime:RFC3339 double string string string
#default _result
result table _start _stop _time _value _field _measurement recorder
0 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:32.369Z 431 value readings 1001
0 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:34.015Z 430 value readings 1001
0 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:35.375Z 431 value readings 1001
0 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:44.375Z 432 value readings 1001
0 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:49.04Z 431 value readings 1001
1 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:21.806Z 429 value readings 535
1 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:23.284Z 429 value readings 535
1 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:24.768Z 429 value readings 535
1 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:26.286Z 429 value readings 535
1 2021-09-03T19:17:20.869852374Z 2021-09-03T20:17:20.869852374Z 2021-09-03T19:17:27.793Z 429 value readings 535

How can I compare data from table 1 and 2 and get values like: 2, 1, 2, 3, 2…?

I did this to one recorder, but how can i put recorder “535” dinamically as setpoint on map function:

from(bucket:"sensoring")
  |> range(start:-1h)
  |> filter(fn:(r) => r._measurement == "readings" and r._field == "value" and r.recorder == "1001")
  |> map(fn: (r) => ({r with diff: r._value - 50.0}))

I tried this: Use multiple fields in a calculation | InfluxDB Cloud Documentation (influxdata.com)
But wasn’t helpful.

Thankou for your help.

2

Hello @bash,
Try ungrouping your data in the same table.
Then pivot your data.

|> group()
|> pivot(rowKey:["_time"], columnKey: ["recorder"], valueColumn: "_value")
|> map(fn: (r) => ({r with diff: r.1001 - r.535}))

Let me know if that does the trick :slight_smile: