Oracle / PLSQL: LAG Function
This Oracle tutorial explains how to use the Oracle/PLSQL LAG function with syntax and examples.
Description
The Oracle/PLSQL LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the LEAD function.
Syntax
The syntax for the LAG function in Oracle/PLSQL is:
LAG ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause )
Parameters or Arguments
- expression
- An expression that can contain other built-in functions, but can not contain any analytic functions.
- offset
- Optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.
- default
- Optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.
- query_partition_clause
- Optional. It is used to partition the results into groups based on one or more expressions.
- order_by_clause
- Optional. It is used to order the data within each partition.
Returns
The LAG function returns values from a previous row in the table.
Applies To
The LAG function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example
The LAG function can be used in Oracle/PLSQL.
Let's look at an example. If we had an orders table that contained the following data:
ORDER_DATE | PRODUCT_ID | QTY |
---|---|---|
2007/09/25 | 1000 | 20 |
2007/09/26 | 2000 | 15 |
2007/09/27 | 1000 | 8 |
2007/09/28 | 2000 | 12 |
2007/09/29 | 2000 | 2 |
2007/09/30 | 1000 | 4 |
And we ran the following SQL statement:
SELECT product_id, order_date, LAG (order_date,1) OVER (ORDER BY order_date) AS prev_order_date FROM orders;
It would return the following result:
PRODUCT_ID | ORDER_DATE | PREV_ORDER_DATE |
---|---|---|
1000 | 2007/09/25 | NULL |
2000 | 2007/09/26 | 2007/09/25 |
1000 | 2007/09/27 | 2007/09/26 |
2000 | 2007/09/28 | 2007/09/27 |
2000 | 2007/09/29 | 2007/09/28 |
1000 | 2007/09/30 | 2007/09/29 |
In this example, the LAG function will sort in ascending order all of the order_date values in the orders table and then return the previous order_date since we used an offset of 1.
If we had used an offset of 2 instead, it would have returned the order_date from 2 orders earlier. If we had used an offset of 3, it would have returned the order_date from 3 orders earlier....and so on.
Using Partitions
Now let's look at a more complex example where we use a query partition clause to return the previous order_date for each product_id.
Enter the following SQL statement:
SELECT product_id, order_date, LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date FROM orders;
It would return the following result:
PRODUCT_ID | ORDER_DATE | PREV_ORDER_DATE |
---|---|---|
1000 | 2007/09/25 | NULL |
1000 | 2007/09/27 | 2007/09/25 |
1000 | 2007/09/30 | 2007/09/27 |
2000 | 2007/09/26 | NULL |
2000 | 2007/09/28 | 2007/09/26 |
2000 | 2007/09/29 | 2007/09/28 |
In this example, the LAG function will partition the results by product_id and then sort by order_date as indicated by PARTITION BY product_id ORDER BY order_date
. This means that the LAG function will only evaluate an order_date value if the product_id matches the current record's product_id. When a new product_id is encountered, the LAG function will restart its calculations and use the appropriate product_id partition.
As you can see, the 1st record in the result set has a value of NULL for the prev_order_date because it is the first record for the partition where product_id is 1000 (sorted by order_date) so there is no lower order_date value. This is also true for the 4th record where the product_id is 2000.
Advertisements