Oracle LAG

Summary: in this tutorial, you will learn how to access the row at a given offset prior to the current row using Oracle LAG() function.

Introduction to Oracle LAG() function

Oracle LAG() is an analytic function that allows you to access the row at a given offset prior to the current row without using a self-join.

The following illustrates the syntax of the LAG() function:

LAG(expression [, offset ] [, default ])
OVER (
	[ query_partition_clause ] 
	order_by_clause
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

is a scalar expression evaluated against the value of the row at a given offset prior to the current row.

offset

is the number of rows that you want to backward from the current row. The default is 1.

default

If the offset goes beyond the scope of the partition, the function returns the default. If you omit default, then the function returns NULL.

query_partition_clause

The query_partition_clause clause divides rows into partitions to which the LAG() function is applied. By default, the function treats the whole result set as a single partition.

order_by_clause

The order_by_clause clause specifies the order of the rows in each partition to which the LAG() function is applied.

Similar to the LEAD() function, the LAG() function is very useful for calculating the difference between the values of current and previous rows.

Oracle LAG() function examples

We will reuse the salesman_performance view created in the LEAD() function tutorial for the demonstration.

SELECT 
	salesman_id,
	year, 
	sales
FROM 
	salesman_performance;
Code language: SQL (Structured Query Language) (sql)

A) Using Oracle LAG() function over a result set example

The following query uses the LAG() function to return sales and the previous year’s sales of the salesman id 62:

SELECT 
	salesman_id,
	year, 
	sales,
	LAG(sales) OVER (
		ORDER BY year
	) py_sales
FROM 
	salesman_performance
WHERE
	salesman_id = 62;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

The first row returned NULL for the py_sales column because there was no previous row of the first row.

To calculate year over year sales performance of the salesman id 62, you can use a common table expression as shown in the following query:

WITH cte_sales (
	salesman_id, 
	year, 
	sales,
	py_sales) 
AS (
	SELECT 
		salesman_id,
		year, 
		sales,
		LAG(sales) OVER (
			ORDER BY year
		) py_sales
	FROM 
		salesman_performance
	WHERE
		salesman_id = 62
)
SELECT 
	salesman_id,
	year,
	sales,
	py_sales,
	CASE 
    	 WHEN py_sales IS NULL THEN 'N/A'
  	ELSE
    	 TO_CHAR((sales - py_sales) * 100 / py_sales,'999999.99') || '%'
  	END YoY
FROM 
	cte_sales;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the result:

B) Using Oracle LAG() function over partitions example

The following statement uses the LAG() function to return YoY sales performance of every salesman:

WITH cte_sales (
	salesman_id, 
	year, 
	sales,
	py_sales) 
AS (
	SELECT 
		salesman_id,
		year, 
		sales,
		LAG(sales) OVER (
			PARTITION BY salesman_id
			ORDER BY year
		) py_sales
	FROM 
		salesman_performance
)
SELECT 
	salesman_id,
	year,
	sales,
	py_sales,
	CASE 
    	 WHEN py_sales IS NULL THEN 'N/A'
  	ELSE
    	 TO_CHAR((sales - py_sales) * 100 / py_sales,'999999.99') || '%'
  	END YoY
FROM 
	cte_sales;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this tutorial, you have learned how to use the Oracle LAG() function to access data of the row at a given offset prior to the current row in the partition without using a self-join.