many thanks for the help. Now I have the number of trips, but I can’t determine any other information.
I would like to count up one _tag per trip so that I can evaluate one trip at a time. How long did it last? How many km have been driven? What was the average speed?
A light is switched on and off again and again. The current power consumption is measured. In the table, the times and amperes are recorded.
I would like to show in a table (Grafana) how often the lamp was on today, when it was on and how much electricity it used. And I want to know how long it was out.
But not aggregated, but individually for each phase.
0 amp is off and something >0 is on.
I am very happy if you push me in the right direction.
Before we do the light switch example, let’s go back to the taxi ride.
I created some fake data in my system. Does this look OK for demonstration purposes? If yes, we can write queries to evaluate each trip’s distance (km driven) and the average speed for that trip.
Perfect. Both examples are the same in that over time something supplies data multiple times and one cycle of the data should be used in each case.
The columns are sometimes calculated with difference(), sometimes with mean() or with sum(). Everything is summarized with a column != 0.
I look forward to your example. I think that should definitely be included in the documentation.
You are right…this is a tough nut to crack. All the solutions I can find dealing with state changes are about aggregating the whole dataset. What we want to do here is separate out each chunk of records where the EngineRPM == 1 and perform some math on the other columns (Odometer, VehicleSpeed).
Maybe it’s only possible with a special function. Am I really the first to have this requirement? I can hardly imagine that, because especially with IoT there are many devices that want to be viewed according to their operating status.
I really hope there is a solution. Many thanks for your support.
Similar use cases come up a lot and I would summarize them as “detecting cycles and grouping by cycles.” Something I’ve been advocating for in Flux for a long time, but it hasn’t been added yet is a version of map() or something similar that can use values from previous rows.
There is a proposal for a scan function that would do just this. In the proposal, there’s an example of how you could use scan() to detect phases and increment a phase ID when a new phase starts. EPIC: scan function · Issue #4671 · influxdata/flux · GitHub
Thanks to you both for the help and clarification.
Sometimes 5% can decide everything.
The SCAN() function described on Github prepares exactly what I described at the beginning. Once the data is grouped about a license plate, everything else can be determined.
If variables could be reassigned, it would already be possible today. So you could remember _value and increment a counter and write it back. This could be implemented as a trigger.
What can we do to ensure that this proposal is heard and implemented as soon as possible?
"Oh, I see what you mean now! In that case, you could adjust the query to calculate the number of trips based on the duration of the non-zero values as follows:
scss
from(bucket: "myDatabase")
|> range(start: -1h) // Query the last hour
|> filter(fn: (r) => r._measurement == "taxiData" and r._field == "rpm") // Select only the RPM data
|> map(fn: (r) => if r._value > 0 then 1 else 0) // Return a 1 if RPM > 0, otherwise 0
|> derivative(unit: 1s, nonNegative: true) // Calculate the rate of change to determine the duration of the ones (i.e. the trips)
|> filter(fn: (r) => r._value == 1) // Select only the values with a rate of change of 1
|> map(fn: (r) => 1) // Return a 1 to count the number of trips
|> sum() // Calculate the total number of trips
In this query, we first query the data from the last hour in the “myDatabase” bucket and filter down to only the datapoints with the “rpm” field. We then map each value to a 1 if it is greater than 0, or a 0 otherwise, which creates a sequence of 1’s and 0’s indicating whether the taxi was driving at that point in time or not. We then calculate the rate of change (i.e. the duration) of the 1’s per second using the derivative function and select only the values with a rate of change of 1 (i.e. a transition from 0 to 1). Finally, we return a 1 to count the number of trips and calculate the total number of trips using the sum function."
In this query, we first filter down to the “rpm” and “speed” fields for the “taxiData” measurement. We then group the data by a unique identifier for each trip, which we can derive by assigning a unique ID to each trip based on the transitions in the “rpm” data (similar to the previous query).
We then map each group to include the trip duration, trip distance (calculated as the last speed value multiplied by the trip duration), and the average speed (calculated as the distance divided by the duration). We use an if statement to handle cases where the last field is “speed” (i.e. the taxi was still moving at the end of the trip).
We then group the data by the trip ID and calculate the mean of the average speeds for each trip.
Note that this query assumes that the speed data is recorded in km/h. If it’s recorded in a different unit, you will need to adjust the calculation accordingly.
To count the number of trips, I have received a working solution. Slowly but surely, ChatGPT understands what I am looking for and provides me with solutions. I still need to test them all. I am amazed at what ChatGPT can do.
from(bucket: v.defaultBucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "97912487" and r._field == "S1_PID_0C_EngineRPM")
|> 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)
|> map(fn: (r) => ({ r with _value: 1 }))
|> sum()
