Calulating fields from multiple tags

[ardiehl]

From this query, i want to
add some fields:

import "join"

first = from(bucket: "ad")
  |> range(start: 2023-01-01T00:00:00Z, stop: 2023-12-31T23:59:59Z)
  |> filter(fn: (r) => r["_measurement"] == "heatConsumption")
  |> filter(fn: (r) => r["_field"] == "kwh")
  |> first()
  |> drop(columns: ["_start","_stop", "_measurement"])

last = from(bucket: "ad")
  |> range(start: 2023-01-01T00:00:00Z, stop: 2023-12-31T23:59:59Z)
  |> filter(fn: (r) => r["_measurement"] == "heatConsumption")
  |> filter(fn: (r) => r["_field"] == "kwh")
  |> last()
  |> drop(columns: ["_start","_stop", "_measurement"])

join.left(
    left: first,
    right: last,
    on: (l, r) => l.Device == r.Device,
    as: (l, r) => ({_time: 2023-12-31T23:59:59Z, Device: l.Device, _field: r._field, _value: r._value - l._value}),
    )
  |> yield(name: "mean")

Result:

ad@lnx:/mpeg/prj/influxdb$ influx query <consumption.flux 
Result: mean
Table: keys: [Device, _field]
         Device:string           _field:string                      _time:time                  _value:float
----------------------  ----------------------  ------------------------------  ----------------------------
                    EG                     kwh  2023-12-31T23:59:59.000000000Z                          2788
Table: keys: [Device, _field]
         Device:string           _field:string                      _time:time                  _value:float
----------------------  ----------------------  ------------------------------  ----------------------------
                    G1                     kwh  2023-12-31T23:59:59.000000000Z                           629
Table: keys: [Device, _field]
         Device:string           _field:string                      _time:time                  _value:float
----------------------  ----------------------  ------------------------------  ----------------------------
                    G2                     kwh  2023-12-31T23:59:59.000000000Z                          1231
Table: keys: [Device, _field]
         Device:string           _field:string                      _time:time                  _value:float
----------------------  ----------------------  ------------------------------  ----------------------------
                 HK_EG                     kwh  2023-12-31T23:59:59.000000000Z                          1120
Table: keys: [Device, _field]
         Device:string           _field:string                      _time:time                  _value:float
----------------------  ----------------------  ------------------------------  ----------------------------
                HK_G12                     kwh  2023-12-31T23:59:59.000000000Z                          1869
Table: keys: [Device, _field]
         Device:string           _field:string                      _time:time                  _value:float
----------------------  ----------------------  ------------------------------  ----------------------------
                    KE                     kwh  2023-12-31T23:59:59.000000000Z                             0
Table: keys: [Device, _field]
         Device:string           _field:string                      _time:time                  _value:float
----------------------  ----------------------  ------------------------------  ----------------------------
                   SUT                     kwh  2023-12-31T23:59:59.000000000Z                          1608

Now i want to have a result table with
EG_Total = EG.kwh + HK_EG.kwh
and
G1_Total = G1.kwh + HK_G12 * 0.4
G2_Total = G2.kwh + KH_G12 * 0.6
KE_Total = KE.kwh
SUT_Total = SUT.kwh

no clue how to do that, any help really appreciated.

Thx

[scott]

@ardiehl I’d actually approach this query without a join and it should be easier and more performant. I’m making some assumptions about your data based on the sample results you gave. Hopefully they are correct.

I think you should use pivot here to pivot your existing data into a format where you can perform the calculations you want for each row. You can then use map() to iterate over each row and perform the calculations:

first = from(bucket: "ad")
    |> range(start: 2023-01-01T00:00:00Z, stop: 2023-12-31T23:59:59Z)
    |> filter(fn: (r) => r["_measurement"] == "heatConsumption")
    |> filter(fn: (r) => r["_field"] == "kwh")
    |> first()
    |> pivot(rowKey: ["_time"], columnKey: ["Device", "_field"], valueColumn: "_value")
    |> map(fn: (r) => {
        EG_Total = r.EG_kwh + r.HK_EG_kwh
        G1_Total = r.G1_kwh + r.HK_G12_kwh * 0.4
        G2_Total = r.G2_kwh + r.KH_G12_kwh * 0.6
        KE_Total = r.KE_kwh
        SUT_Total = r.SUT_kwh

        return {_time: r._time, EG_Total: EG_Total, G1_Total: G1_Total, G2_Total: G2_Total, KE_Total: KE_Total, SUT_Total: SUT_Total}
    }

[ardiehl]

@scott

irst = from(bucket: "ad")
    |> range(start: 2023-01-01T00:00:00Z, stop: 2023-12-31T23:59:59Z)
    |> filter(fn: (r) => r["_measurement"] == "heatConsumption")
    |> filter(fn: (r) => r["_field"] == "kwh")
    |> first()
    |> pivot(rowKey: ["_time"], columnKey: ["Device", "_field"], valueColumn: "_value")
    |> map(fn: (r) => {
        EG_Total = r.EG_kwh + r.HK_EG_kwh
        G1_Total = r.G1_kwh + r.HK_G12_kwh * 0.4
        G2_Total = r.G2_kwh + r.KH_G12_kwh * 0.6
        KE_Total = r.KE_kwh
        SUT_Total = r.SUT_kwh

        return {_time: r._time, EG_Total: EG_Total, G1_Total: G1_Total, G2_Total: G2_Total, KE_Total: KE_Total, SUT_Total: SUT_Total}
    }

Hi Scott,
thanks for the answer. Pivot was exactly what i was looking for. However, i’m performing the join because i have to calculate the delta between the newest and the oldest value of *_kwh. This seems to work for me:

import "join"

//startTime = time(v: "2023-01-01T00:00:00Z")
//endTime = time(v: "2023-12-31T23:59:59Z")
startTime = v.timeRangeStart
endTime = v.timeRangeStop

first = from(bucket: "ad")
  |> range(start: startTime, stop: endTime)
  |> filter(fn: (r) => r["_measurement"] == "heatConsumption")
  |> filter(fn: (r) => r["_field"] == "kwh")
  |> first()
  |> drop(columns: ["_start","_stop", "_measurement"])

last = from(bucket: "ad")
  |> range(start: startTime, stop: endTime)
  |> filter(fn: (r) => r["_measurement"] == "heatConsumption")
  |> filter(fn: (r) => r["_field"] == "kwh")
  |> last()
  |> drop(columns: ["_start","_stop", "_measurement"])

join.left(
    left: first,
    right: last,
    on: (l, r) => l.Device == r.Device,
    as: (l, r) => ({_time: endTime, Device: l.Device, _field: r._field, _value: r._value - l._value}),
    )
  |> pivot(rowKey: ["_time"], columnKey: ["Device", "_field"], valueColumn: "_value")
  |> map(fn: (r) => {
        EG_Total = r.EG_kwh + r.HK_EG_kwh
        G1_Total = r.G1_kwh + r.HK_G12_kwh * 0.4
        G2_Total = r.G2_kwh + r.HK_G12_kwh * 0.6
        KE_Total = r.KE_kwh
        SUT_Total = r.SUT_kwh
        Total = r.EG_kwh + r.HK_EG_kwh + r.G1_kwh + r.HK_G12_kwh + r.KE_kwh +  r.SUT_kwh
        return {_time: r._time, EG_Total: EG_Total, G1_Total: G1_Total, G2_Total: G2_Total, KE_Total: KE_Total, SUT_Total: SUT_Total, Total: Total}
    })
  |> yield(name: "mean")

[scott]

The following query should give you the delta and avoid a join. Joins are notoriously resource-heavy operations. This approach avoids the join by using union() and pivot(). I also included some other optimizations in there as well:

startTime = v.timeRangeStart
endTime = v.timeRangeStop

data = () =>
    from(bucket: "ad")
      |> range(start: startTime, stop: endTime)
      |> filter(fn: (r) => r["_measurement"] == "heatConsumption")
      |> filter(fn: (r) => r["_field"] == "kwh")

first = data() |> first()
last = data() |> last()

union(tables: [first, last])
    |> difference()
    |> pivot(rowKey: ["_time"], columnKey: ["Device", "_field"], valueColumn: "_value")
    |> map(fn: (r) => {
            EG_Total = r.EG_kwh + r.HK_EG_kwh
            G1_Total = r.G1_kwh + r.HK_G12_kwh * 0.4
            G2_Total = r.G2_kwh + r.HK_G12_kwh * 0.6
            KE_Total = r.KE_kwh
            SUT_Total = r.SUT_kwh
            Total = r.EG_kwh + r.HK_EG_kwh + r.G1_kwh + r.HK_G12_kwh + r.KE_kwh +  r.SUT_kwh

            return {_time: r._time, EG_Total: EG_Total, G1_Total: G1_Total, G2_Total: G2_Total, KE_Total: KE_Total, SUT_Total: SUT_Total, Total: Total}
        }
    )

[ardiehl]

@ardiehl

//startTime = time(v: "2023-01-01T00:00:00Z")
//endTime = time(v: "2023-12-31T23:59:59Z")
startTime = v.timeRangeStart
endTime = v.timeRangeStop

Thanks for the reply, this one does not seem to work, i get different results when running the query multiple times and the total is not calculated at all, below 3 samples (data has not been changed between queries as these are updates once a day only)

table_result	_timeno groupdateTime:RFC3339  EG_Total  G1_Total  G2_Total  KE_Total  SUT_Total  Total
0                2023-09-04T09:22:41.740Z                                                  -1658
0                2023-12-21T21:45:15.741Z						
0                2023-10-18T18:47:30.581Z						
0                2023-10-30T17:20:55.483Z                                           0		
							
table_result	_timeno groupdateTime:RFC3339  EG_Total  G1_Total  G2_Total  KE_Total  SUT_Total  Total
0                2023-09-04T09:22:41.740Z                 -1423.4   -2428.6                -1658	
0                2023-10-18T18:47:30.581Z						
0                2023-10-30T17:20:55.483Z                                           0		
							
table_result	_timeno groupdateTime:RFC3339  EG_Total	 G1_Total  G2_Total  KE_Total  SUT_Total  Total
0                2023-09-04T09:22:41.740Z                           -2428.6                -1658	
0                2023-12-21T21:45:15.741Z						
0                2023-10-18T18:47:30.581Z						
0                2023-10-30T17:20:55.483Z                                           0		

Btw, looks like the export to csv option in the gui is no longer available (nfluxDB v2.7.4)

[ardiehl]

i have tried to export some of my data and include these into the query, results are different but also not consistent. Timetamps of source data are currently equal between Devices but there is no guaranty that this is always the case.

import "csv"

consumptionData =
    "
#datatype,string,long,string,dateTime:RFC3339,string,string,double
#group,false,false,true,false,true,true,false
#default,,,,,,,
,result,table,_measurement,_time,Device,_field,_value
,mean,0,heatConsumption,2023-09-04T09:22:41Z,EG,kwh,42.0
,mean,0,heatConsumption,2023-09-04T20:45:13Z,EG,kwh,42.0
,mean,0,heatConsumption,2023-12-20T21:45:15Z,EG,kwh,2879.0
,mean,0,heatConsumption,2023-12-21T21:45:15Z,EG,kwh,2925.0
,mean,0,heatConsumption,2023-09-04T09:22:41Z,G1,kwh,11.0
,mean,0,heatConsumption,2023-09-04T20:45:13Z,G1,kwh,11.0
,mean,0,heatConsumption,2023-09-05T12:24:29Z,G1,kwh,11.0
,mean,0,heatConsumption,2023-09-05T20:45:13Z,G1,kwh,11.0
,mean,0,heatConsumption,2023-12-19T21:45:15Z,G1,kwh,640.0
,mean,0,heatConsumption,2023-12-20T21:45:15Z,G1,kwh,651.0
,mean,0,heatConsumption,2023-12-21T21:45:15Z,G1,kwh,660.0
,mean,0,heatConsumption,2023-09-04T09:22:41Z,G2,kwh,0.0
,mean,0,heatConsumption,2023-09-04T20:45:13Z,G2,kwh,0.0
,mean,0,heatConsumption,2023-09-05T12:24:29Z,G2,kwh,0.0
,mean,0,heatConsumption,2023-09-05T20:45:13Z,G2,kwh,0.0
,mean,0,heatConsumption,2023-12-20T21:45:15Z,G2,kwh,1250.0
,mean,0,heatConsumption,2023-12-21T21:45:15Z,G2,kwh,1267.0
,mean,0,heatConsumption,2023-10-18T18:47:30Z,HK_EG,kwh,113.0
,mean,0,heatConsumption,2023-10-18T20:45:13Z,HK_EG,kwh,114.0
,mean,0,heatConsumption,2023-10-19T20:45:19Z,HK_EG,kwh,127.0
,mean,0,heatConsumption,2023-10-20T20:45:13Z,HK_EG,kwh,138.0
,mean,0,heatConsumption,2023-10-21T20:45:13Z,HK_EG,kwh,149.0
,mean,0,heatConsumption,2023-10-22T20:45:13Z,HK_EG,kwh,158.0
,mean,0,heatConsumption,2023-10-23T20:45:37Z,HK_EG,kwh,170.0
,mean,0,heatConsumption,2023-10-24T20:45:13Z,HK_EG,kwh,185.0
,mean,0,heatConsumption,2023-10-25T15:48:11Z,HK_EG,kwh,197.0
,mean,0,heatConsumption,2023-10-25T16:01:00Z,HK_EG,kwh,198.0
,mean,0,heatConsumption,2023-10-25T16:17:51Z,HK_EG,kwh,198.0
,mean,0,heatConsumption,2023-10-25T16:32:02Z,HK_EG,kwh,198.0
,mean,0,heatConsumption,2023-12-16T21:45:15Z,HK_EG,kwh,1162.0
,mean,0,heatConsumption,2023-12-17T21:45:15Z,HK_EG,kwh,1183.0
,mean,0,heatConsumption,2023-12-18T21:45:15Z,HK_EG,kwh,1207.0
,mean,0,heatConsumption,2023-12-19T21:45:15Z,HK_EG,kwh,1233.0
,mean,0,heatConsumption,2023-12-20T21:45:15Z,HK_EG,kwh,1257.0
,mean,0,heatConsumption,2023-12-21T21:45:15Z,HK_EG,kwh,1281.0
,mean,0,heatConsumption,2023-09-04T09:22:41Z,HK_G12,kwh,14.0
,mean,0,heatConsumption,2023-09-04T20:45:13Z,HK_G12,kwh,14.0
,mean,0,heatConsumption,2023-09-05T12:24:29Z,HK_G12,kwh,14.0
,mean,0,heatConsumption,2023-09-05T20:45:13Z,HK_G12,kwh,14.0
,mean,0,heatConsumption,2023-09-06T20:45:13Z,HK_G12,kwh,14.0
,mean,0,heatConsumption,2023-09-07T15:01:38Z,HK_G12,kwh,14.0
,mean,0,heatConsumption,2023-09-07T19:01:27Z,HK_G12,kwh,14.0
,mean,0,heatConsumption,2023-12-17T21:45:15Z,HK_G12,kwh,1810.0
,mean,0,heatConsumption,2023-12-18T21:45:15Z,HK_G12,kwh,1845.0
,mean,0,heatConsumption,2023-12-19T21:45:15Z,HK_G12,kwh,1883.0
,mean,0,heatConsumption,2023-12-20T21:45:15Z,HK_G12,kwh,1918.0
,mean,0,heatConsumption,2023-12-21T21:45:15Z,HK_G12,kwh,1950.0
,mean,0,heatConsumption,2023-10-30T17:20:55Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-10-30T21:45:15Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-10-31T21:45:39Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-11-01T21:45:22Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-11-02T21:45:15Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-11-03T15:29:33Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-12-18T21:45:15Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-12-19T21:45:15Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-12-20T21:45:15Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-12-21T21:45:15Z,KE,kwh,0.0
,mean,0,heatConsumption,2023-09-04T09:22:41Z,SUT,kwh,0.0
,mean,0,heatConsumption,2023-09-04T20:45:13Z,SUT,kwh,0.0
,mean,0,heatConsumption,2023-09-05T12:24:29Z,SUT,kwh,0.0
,mean,0,heatConsumption,2023-12-19T21:45:15Z,SUT,kwh,1608.0
,mean,0,heatConsumption,2023-12-20T21:45:15Z,SUT,kwh,1635.0
,mean,0,heatConsumption,2023-12-21T21:45:15Z,SUT,kwh,1658.0
"

startTime = time(v: "2023-01-01T00:00:00Z")
endTime = time(v: "2023-12-31T23:59:59Z")
//startTime = v.timeRangeStart
//endTime = v.timeRangeStop

data = () =>
    //from(bucket: "ad")
    csv.from(csv: consumptionData)
      |> range(start: startTime, stop: endTime)
      |> filter(fn: (r) => r["_measurement"] == "heatConsumption")
      |> filter(fn: (r) => r["_field"] == "kwh")

first = data() |> first()
last = data() |> last()

union(tables: [first, last])
    |> difference()
    |> pivot(rowKey: ["_time"], columnKey: ["Device", "_field"], valueColumn: "_value")
    |> map(fn: (r) => {
            EG_Total = r.EG_kwh + r.HK_EG_kwh
            G1_Total = r.G1_kwh + r.HK_G12_kwh * 0.4
            G2_Total = r.G2_kwh + r.HK_G12_kwh * 0.6
            KE_Total = r.KE_kwh
            SUT_Total = r.SUT_kwh
            Total = r.EG_kwh + r.HK_EG_kwh + r.G1_kwh + r.HK_G12_kwh + r.KE_kwh +  r.SUT_kwh

            return {_time: r._time, EG_Total: EG_Total, G1_Total: G1_Total, G2_Total: G2_Total, KE_Total: KE_Total, SUT_Total: SUT_Total, Total: Total}
        }
    )