{"id":1696,"date":"2018-11-10T11:43:34","date_gmt":"2018-11-10T04:43:34","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1696"},"modified":"2024-04-16T09:56:45","modified_gmt":"2024-04-16T02:56:45","slug":"sqlite-lag","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-window-functions\/sqlite-lag\/","title":{"rendered":"SQLite LAG() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite LAG()<\/code> function to get the value of the preceding row from the current row in the partition.<\/p>\n\n\n\n

Introduction to SQLite LAG() function<\/h2>\n\n\n\n

SQLite LAG()<\/code> function is a window function<\/a> that allows you to obtain the data of the preceding row at the given physical offset from the current row in the partition.<\/p>\n\n\n\n

The following shows the syntax of the LAG()<\/code> function:<\/p>\n\n\n

LAG(expression [,offset[, default ]]) OVER (\n    PARTITION BY expression1, expression2,...\n\tORDER BY expression1 [ASC | DESC], expression2,...\n)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
expression<\/h3>\n\n\n\n
It is an expression that is evaluated against the value of the preceding row based on the specified offset<\/code>. The expression must return a single value.<\/p>\n\n\n\n
offset<\/h3>\n\n\n\n
Offset is the number of rows from the current row to obtain the value. The default value of the offset<\/code> is 1 if you don’t specify it explicitly.<\/p>\n\n\n\n
default<\/h3>\n\n\n\n
It is the default value to return if the expression<\/code> at offset<\/code> is NULL<\/code>. If you skip the default<\/code>, then the LAG()<\/code> function will return NULL<\/code> if the expression<\/code> evaluates to NULL<\/code>.<\/p>\n\n\n\n
PARTITION BY clause<\/h3>\n\n\n\n
The PARTITION BY<\/code> clause divides the rows of the result set into partitions to which the LAG()<\/code> function applies. If you don’t specify the PARTITION BY<\/code> clause explicitly, the LAG()<\/code> function will treat the whole result set as a single partition.<\/p>\n\n\n\n
ORDER BY clause<\/h3>\n\n\n\n
The ORDER BY<\/code> clause sorts the rows of each partition to which the LAG()<\/code> function applies.<\/p>\n\n\n\n
The LAG()<\/code> function is often used to calculate the difference between the values of the current row and the preceding row at a given offset.<\/p>\n\n\n\n
SQLite LAG() function examples<\/h2>\n\n\n\n
We will use the CustomerInvoices<\/code> view created in the LEAD()<\/a><\/code> function tutorial for the demonstration.<\/p>\n\n\n\n
The following query returns data from the CustomerInvoices<\/code> view:<\/p>\n\n\n
SELECT<\/span>\n\t* \nFROM<\/span>\n\tCustomerInvoices \nORDER<\/span> BY<\/span>\n\tCustomerId,\n\tYear<\/span>,\n\tTotal;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
1) Using SQLite LAG() function over the result set example<\/h3>\n\n\n\n
The following query uses the LAG()<\/code> function to return the difference in invoice amounts for the customer Id 4 over the subsequent years:<\/p>\n\n\n
SELECT<\/span>\n  CustomerId,\n  Year<\/span>,\n  Total,\n  LAG (Total, 1<\/span>, 0<\/span>) OVER<\/span> (ORDER<\/span> BY<\/span>  Year<\/span>) PreviousYearTotal\nFROM<\/span>\n  CustomerInvoices\nWHERE<\/span>\n  CustomerId = 4<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n
    \n
  • First, we skipped the PARTITION BY<\/code> clause so the LAG()<\/code> function treated the whole result set as a single partition.<\/li>\n\n\n\n
  • Second, because there is no preceding value available for the first row, the LAG()<\/code> function returned the default value of zero.<\/li>\n<\/ul>\n\n\n\n
    2) Using SQLite LAG() function over partition by example<\/h3>\n\n\n\n
    The following statement uses the LAG()<\/code> function to return the difference in invoice amounts for every customer over subsequent years:<\/p>\n\n\n
    SELECT<\/span>\n  CustomerId,\n  year<\/span>,\n  Total,\n  LAG (Total, 1<\/span>, 0<\/span>) OVER<\/span> (PARTITION<\/span> BY<\/span> CustomerId ORDER<\/span> BY<\/span> Year<\/span>) PreviousYearTotal\nFROM<\/span>\n  CustomerInvoices;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example:<\/p>\n\n\n\n
      \n
    • First, the PARTITION BY<\/code> clause divided the rows in the result set by customer Id into partitions.<\/li>\n\n\n\n
    • Second, the ORDER BY<\/code> clause specified in the OVER<\/code> clause specified the order of the rows in each partition by year before the LAG()<\/code> function was applied.<\/li>\n\n\n\n
    • The LAG()<\/code> function is applied to each partition separately and the calculation was restarted for each partition.<\/li>\n<\/ul>\n\n\n\n
      The following picture shows the partial output:<\/p>\n\n\n\n
      \"SQLite<\/figure>\n\n\n\n
      Note that the first row of each partition has a value of zero (0) because it had no LAG value.<\/p>\n\n\n\n
      Summary<\/h2>\n\n\n\n
        \n
      • Use the SQLite LAG()<\/code> function to get data of the preceding row from the current row in the partition.<\/li>\n<\/ul>\n
        \n\t
        \n\t\t
        Was this tutorial helpful ?<\/div>\n\t\t
        \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
        \n\t\t
        \n\t\t\t
        <\/div>\n\t\t\t\n\t\t\t