Combine two InfluxDB rows into one - Flux query

Question

Hi I am currently doing this in R but would like to know if there is a way for me to do it in Flux instead:

I have a time series that tracks a value and only stores when the signal is turned on and off. The problem is that the nature of the machine I am tracking only allows for it to be done this way. This results in a data table/measurement where two rows kind of display a single value (e.g. the start and end of a Fault). How do I query the data using flux to combine those two rows? (With "start" and "stop" as tags/fields)

I currently use the elapsed()-function to calculate the time difference/duration of my value

time                      value     field          measurement    equipmentNumber    workplace    duration
2021-01-29 07:11:17.496   1         FAULT_LASER    FAULT_LASER    L5211M0855         0            188
2021-01-29 07:12:03.332   0         FAULT_LASER    FAULT_LASER    L5211M0855         0            45835
2021-01-29 07:12:19.618   1         FAULT_LASER    FAULT_LASER    L5211M0855         0            16285
2021-01-29 07:12:19.618   0         FAULT_LASER    FAULT_LASER    L5211M0855         0            161725

Im am doing this in R at the moment:

for(i in 1:nrow(df_f)){
  if(df_f[i, "duration"] > 0){
    df_fdur[i, "start"] <- df_f[i, "time"]
    df_fdur[i, "stop"] <- df_f[i+1, "time"]
    df_fdur[i, "type"] <- df_f[i, "value"]
    df_fdur[i, "duration"] <- df_f[i, "duration"]
    df_fdur[i, "workplace"] <- df_f[i, "workplace"]
    df_fdur[i, "equipmentNumber"] <- df_f[i, "equipmentNumber"]
  }
}

Any ideas on how I can do that?

Answer 1

This does not directly solve the question but it solved the problem I was working on. Maybe it is useful for someone else. Have a great day!

// Get all the data from the bucket filtered for FAULT_LASER
data = from(bucket: "plcview_4/autogen")
  |> range(start: 2021-01-29T00:00:00.000Z, stop: now())                // regular time range filter
  |> filter(fn: (r) => r._measurement == "FAULT_LASER")                 // filter for the measurement
  |> elapsed(unit: 1ms, timeColumn: "_time", columnName: "duration")    // calculate time difference between rows
  |> yield(name: "data")
// returns data tables for every unique set of tags (workplace and equipmentNumber)

// Filter for all "No-Fault-Values" and sum their durations
operational = data
  |> filter(fn: (r) => r._value == 0)   // filter for all rows where FAULT_LASER = 0 --> No Faults
  |> group()                            // group all data tables together
  |> sum(column: "duration")            // sum all the durations from all data tables 
  |> yield(name: "operational")

// Count the number of faults
nfaults = data
  |> filter(fn: (r) => r._value == 1)   // filter for all rows where FAULT_LASER = 1 --> Faults
  |> group()                            // group all data tables together
  |> count()                            // count the number of records
  |> yield(name: "nfaults")