Hi all,
I have a question and I wonder if any of you could give me some ideas on how to resolve this kind of queries.
To understand, the best is to give an exemple. Let's consider a table like this one:
CREATE TABLE orders (
id number,
ordered number
);
INSERT INTO orders(id, ordered) VALUES(1, 10);
INSERT INTO orders(id, ordered) VALUES(2, 4);
INSERT INTO orders(id, ordered) VALUES(3, 12);
INSERT INTO orders(id, ordered) VALUES(4, 15);
INSERT INTO orders(id, ordered) VALUES(5, 1);
INSERT INTO orders(id, ordered) VALUES(6, 10);
INSERT INTO orders(id, ordered) VALUES(7, 20);
INSERT INTO orders(id, ordered) VALUES(8, 22);
INSERT INTO orders(id, ordered) VALUES(9, 26);
In this table I have a number of orders. First is an auto increment id. Orders follow this ID.
And we have an "ordered" field. That field is the amount of orders not yet processed. I would like to find if the last set of rows after an increase is more than 3.
In this exemple, row with ID 5 is 1. Then, we jump to 10. We see that there is an increase. The we go to 20, 22, 26. This is all increase and we have more than 3 consecutive rows. So, I should select these rows.
But, if in the middle of the rows, I had another decrease, I should not select them.
The idea is to see that orders are being processed faster than ordered.
So I thought we should start from the last row (bigger ID) and compare with previous rows until I get a value bigger than previous row. LAG can help. But how can I tell LAG to compare 3 rows? I don't know how to do that in SQL. Any suggestion? I'm still using Oracle 19c. So, an idea compatible to that version would be nice.
Thank you all