I would like to write a sliding window query in KQL which would check if the the speed of a car is ALWAYS between a certain speed limit (e.g. b/w 100 and 150 km/h) for a time window of 5 mins.
Following is a sample dataset for it:
Timestamp | Speed | Temperature |
---|---|---|
2022-01-01 00:00:00.0000000 | 142.5 | 25.1 |
2022-01-01 00:01:00.0000000 | 147.4 | 25.5 |
2022-01-01 00:02:00.0000000 | 158.2 | 25.4 |
2022-01-01 00:03:00.0000000 | 134.8 | 25.6 |
2022-01-01 00:04:00.0000000 | 125.3 | 25.5 |
2022-01-01 00:05:00.0000000 | 118.4 | 25.4 |
2022-01-01 00:06:00.0000000 | 106.3 | 26.3 |
2022-01-01 00:07:00.0000000 | 119.6 | 26.5 |
2022-01-01 00:08:00.0000000 | 134.7 | 25.4 |
2022-01-01 00:09:00.0000000 | 153.2 | 26.6 |
2022-01-01 00:10:00.0000000 | 137.5 | 25.5 |
2022-01-01 00:11:00.0000000 | 129.9 | 27.4 |
2022-01-01 00:12:00.0000000 | 118.1 | 26.3 |
2022-01-01 00:13:00.0000000 | 105.4 | 25.7 |
2022-01-01 00:14:00.0000000 | 101.7 | 24.4 |
2022-01-01 00:15:00.0000000 | 100.8 | 25.6 |
2022-01-01 00:16:00.0000000 | 95.4 | 26.2 |
2022-01-01 00:17:00.0000000 | 105.6 | 26.7 |
First the window would check if the speed is in the defined range from 0-4mins, then 1-5mins, then, 2-6mins, then 3-7mins, then, 4-8mins and so on ….. until 10-14mins, then 11-15mins, then 12-16mins and then 13-17mins. If the speed is continuously in the 100-150km/h range the query would return those rows as output.
I would expect the following output:
Timestamp | Speed | Temperature |
---|---|---|
2022-01-01 00:03:00.0000000 | 134.8 | 25.6 |
2022-01-01 00:04:00.0000000 | 125.3 | 25.5 |
2022-01-01 00:05:00.0000000 | 118.4 | 25.4 |
2022-01-01 00:06:00.0000000 | 106.3 | 26.3 |
2022-01-01 00:07:00.0000000 | 119.6 | 26.5 |
2022-01-01 00:08:00.0000000 | 134.7 | 25.4 |
2022-01-01 00:10:00.0000000 | 137.5 | 25.5 |
2022-01-01 00:11:00.0000000 | 129.9 | 27.4 |
2022-01-01 00:12:00.0000000 | 118.1 | 26.3 |
2022-01-01 00:13:00.0000000 | 105.4 | 25.7 |
2022-01-01 00:14:00.0000000 | 101.7 | 24.4 |
2022-01-01 00:15:00.0000000 | 100.8 | 25.6 |
In the output dataset, timestamps from 0-2mins are filtered out because when we check from 0-4mins there is a value (158.2km/h) out of the range (100-150km/h). Similarly, we find this value when checking from 1-5mins and also when checking from 2-6mins.
From 3-7mins all the speed values are within the range constantly for 5 mins. And from 4-8mins as well that’s why these rows are kept.
In the end, I would just like to plot the temperature for all the 5 min time windows where the speed was always within the range. The plotting part is clear so I just need help with filtering the rows.
Thanks in Advance!
Here is a solution without any assumptions on your data.
Additional information, such has the granularity of the timestamps, could help to simplify it.
bin
which is a synonym to floor, but we don’t have an equivalent for ceiling.Fiddle