Number Taxi rides

// Calculate when trips start
startTrip = from(bucket: "myDatabase")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "taxiData" and r._field == "rpm")
  |> map(fn: (r) => ({ r with _value: if r._value > 0 then 1 else 0 }))
  |> derivative(unit: 1s, nonNegative: true)
  |> filter(fn: (r) => r._value == 1)

// Calculate average speed per trip
from(bucket: "myDatabase")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "taxiData" and r._field == "speed")
  |> join(tables: {startTrip: startTrip}, on: ["_measurement", "_start", "_stop"])
  |> map(fn: (r) => ({ r with tripId: r._time_startTrip })) // Assign a tripId based on the start time of the trip
  |> group(columns: ["_field", "tripId"])
  |> aggregateWindow(every: 1s, fn: mean, createEmpty: false) // Calculate the average speed per second for each trip
  |> group(columns: ["tripId"])
  |> mean() // Calculate the overall average speed for each trip

or

from(bucket: "meineDatenbank")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "taxiDaten" and (r._field == "drehzahl" or r._field == "geschwindigkeit"))
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: if r.drehzahl > 0 then 1 else 0 }))
  |> derivative(unit: 1s, nonNegative: true)
  |> filter(fn: (r) => r._value == 1)
  |> group()
  |> cumulativeSum(column: "_value")
  |> map(fn: (r) => ({ r with fahrtId: r._value }))
  |> join(tables: {drehzahl: key, geschwindigkeit: key}, on: ["_time", "fahrtId"])
  |> drop(columns: ["drehzahl", "_value"])
  |> group(columns: ["fahrtId"])
  |> mean(column: "geschwindigkeit")

I think I got it. However, I have one small question: How can I fill a variable with the last value of a field? I can number the trips using a small trigger, but I should get the last tripId.

maxTrip = from(bucket: "myTaxi")
  |> range(start: -10d)
  |> filter(fn: (r) => r._measurement == "TaxiNo11" and r._field == "rideNo")
  |> last()
  |> .... as int, not as stream please

Here is the solution:

// Helper function to return an integer from a table
getFieldValue = (tables=<-, field) => {
  extract = tables
    |> last() // shrink table to one row
    |> findColumn(fn: (key) => key._field == field, column: "_value")

  return if length(arr: extract) == 0 then 0 else extract[0] // return 0 if there is no table else last value
}

// We need the last tripId as integer
lastId = from(bucket: "TaxiData")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "Taxi" and r._field == "tripId" and r._value != 0) // search for tripId
  |> last() // shrink table to one row
  |> getFieldValue(field: "_field" )

rpm_data = from(bucket: "TaxiData")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "Taxi" and r._field == "EngineRPM")
  |> map(fn: (r) => ({ r with tmp: if r._value > 0 then 1 else 0 })) // create helperfield tmp
  |> derivative(unit: 1s, nonNegative: true, columns: ["tmp"]) // only the changes from 0 to not 0
  |> map(fn: (r) => ({ r with tripId: int(v: r.tmp) })) // create tripIp with integer of tmp
  |> drop(columns: ["tmp"]) // remove helperfield tmp
  |> cumulativeSum(columns: ["tripId"])
  |> map(fn: (r) => ({ r with tripId: if r._value > 0 then r.tripId + lastId else 0 }))
  |> yield(name: "rpm")
1 Like

To count the rides, it is importend to use the same window:

from(bucket: v.defaultBucket)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "Taxi" and r._field == "EngineRPM")
  |> aggregateWindow(every: v.windowPeriod , fn: mean, createEmpty: true) // we need a defined window
  |> map(fn: (r) => ({ r with tmp: if r._value > 0 then 1 else 0 }))
  |> derivative(unit: v.windowPeriod, nonNegative: true, columns: ["tmp"]) // the same window must be used here
  |> filter(fn: (r) => r.tmp == 1)
  |> count()

Super brilliant @Ulrich

Thanks for working out the details and sharing. And thanks ChatGPT for the hints along the way!

It was the new engine ChatGPT 4, version 3.5 didn’t understand my questions.
It will be quieter in the forums, because I will solve my questions directly with ChatGPT in the future. Try it, it’s amazing that ChatGPT offered a solution but the specialists couldn’t. (Either because they didn’t have time, or because I didn’t describe the problem in a concise and easy-to-understand manner.)