SQLite LAG() Function

Summary: in this tutorial, you will learn how to use the SQLite LAG() function to get the value of the preceding row from the current row in the partition.

Introduction to SQLite LAG() function

SQLite LAG() function is a window function that allows you to obtain the data of the preceding row at the given physical offset from the current row in the partition.

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

LAG(expression [,offset[, default ]]) OVER (
    PARTITION BY expression1, expression2,...
	ORDER BY expression1 [ASC | DESC], expression2,...
)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

It is an expression that is evaluated against the value of the preceding row based on the specified offset. The expression must return a single value.

offset

Offset is the number of rows from the current row to obtain the value. The default value of the offset is 1 if you don’t specify it explicitly.

default

It is the default value to return if the expression at offset is NULL. If you skip the default, then the LAG() function will return NULL if the expression evaluates to NULL.

PARTITION BY clause

The PARTITION BY clause divides the rows of the result set into partitions to which the LAG() function applies. If you don’t specify the PARTITION BY clause explicitly, the LAG() function will treat the whole result set as a single partition.

ORDER BY clause

The ORDER BY clause sorts the rows of each partition to which the LAG() function applies.

The LAG() function is often used to calculate the difference between the values of the current row and the preceding row at a given offset.

SQLite LAG() function examples

We will use the CustomerInvoices view created in the LEAD() function tutorial for the demonstration.

The following query returns data from the CustomerInvoices view:

SELECT
	* 
FROM
	CustomerInvoices 
ORDER BY
	CustomerId,
	Year,
	Total;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

1) Using SQLite LAG() function over the result set example

The following query uses the LAG() function to return the difference in invoice amounts for the customer Id 4 over the subsequent years:

SELECT
  CustomerId,
  Year,
  Total,
  LAG (Total, 1, 0) OVER (ORDER BY  Year) PreviousYearTotal
FROM
  CustomerInvoices
WHERE
  CustomerId = 4;Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example:

• First, we skipped the PARTITION BY clause so the LAG() function treated the whole result set as a single partition.
• Second, because there is no preceding value available for the first row, the LAG() function returned the default value of zero.

2) Using SQLite LAG() function over partition by example

The following statement uses the LAG() function to return the difference in invoice amounts for every customer over subsequent years:

SELECT
  CustomerId,
  year,
  Total,
  LAG (Total, 1, 0) OVER (PARTITION BY CustomerId ORDER BY Year) PreviousYearTotal
FROM
  CustomerInvoices;Code language: SQL (Structured Query Language) (sql)

In this example:

• First, the PARTITION BY clause divided the rows in the result set by customer Id into partitions.
• Second, the ORDER BY clause specified in the OVER clause specified the order of the rows in each partition by year before the LAG() function was applied.
• The LAG() function is applied to each partition separately and the calculation was restarted for each partition.

The following picture shows the partial output:

Note that the first row of each partition has a value of zero (0) because it had no LAG value.

Summary

• Use the SQLite LAG() function to get data of the preceding row from the current row in the partition.