Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

LAG function in oracle plsql

LAG Function in Oracle SQL – PLSQL

by

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:

ORDER_DATE PRODUCT_ID Quantity
25/01/2012 1001 10
26/01/2012 1001 20
27/01/2012 1002 28
28/01/2012 1002 30
29/01/2012 1001 32
30/01/2012 1003 45

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:

PRODUCT_ID ORDER_DATE PREVIOUS_ORDER_DATE
1001 1/25/2012
1001 1/26/2012 1/25/2012
1002 1/27/2012 1/26/2012
1002 1/28/2012 1/27/2012
1001 1/29/2012 1/28/2012
1003 1/30/2012 1/29/2012

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

  • 2nd, 3rd, 4th, 5th, and 6th 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 1st 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:

ORDER_DATE PRODUCT_ID Quantity
25/01/2012 1001 10
26/01/2012 1001 20
27/01/2012 1002 28
28/01/2012 1002 30
29/01/2012 1001 32
30/01/2012 1003 45

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:

PRODUCT_ID ORDER_DATE PREVIOUS_ORDER_DATE
1001 1/25/2012
1001 1/26/2012 1/25/2012
1001 1/29/2012 1/26/2012
1002 1/27/2012
1002 1/28/2012 1/27/2012
1003 1/30/2012

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

  • 2nd and 3rd 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.
  • 4th and 5th 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 6th 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.