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 LEAD() function over a result set example

The following query uses the LEAD() 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:

Oracle LAG Function example

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:

Oracle LAG Function YoY example

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:

Oracle LAG Function over partitions example

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.

Was this tutorial helpful?