SQLite LAG

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 which 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

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

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

default

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:

SQLite LEAD function sample table

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:

SQLite LAG Function ORDER BY clause example

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 SQL 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 order 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:

SQLite LAG Function Over Partition Example

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

In this tutorial, you have learned how to use the SQLite LAG() function to get data of the preceding row from the current row in the partition.

Was this tutorial helpful ?