LAG Function in Oracle SQL – PLSQL

The LAG function in Oracle SQL / PLSQL is an analytical function and helps us to query more than one row at a time without having self join on the table.
The LAG function returns the value from the rows prior in the table.

Syntax for the LAG Function in Oracle SQL / PLSQL is:
SELECT column(s)
,LAG (expression [,offset,default]) OVER ([PARTITION BY column(s) ORDER BY column(s)]
FROM table_name(s);

• ‘expression’ is an expression that can contain other built in Oracle SQL functions, but cannot contain any Oracle SQL analytical function.
• ‘offset’ is an optional value. It is a physical offset from the current records in a table, if omitted the default is 1.
• ‘default’ is also optional. It is the value that would be returned if the offset goes out of bounds of the table, if omitted default will be NULL.

Example 1:
Using LAG function to without PARTITION BY

Suppose we have a table named ‘orders as shown below:

If we write our query for LAG without PARTITION BY as:

 SELECT product_id
       ,order_date
       ,LAG(order_date,1) OVER (ORDER BY order_date) AS Previous_Order_Date
FROM orders;

We will get the following output:

Let’s analyze the fetched records for understanding the LAG Analytical function without PARTITION BY:

• 2^nd, 3^rd, 4^th, 5^th, and 6^th record tells us the product_id, order_date and also the previous_order_date when a product was ordered, it doesn’t matter what product was order, what matters here is that on order was placed on a date.
• The 1^st records tell us that before 1/25/2012 no product was ordered and hence NULL was returned as previous_order_date.

Example 2:

Using LAG function to with PARTITION BY

Suppose we have a table named ‘orders as shown below:

If we write our query for LAG with PARTITION BY as:

SELECT product_id
       ,order_date
       ,LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS PREVIOUS_Order_Date
FROM orders;

We will get the following output:

Let’s analyze the fetched records for understanding the LAG Analytical function with PARTITION BY:

• 2^nd and 3^rd record tells us that the product with product_id = 1001 was ordered on 01/29/2012 and previous order was placed on 01/26/2012 and then also an order was placed on 01/25/2012 but before 01/25/2012 no order was placed and hence NULL is returned.
• 4^th and 5^th record tells us that an order for product with product_id = 1002 was placed on 01/28/2012 and the previous order for the same was placed on 01/27/2012 and no previous order was placed.
• The 6^th records tell us that before 1/30/2012 product with product_id = 1003 was not ordered and hence NULL was returned as previous_order_date.