CODEWITHSUNDEEP
sqlsql-server
Cody Richard
Cody Richard

Reputation: 3

SQL Server Determine the Amount of Time Above a Threshold

I am trying to determine the amount of time my data spends above a certain threshold. I have a SQL table of values that looks like this:

Data Image

Where the first column is datetime and the second column is value. This is time series data so it is a large table and cannot be changed. I want to know the first value that crosses over the threshold (say it is 50 for the example) this is my beginning, the last value that crosses back over the threshold which is the end, and the duration spent over the threshold.

In my data example the Beginning would be 9/20/2019 19:18, the end would be 9/20/2019 19:46 and the duration would be 28 minutes.

This needs to be written in one sql statement due to the requirements of the project. I am just wondering if this is possible and how to do it. Thanks!

Upvotes: 0

Views: 1090

Answers (2)

Anthony
Anthony

Reputation: 33

It looks like you are sampling every 10 seconds. If that is pretty solid, you can just count how many records are above 50 during a selected interval, and multiply by 10 seconds, that will be the duration that exceeds 50.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can use lead() and some aggregation:

select t.*
from (select t.*,
             datediff(minute,
                      ts, lead(ts) over (order by ts)
                     ) as diff_minutes
      from (select t.*,
                   lead(value) over (order by ts) as next_value
            from t
           ) t
      where (value < 50 and next_value >= 50) or
            (value >= 50 and next_value < 50
     ) t
where value < 50;

Your question is a little tricky because you want the time span to start just before the period in question. That is actually a simplification. The above implements:

  • Identify the next value.
  • Keep a row when next_value or current value exceeds the threshold or vice versa. This is the first row before and last row after the period.
  • Then use lead() to get the ending timestamp.
  • Finally filter down to just the first row.

Another approach is perhaps simpler. Define the groups based on the count of rows that are under the threshold up to or before the row. This keeps the previous row with the following group.

Then aggregate:

select min(ts), max(ts),
       datediff(minute, min(ts), max(ts)) as diff_minute
from (select t.*,
             sum(case when value < 50 then 1 else 0 end) over (order by ts) as grp
      from t
     ) t
group by grp;

Upvotes: 1

Related Questions