By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE x (order_id, col) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 8 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 5, 3 FROM DUAL UNION ALL
SELECT 6, 2 FROM DUAL;
6 rows affected
SELECT *
FROM x
MATCH_RECOGNIZE(
ORDER BY order_id
MEASURES
any_row.col AS col1,
FIRST(up.col) AS col2,
LAST(up.col) AS col3,
FIRST(order_id) AS start_order_id
PATTERN ( any_row up{2} )
DEFINE up AS ( col > PREV(col) )
)
| COL1 | COL2 | COL3 | START_ORDER_ID |
|---|---|---|---|
| 1 | 2 | 8 | 1 |
SELECT *
FROM (
SELECT col AS col1,
LEAD(col, 1) OVER (ORDER BY order_id) AS col2,
LEAD(col, 2) OVER (ORDER BY order_id) AS col3,
order_id
FROM x
)
WHERE col2 > col1
AND col3 > col2;
| COL1 | COL2 | COL3 | ORDER_ID |
|---|---|---|---|
| 1 | 2 | 8 | 1 |