Number Taxi rides

Fluxlang
21 
// 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")
22

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
23

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
24

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()
25

Super brilliant @Ulrich

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

26

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.)