Compare the triplet rows together and select max of them?

user_16757014 0
Viewed 91

Consider the following time series data

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

These are the values of y, and the x column starts from 1 respectively.

CREATE TABLE "Z_AXIS" (X NUMBER, Y NUMBER)

I have come to the conclusion that if the data in the y-column are compared three by three, the maximum value in each triad, is the peak point.

Does anyone have an idea how to specify peak points without using cursor, loop, lead and lag?

oracle sql
3 Answers

MATCH_RECOGNIZE was designed for such scenarios:

SELECT *
FROM Z_AXIS
MATCH_RECOGNIZE
(
   ORDER BY X
   MEASURES    MATCH_NUMBER() AS match_num,
               CLASSIFIER() AS var_match
   ALL ROWS PER MATCH
   PATTERN ( PEAK )
   DEFINE
        PEAK AS      PEAK.Y > PREV(PEAK.Y) 
                AND  PEAK.Y > NEXT(PEAK.Y)
) mr;

db<>fiddle demo

Output:

         X  MATCH_NUM VAR_MATCH             Y
---------- ---------- ------------ ----------
         5          1 PEAK                 30
         7          2 PEAK                 20
         9          3 PEAK                 14
        11          4 PEAK                 50
        14          5 PEAK                 60
        18          6 PEAK                  4
        22          7 PEAK                 15
        24          8 PEAK                 40

Peak is defined as:

Y > prev value and Y > next value
user_50708790

If you mean that a given y value is larger than the y values on the previous or next row and your x values are really sequential with no gaps (as your question specifies), then you just want a join:

select z.*
from z_axis z join
     z_axis z_prev
     on z_prev.x = z.x - 1 join
     z_axis z_next
     on z_next.x = z.x + 1
where z.y > z_prev.y and z.y > z_next.y;
user_11440350

If you cannot use MATCH_RECOGNIZE then you can find the answer using analytic functions without any self-joins (assuming that you do not have an arbitrary self-imposed restriction to not use LAG or LEAD):

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   z_axis
)
WHERE y > prev_y AND y > next_y;

or:

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   z_axis
)
WHERE y > prev_y AND y > next_y;

Which, for the sample data, both output:

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

db<>fiddle here

user_15092640
Related