In Oracle, an analytic function refers to functions that are used to calculate a set of rows. Analytic functions are mainly used to determine the various metrics such as percentiles, standard deviations, etc.
In this tutorial, we will learn how to use one of the Oracle LAG functions to access a row at a specific offset from the current row.
Oracle LAG() Function
The lag() function is used to fetch the value of a row in a table from the current row. For example, you can use it to get the value from five rows from your current position. You can use it to perform the calculations or comparisons between values over time.
The following shows the basic syntax of the function:
OVER (
[ query_partition_clause]
order_by_clause
)
The function accepts the following parameters:
- Expr – This defines the expression which is evaluated against the specified row value at the offset parameter.
- Offset – It sets the number of rows that you wish to go backward from the current row. By default, the function fetches the row that is behind your current one.
- Default – It allows the function to return a default value when it goes beyond the scope of the specified offset instead of a NULL value.
- Query_partition_clause – It specifies the partition by clause to organize the rows into various groups.
- Order_by_clause – It sets the order for the rows.
Oracle LAG() Function Example
This example demonstrates how to use the LAG() function.
from EMPLOYEES emp;
Result:
As you can see, the lag column contains the salary of the row behind it. If there is no row behind it such as the first column, the function returns NULL.
We can avoid this by setting the default parameter.
from EMPLOYEES emp;
Output:
We can also specify the offset value as shown in the following:
from EMPLOYEES emp;
In this case, the query should fetch five columns behind it as follows:
To use the lag function with a partition by clause, we can run the following command:
from EMPLOYEES emp;
Result:
Conclusion
In this post, we discussed how to use the Oracle lag() function to fetch the previous rows from the current position at a given offset.
