{"id":1690,"date":"2018-11-10T11:07:45","date_gmt":"2018-11-10T04:07:45","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1690"},"modified":"2024-04-16T10:20:36","modified_gmt":"2024-04-16T03:20:36","slug":"sqlite-lead","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-window-functions\/sqlite-lead\/","title":{"rendered":"SQLite LEAD() Function"},"content":{"rendered":"\n

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

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

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

Here’s the syntax of the LEAD()<\/code> function:<\/p>\n\n\n

LEAD(expression [,offset[, default ]]) \nOVER (\n    PARTITION BY expression1, expression2,...\n    ORDER BY expression1 [ASC | DESC], expression2,...\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 a row based on the specified offset. It must return a single value.<\/p>\n\n\n\n
offset<\/h3>\n\n\n\n
It is the number of rows forwarding 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 in case the expression<\/code> at offset<\/code> is NULL. If you don’t specify default<\/code>, then the LEAD()<\/code> function returns NULL.<\/p>\n\n\n\n
PARTITION BY clause<\/h3>\n\n\n\n
The PARTITION BY<\/code> clause distributes the rows of the result set into partitions to which the LEAD()<\/code> function applies. If you don’t explicitly specify the PARTITION BY<\/code> clause, the function treats 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 specifies the order of rows in each partition to which the LEAD()<\/code> function applies.<\/p>\n\n\n\n
In practice, you often use the LEAD()<\/code> function to calculate the difference between the values of the current and subsequent rows.<\/p>\n\n\n\n
SQLite LEAD() function examples<\/h2>\n\n\n\n
We create a view named CustomerInvoices<\/code> based on the invoices<\/code> table in the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n
CREATE<\/span> VIEW<\/span> CustomerInvoices\nAS<\/span>\nSELECT<\/span>\n\tCustomerId,\n\tstrftime('%Y'<\/span>,InvoiceDate) Year<\/span>,\n\tSUM<\/span>( total ) Total\nFROM<\/span>\n\tinvoices\n\tGROUP<\/span> BY<\/span> CustomerId, strftime('%Y'<\/span>,InvoiceDate);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement queries data against the CustomerInvoices<\/code> view:<\/p>\n\n\n
SELECT<\/span>\n  *\nFROM<\/span>\n  CustomerInvoices\nORDER<\/span> BY<\/span>\n  CustomerId,\n  year<\/span>,\n  Total;<\/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 the LEAD() function over the result set example<\/h3>\n\n\n\n
The following query uses the LEAD()<\/code> function to return the difference in invoice amounts for a specific customer over subsequent years:<\/p>\n\n\n
SELECT<\/span>\n\tCustomerId,\n\tYear<\/span>,\n\tTotal,\n\tLEAD<\/span> ( Total,1<\/span>,0<\/span>) OVER<\/span> ( ORDER<\/span> BY<\/span> Year<\/span> ) NextYearTotal\nFROM<\/span>\n\tCustomerInvoices \nWHERE<\/span>\n\tCustomerId = 1<\/span>;\n<\/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 did not use the\u00a0PARTITION BY<\/code> clause so the LEAD()<\/code> function treated the whole result set derived from the FROM<\/code> clause as a single partition.<\/li>\n\n\n\n
  • Second, since the last row has no lead value, the function returned the default value of zero.<\/li>\n<\/ul>\n\n\n\n
    3) Using the LEAD() function over partition by example<\/h3>\n\n\n\n
    The following example uses the LEAD()<\/code> function to return the difference in invoice amounts for every customer over subsequent years:<\/p>\n\n\n
    SELECT<\/span>\n\tCustomerId,\n\tYear<\/span>,\n\tTotal,\n\tLEAD<\/span> ( Total, 1<\/span>, 0<\/span> ) OVER<\/span> (\n\t\tPARTITION<\/span> BY<\/span> CustomerId \n\t\tORDER<\/span> BY<\/span> Year<\/span> \n\t) NextYearTotal \nFROM<\/span>\n\tCustomerInvoices;<\/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
    In this example:<\/p>\n\n\n\n
      \n
    • First, the PARTITION BY<\/code> clause partitioned the rows in the result set by customer Id.<\/li>\n\n\n\n
    • Second, the ORDER BY<\/code> clause specified in the OVER<\/code> clause sorted the rows in each partition by year before theLEAD()<\/code> function was applied.<\/li>\n\n\n\n
    • Third, the LEAD()<\/code> function\u00a0is applied separately to each partition and the calculation restarted for each partition.<\/li>\n<\/ul>\n\n\n\n
      Notice that the last row of each partition has a value of zero (0) because it had no lead value.<\/p>\n\n\n\n
      Summary<\/h2>\n\n\n\n
        \n
      • Use the LEAD()<\/code> function to obtain data of the following 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