Как сравнить значенния в триаде последовательных строк и выбрать с максимальным из них?

Question

Предоположим, есть следующие значения временного ряда:

10-5-8-20-30-15-20-2-14-6-50-20-30-60-25-15-0-4-2-8-10-15-12-40-20

Это значения столбца y, а столбец x нумерует их начиная с 1.

with axis (x,y) as (
    select rownum, to_number(column_value)
    from xmlTable (
        '10,5,8,20,30,15,20,2,14,6,50,20,30,60,25,15,0,4,2,8,10,15,12,40,20')
)
select * from axis

Пришел к заключению, что если значения в столбце y сравнить триадами одна за одной, то максимальное значение в середине триады, является точкой пика.

Как в запросе определить точки пика без использования курсора и цикла?

_{Свободный перевод вопроса Compare the triplet rows together and select max of them от участника @Just an intern}

Answer 1

Для работы с последовательностями хорошо подходит Pattern Matching, который был специально разработан для таких задач.

select *
from axis
match_recognize (
    order by x
    measures 
        p.x as x, match_number () as pеаk#, p.y as y
    pattern (p)
    define p as p.y > prev (p.y) and p.y > next (p.y)
)

Точкa пика в клаузе DEFINE определена как: предыдущее и последующее значения Y меньше, чем текущее.

Результат (на db<>fiddle):

         X      PЕАK#          Y
---------- ---------- ----------
         5          1         30
         7          2         20
         9          3         14
        11          4         50
        14          5         60
        18          6          4
        22          7         15
        24          8         40

Answer 2

Если имеется в виду, что текущее значение в столбце y больше, чем значения y в предыдущей и последующей строке, и значения x действительно без пропусков (как и указано вопросе), тогда просто нужен JOIN:

select z.*
from axis z 
join axis prev on prev.x = z.x - 1 
join axis next on next.x = z.x + 1
where z.y > prev.y and z.y > next.y

_{Свободный перевод ответа от участника @Gordon Linoff}

Answer 3

Если MATCH_RECOGNIZE пока недоступна, потому что версия ниже 12c, то можно найти точки пика с помощью аналитических функций без каких-либо самосоединений таблиц:

SELECT x, y
FROM (
  SELECT x, y,
    LAG(y)  OVER (ORDER BY x) AS prev_y,
    LEAD(y) OVER (ORDER BY x) AS next_y
  FROM axis)
WHERE y > prev_y AND y > next_y

Или:

SELECT x, y
FROM (
  SELECT x, y,
    MAX(y) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_y,
    MAX(y) OVER (ORDER BY x RANGE BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_y
  FROM axis)
WHERE y > prev_y AND y > next_y

Оба датут ожидаемый результ:

         X          Y
---------- ----------
         5         30
         7         20
         9         14
        11         50
        14         60
        18          4
        22         15
        24         40

8 rows selected.

На db<>fiddle

_{Свободный перевод ответа от участника @MT0}

БЛОГ НА HUSL

Как сравнить значенния в триаде последовательных строк и выбрать с максимальным из них?

Ответы (3 шт):