Oracle lead and lag Functions

The lag and lead functions are used to retrieve the data of the first n rows and the data of the last n rows. They must be combined with the over (order by) function.
Select id, name from dave
Id name
4 SF
3 Oracle
4 eygle
5 rman
4 export
The dave table contains id and name data. The lag function is used to retrieve the first n rows of data, and the lead function is used to retrieve the data of the last n rows of data. It is simple and clear in the example.
Select id, name, lag (id, 4) over (order by id) as v_ AB from dave
Id name v_ AB
3 oracle
4 export
4 SF
4 eygle
5 rman 3
Lag (id, n, values) is the complete form of the entire lag, where id is column_name, n is the first n rows, values is the default value, if n and values are omitted, the default n value is 1, and the values value is null.
In fact, we can fully understand that n is the data of the first n rows of the current row. If n is present, values or null values are displayed. The above results can also be explained.
The lag function has an over (order by id) where order by sorts the data again by asc. Therefore, the lag function must be created when order by is used, of course, order by name can also be used, but the final result displayed is the first order by and then the n rows of its lead or lag.
Of course, the lead function is basically the same as the lad function. The difference is that the first n rows are the last n rows.
Select id, name, lead (id, 4) over (order by id) as v_ AB from dave
3 oracle 5
4 export
4 SF
4 eygle
5 rman
Select id, name, lead (id, 4) over (order by 1) as v_ AB from dave
Is there a result for the order by 1 pair above.
Select id, name from dave order by 2 sorted
The order by item must be the number of SELECT-list expressions, that is, within the range of the total number of columns in the preceding select column.
Select id, name from dave order by 3. In this case, oracle Reports an error because the third column cannot be found in sorting.
But the following
Select id, name, lead (id, 4) over (order by id) as v_ AB from dave, check that the SQL Execution Plan has a window sort sorting plan, windows sort sorts window data, which is more efficient than global sort order.
Now we have discussed order by. If you want to use lead but do not want to use order by to change the lead value, you can directly select id, name, lead (id, 4) over (order by 1) as v_ AB from dave.