Oracle LAG function

Oracle/PLSQL LAG function is an analytical function that allows you to query more than one row in a table, while not having an attached table. This returns values from the previous row in the table. To return values from the next row, try the LEAD function.

Oracle/PLSQL syntax of the LAG function

LAG ( expression_id [, offset_id [, default_id] ] ).
over ([ query_partition_clause_id ] order_by_clause_id )

Parameters or arguments

• expression_id – an expression that can contain other built-in functions, but cannot contain analytical functions.
• offset_id – optional. It is a physical offset from the current row in the table. If this parameter is not specified, it is 1 by default.
• default_id – optional. This is the value that is returned if offset is outside the table. If this option is not specified, the default is Null.
• query_partition_clause_id – optional. It is used to divide results into groups based on one or more expressions.
• order_by_clause_id – optional. It is used to organize the data in each section.

The LAG function returns values from the previous row in the table.

You can use the LAG function in the following versions of Oracle/PLSQL

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

LAG function can be used in Oracle/PLSQL

Let’s take a look at an example. If we have a table that contains the following data:

And we will fulfill the next request:

select prod_id,
order_date_id,
LAG (order_date_id,1) over (ORDER BY order_date_id) AS prev_order_date_id
from orders_id;

then we’ll get the next result:

Since we used offset = 1, the query returns the previous ORDER_DATE_id.

If we used offset = 2 instead of 1, the request would return ORDER_DATE_id 2 positions earlier. If we used offset = 3, then the request would return ORDER_DATE_id on 3 positions earlier … and so on.

If we only want to get orders for this product_id, we will execute the following SQL query:

SELECT prod_id,
order_date_id,
LAG (order_date_id,1) over (ORDER BY order_date_id) AS prev_order_date_id
FROM orders_id
WHERE prod_id = 2000;

We’ll get the result:

In this example, the query returned ORDER_DATE_id for prod_id = 2000 and ignored all other entries.

Using partition

Now let’s look at a more complex example where we use the partition parameter to return the previous order_date_id for each prod_id.

Enter the following SQL statement:

SELECT prod_id,
order_date_id,
LAG (order_date_id,1) OVER (PARTITION BY prod_id ORDER BY order_date_id) AS prev_order_date_id
FROM orders_id;

It will return the next result:

In this example, the LAG function will divide the results by prod_id and then sort by order_date_id, as specified in PARTITION BY prod_id ORDER BY order_date_id. This means that the LAG function will only evaluate the order_date_id value if prod_id matches the prod_id of the current record. When a new prod_id occurs, the LAG function restarts its calculations and uses the corresponding section prod_id.

As you can see, the first record in the result set has the value NULL for prev_order_date_id, because this is the first record for a section where prod_id is 1000 (sorted by order_date_id), so there is no lower order_date_id value. This is also true for the 4th entry, where prod_id is equal to 2000.

LAG and LEAD : Problem Solving using Analytic Functions