Sign Up

Have an account? Sign In Now

Sign In

Forgot Password?

Don't have account, Sign Up Here

Forgot Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Have an account? Sign In Now

You must login to ask question.

Forgot Password?

Need An Account, Sign Up Here

Please briefly explain why you feel this question should be reported.

Please briefly explain why you feel this answer should be reported.

Sign InSign Up

Softans

Softans Logo Softans Logo
Search
Ask A Question

Mobile menu

Close
Ask a Question
  • Home
  • Add group
  • Groups page
  • Communities
  • Questions
    • New Questions
    • Trending Questions
    • Must read Questions
    • Hot Questions
  • Polls
  • Tags
  • Badges
  • Users
  • Help
Home/ Questions/Q 1689
Answered
Ahmad Khan Kolaachi
Ahmad Khan Kolaachi
Asked: April 29, 20222022-04-29T07:52:42+00:00 2022-04-29T07:52:42+00:00

Check if value between a threshold range using sliding time window/bins – KQL query

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!

kql querywindow
  • 0
  • 1 1 Answer
  • 37 Views
  • 0 Followers
  • 0
Answer
Share
  • Facebook
  • Report

1 Answer

  • Voted
  • Oldest
  • Recent
  1. Best Answer
    Ahmad Khan Kolaachi
    2022-04-29T07:53:42+00:00Added an answer on April 29, 2022 at 7:53 am

    Here is a solution without any assumptions on your data.
    Additional information, such has the granularity of the timestamps, could help to simplify it.

    1. Break the rows to groups, treating records with out-of-range (OOR) speed as boundaries. Also add a synthetic record that could later be used as a minimal boundary.
    2. For each group find its boundaries, which are aligned to the sliding interval (1m). The main challenge here is to find the lower boundry for each group, since for the upper boundry we use bin which is a synonym to floor, but we don’t have an equivalent for ceiling.
    3. Remove the groups that are smaller than the defined window (5m).
    4. Join the groups boundaries with the groups’ records and drop the OOR records.

    let p_sliding_interval = 1m;
    let p_window = 5m;
    let t = 
    datatable (Timestamp:datetime ,Speed:real ,Temperature:real)
    [
         '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
    ];
    let min_Timestamp = toscalar(t | summarize min(Timestamp));
    let max_Timestamp = toscalar(t | summarize max(Timestamp));
    let row_level = 
    t
    | extend out_of_range_record_flag = iff(Speed !between (100 .. 150),1,0)
    | union (print Timestamp = datetime(null) , out_of_range_record_flag = 1)
    | order by Timestamp asc nulls first 
    | extend in_range_group_id = row_cumsum(out_of_range_record_flag);
    let group_boundries =
    row_level 
    | where out_of_range_record_flag == 1
    | project in_range_group_id, from_timestamp = coalesce(Timestamp, min_Timestamp)
    | order by in_range_group_id asc
    | extend to_timestamp = coalesce(next(from_timestamp), max_Timestamp)
    | extend bin_from_timestamp = bin(from_timestamp, p_sliding_interval)
    | extend ceil_from_timestamp = bin_from_timestamp + iff(bin_from_timestamp == from_timestamp, 0ms, p_sliding_interval)
    | extend in_range_window = bin(to_timestamp, p_sliding_interval) - ceil_from_timestamp
    | where in_range_window >= p_window;
    group_boundries
    | join kind=inner row_level on in_range_group_id
    | where out_of_range_record_flag == 0
    | project Timestamp, Speed, Temperature
    
    Timestamp Speed Temperature
    2022-01-01T00:03:00Z 134.8 25.6
    2022-01-01T00:04:00Z 125.3 25.5
    2022-01-01T00:05:00Z 118.4 25.4
    2022-01-01T00:06:00Z 106.3 26.3
    2022-01-01T00:07:00Z 119.6 26.5
    2022-01-01T00:08:00Z 134.7 25.4
    2022-01-01T00:10:00Z 137.5 25.5
    2022-01-01T00:11:00Z 129.9 27.4
    2022-01-01T00:12:00Z 118.1 26.3
    2022-01-01T00:13:00Z 105.4 25.7
    2022-01-01T00:14:00Z 101.7 24.4
    2022-01-01T00:15:00Z 100.8 25.6

    Fiddle

    • 0
    • Reply
    • Share
      Share
      • Share on Facebook
      • Share on Twitter
      • Share on LinkedIn
      • Share on WhatsApp
      • Report

Leave an answer
Cancel reply

You must login to add an answer.

Forgot Password?

Need An Account, Sign Up Here

Sidebar

Ask A Question
  • Popular
  • Answers
  • Ghulam Nabi

    Why are the British confused about us calling bread rolls ...

    • 5 Answers
  • Alex

    application has failed to start because no appropriate graphics hardware ...

    • 4 Answers
  • Jerry

    Add file to native target programmatically via tuist/XcodeProj

    • 4 Answers
  • Ghulam Nabi
    Ghulam Nabi added an answer To resolve the NullPointerException, you need to identify the variable… March 15, 2023 at 8:25 am
  • Ghulam Nabi
    Ghulam Nabi added an answer You can replace the PnP code in your Azure Function… February 13, 2023 at 7:11 am
  • Ghulam Nabi
    Ghulam Nabi added an answer You can use the $match stage in the aggregate pipeline… February 10, 2023 at 6:20 am

Trending Tags

android c++ cypress flutter java javascript python selenium testng webdriver

Top Members

Robert

Robert

  • 3 Questions
  • 1k Points
Luci

Luci

  • 5 Questions
  • 1k Points
Kevin O Brien

Kevin O Brien

  • 2 Questions
  • 1k Points

Explore

  • Home
  • Add group
  • Groups page
  • Communities
  • Questions
    • New Questions
    • Trending Questions
    • Must read Questions
    • Hot Questions
  • Polls
  • Tags
  • Badges
  • Users
  • Help

Footer

Softans

Softans is a social questions & Answers Engine which will help you establish your community and connect with other people.

About Us

  • Blog
  • Jobs
  • About Us
  • Meet The Team
  • Contact Us

Legal Stuff

Help

Follow

© 2021 Softans. All Rights Reserved
With Love by Softans.

Insert/edit link

Enter the destination URL

Or link to existing content

    No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.