Oracle LAG Function

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.

Here’s the basic 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.

If you have not created the salesman_performance view, you can use the following query:

CREATE OR REPLACE VIEW salesman_performance (salesman_id, YEAR, sales) AS
SELECT
  salesman_id,
  EXTRACT(
    YEAR
    FROM
      order_date
  ),
  SUM(quantity * unit_price)
FROM
  orders
  INNER JOIN order_items USING (order_id)
WHERE
  salesman_id IS NOT NULL
GROUP BY
  salesman_id,
  EXTRACT(
    YEAR
    FROM
      order_date
  );Code language: PHP (php)

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

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:

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:

Summary #

• Use the Oracle LAG() function to access data of the row at a given offset prior to the current row in the partition.