CODEWITHSUNDEEP
sqlsql-serversql-view
tomdertech
tomdertech

Reputation: 507

Detecting Data Transitions in SQL Data using View and Lag()

I am trying to create a view which lists out the transitions when a table of data switches between some bounds donoted by a where condition, for example

| id | data1 | data1high | data1low | 
|----|-------|-----------|----------|
|  1 |   150 |       200 |      100 |      
|  2 |    60 |       200 |      100 | 
|  3 |    60 |       200 |      100 |
|  4 |   150 |       200 |      100 | 
|  5 |    60 |       200 |      100 |
|  6 |    60 |       200 |      100 |
|  7 |   300 |       200 |      100 |

The view I am applying is this:

create view alarming_data as
   select id, data1
   from SensorData as A
   where A.data1 < A.data1low OR A.data1 > A.data1high

This provides me with a table when each line is outside the bounds:

| id | data1 |
|----|-------|
|  2 |    60 |
|  3 |    60 |
|  5 |    60 |
|  6 |    60 |
|  7 |   300 |

What I really want is a view where just the transitions are listed i.e:

| id | data1 | data1high | data1low | 
|----|-------|-----------|----------|
|  1 |   150 |       200 |      100 |      
|  2 |    60 |       200 |      100 | 
|  4 |   150 |       200 |      100 | 
|  6 |    60 |       200 |      100 |
|  7 |   300 |       200 |      100 |

I have thought about using the lag() function to get compare the previous row with the next row and try apply the transition detection logic but I have not been successful in applying lag() and then began to wonder if it was the correct approach:

create view detect_trans as
   select lag(data1,1) over (orderby id) as previousdata,
   from SensorData as A
   where ((A.data1 < A.data1low OR A.data1 > A.data1high) AND ((previousdata.data1 < previousdata.data1low OR previousdata.data1 > previousdata.data1high))

Any thoughts or suggestions on how to achieve the "detect_trans" view?

EDIT:

Working on Gordons solution but trying to integrate the conditional test aswel:

create view trans_data as
select id, data1, data1high, data1low
from (select sd.*, lag(data1) over (order by id) as prevvalue
     from SensorData sd
    ) sd
where (sd.data1 < sd.data1low OR sd.data1 > sd.data1high) <> prevvalue;

Upvotes: 0

Views: 1293

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use lag() with a subquery:

create view alarming_data as
   select id, data1, data1high, data1.low
   from (select sd.*, lag(data1) over (order by id) as prevvalue
         from SensorData sd
        ) sd
   where data1 <> prevvalue;

If you want the first value, then include or prevvalue is null in the where clause.

Upvotes: 2

Related Questions