Find turning points in rows with SQL or LINQ

Lest say I have the following data:

    Date            Value
  04.04.2019    |   -1,7
  05.04.2019    |   -3,3
  06.04.2019    |   -4,4
  07.04.2019    |   -6,4
  08.04.2019    |   5
  11.04.2019    |   8
  12.04.2019    |   -9,2
  13.04.2019    |   -12,4
  14.04.2019    |   2,5
  15.04.2019    |   9,1
  18.04.2019    |   5,6
  19.04.2019    |   5,9
  20.04.2019    |   8,5
  21.04.2019    |   -5,4
  26.04.2019    |   -5,7
  27.04.2019    |   -7,3
  28.04.2019    |   5,4
  29.04.2019    |   5,3
  02.05.2019    |   -3,6
  03.05.2019    |   -3,1
  04.05.2019    |   -1

I’m looking for an SQL or LINQ Query where I can find all rows with a turning point where the value goes up or below a given value, let’s say 5.

Answer

Use lag() and filtering:

select t.*
from (select t.*, lag(value) over (order by date) as prev_value
      from t
     ) t
where (prev_value < 5 and value > 5) or
      (prev_value > 5 and value < 5);

Note that lag() is a standard SQL function available starting in MySQL 8+.

Advertisement

Answer