{"id":6995,"date":"2018-08-26T01:31:58","date_gmt":"2018-08-26T08:31:58","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6995"},"modified":"2024-01-04T03:22:12","modified_gmt":"2024-01-04T10:22:12","slug":"mysql-lag-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-lag-function\/","title":{"rendered":"MySQL LAG Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL LAG()<\/code> function to access data of a previous row from the current row in the same result set.<\/p>\n\n\n\n

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

The LAG()<\/code> function is a window function<\/a> that allows you to access data from a previous row in a result set from the current row without using a self-join<\/a>.<\/p>\n\n\n\n

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

LAG(expression,offset, default_value) \nOVER (\n    PARTITION BY partition_expression\n    ORDER BY order_expresion ASC|DESC\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
expression<\/h3>\n\n\n\n
The expression<\/code> is a column or an expression from which you want to retrieve the previous value.<\/p>\n\n\n\n
offset<\/h3>\n\n\n\n
The offset<\/code> is the number of rows to go back from the current row. The offset<\/code> must be zero or a positive integer number. <\/p>\n\n\n\n
If offset<\/code> is zero, then the LAG()<\/code> function returns the current row. If you don’t provide the offset<\/code> argument, it defaults to 1<\/code>.<\/p>\n\n\n\n
default_value<\/h3>\n\n\n\n
If there is no preceding row, then the LAG()<\/code> function returns the default_value<\/code>. If you omit the default_value<\/code>, the LAG()<\/code> function will return NULL<\/a><\/code>.<\/p>\n\n\n\n
PARTITION BY clause<\/h3>\n\n\n\n
The PARTITION BY<\/code> clause divides the result set into partitions to which the LAG()<\/code> function is applied independently. <\/p>\n\n\n\n
If you omit the PARTITION BY<\/code> clause, the LAG()<\/code> function will consider 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 before the LAG()<\/code> function is applied.<\/p>\n\n\n\n
The\u00a0LAG()<\/code> function can be useful for calculating the difference between the current and previous rows.<\/p>\n\n\n\n
MySQL LAG() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the LAG()<\/code> function. We’ll use the following sales<\/code> table for demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales(\r\n    sales_employee VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\r\n    fiscal_year INT<\/span> NOT<\/span> NULL<\/span>,\r\n    sale DECIMAL<\/span>(14<\/span>,2<\/span>) NOT<\/span> NULL<\/span>,\r\n    PRIMARY KEY<\/span>(sales_employee,fiscal_year)\r\n);\r\n\r\nINSERT<\/span> INTO<\/span> sales(sales_employee,fiscal_year,sale)\r\nVALUES<\/span>('Bob'<\/span>,2016<\/span>,100<\/span>),\r\n      ('Bob'<\/span>,2017<\/span>,150<\/span>),\r\n      ('Bob'<\/span>,2018<\/span>,200<\/span>),\r\n      ('Alice'<\/span>,2016<\/span>,150<\/span>),\r\n      ('Alice'<\/span>,2017<\/span>,100<\/span>),\r\n      ('Alice'<\/span>,2018<\/span>,200<\/span>),\r\n       ('John'<\/span>,2016<\/span>,200<\/span>),\r\n      ('John'<\/span>,2017<\/span>,150<\/span>),\r\n      ('John'<\/span>,2018<\/span>,250<\/span>);\r\n\r\nSELECT<\/span> * FROM<\/span> sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------+-------------+--------+\r\n| sales_employee | fiscal_year | sale   |\r\n+----------------+-------------+--------+\r\n| Alice          |        2016<\/span> | 150.00<\/span> |\r\n| Alice          |        2017<\/span> | 100.00<\/span> |\r\n| Alice          |        2018<\/span> | 200.00<\/span> |\r\n| Bob            |        2016<\/span> | 100.00<\/span> |\r\n| Bob            |        2017<\/span> | 150.00<\/span> |\r\n| Bob            |        2018<\/span> | 200.00<\/span> |\r\n| John           |        2016<\/span> | 200.00<\/span> |\r\n| John           |        2017<\/span> | 150.00<\/span> |\r\n| John           |        2018<\/span> | 250.00<\/span> |\r\n+----------------+-------------+--------+\r\n9<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
1) Basic MySQL LAG() function example<\/h3>\n\n\n\n
The following query uses the LAG function to compare the sales of a year with the previous one:<\/p>\n\n\n
SELECT<\/span> \n  sales_employee, \n  fiscal_year, \n  sale, \n  LAG(sale, 1<\/span> , 0<\/span>) OVER<\/span> (\n    PARTITION<\/span> BY<\/span> sales_employee \n    ORDER<\/span> BY<\/span> fiscal_year\n  ) 'previous year sale'<\/span> \nFROM<\/span> \n  sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------+-------------+--------+--------------------+\r\n| sales_employee | fiscal_year | sale   | previous year sale |\r\n+----------------+-------------+--------+--------------------+\r\n| Alice          |        2016<\/span> | 150.00<\/span> |               0.00<\/span> |\r\n| Alice          |        2017<\/span> | 100.00<\/span> |             150.00<\/span> |\r\n| Alice          |        2018<\/span> | 200.00<\/span> |             100.00<\/span> |\r\n| Bob            |        2016<\/span> | 100.00<\/span> |               0.00<\/span> |\r\n| Bob            |        2017<\/span> | 150.00<\/span> |             100.00<\/span> |\r\n| Bob            |        2018<\/span> | 200.00<\/span> |             150.00<\/span> |\r\n| John           |        2016<\/span> | 200.00<\/span> |               0.00<\/span> |\r\n| John           |        2017<\/span> | 150.00<\/span> |             200.00<\/span> |\r\n| John           |        2018<\/span> | 250.00<\/span> |             150.00<\/span> |\r\n+----------------+-------------+--------+--------------------+\r\n9<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
The LAG() function divides the rows in the sales table by sales employees into partitions. Since we have three sales employees, it creates three partitions:<\/p>\n\n\n
PARTITION BY sales_employee <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In each partition, the LAG() function sorts the rows by fiscal years. Hence, the rows in each partition are sorted by fiscal year column:<\/p>\n\n\n
ORDER BY fiscal_year<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For each row in a partition, the LAG() function returns the value in the sale column of the previous row. If there is no previous row, it returns 0 as we specify in the default_value argument of the LAG() function:<\/p>\n\n\n
LAG(sale, 1 , 0)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
As a result, the LAG() function returns the sales of the previous year (or zero) from the current row. <\/p>\n\n\n\n
2) Using multiple LAG functions<\/h3>\n\n\n\n
To compare the sales of the “current” year with the previous year, you can use an additional LAG() function as follows:<\/p>\n\n\n
SELECT<\/span> \r\n  sales_employee, \r\n  fiscal_year, \r\n  sale, \r\n  LAG(sale, 1<\/span>, 0<\/span>) OVER<\/span> (\r\n    PARTITION<\/span> BY<\/span> sales_employee \r\n    ORDER<\/span> BY<\/span> fiscal_year\r\n  ) AS<\/span> previous_year_sale,\r\n  sale - LAG(sale, 1<\/span>, 0<\/span>) OVER<\/span> (\r\n    PARTITION<\/span> BY<\/span> sales_employee \r\n    ORDER<\/span> BY<\/span> fiscal_year\r\n  ) AS<\/span> vs_previous_year\r\nFROM<\/span> \r\n  sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------+-------------+--------+--------------------+------------------+\r\n| sales_employee | fiscal_year | sale   | previous_year_sale | vs_previous_year |\r\n+----------------+-------------+--------+--------------------+------------------+\r\n| Alice          |        2016<\/span> | 150.00<\/span> |               0.00<\/span> |           150.00<\/span> |\r\n| Alice          |        2017<\/span> | 100.00<\/span> |             150.00<\/span> |           -50.00<\/span> |\r\n| Alice          |        2018<\/span> | 200.00<\/span> |             100.00<\/span> |           100.00<\/span> |\r\n| Bob            |        2016<\/span> | 100.00<\/span> |               0.00<\/span> |           100.00<\/span> |\r\n| Bob            |        2017<\/span> | 150.00<\/span> |             100.00<\/span> |            50.00<\/span> |\r\n| Bob            |        2018<\/span> | 200.00<\/span> |             150.00<\/span> |            50.00<\/span> |\r\n| John           |        2016<\/span> | 200.00<\/span> |               0.00<\/span> |           200.00<\/span> |\r\n| John           |        2017<\/span> | 150.00<\/span> |             200.00<\/span> |           -50.00<\/span> |\r\n| John           |        2018<\/span> | 250.00<\/span> |             150.00<\/span> |           100.00<\/span> |\r\n+----------------+-------------+--------+--------------------+------------------+\r\n9<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL LAG()<\/code> function to access data of the previous row from the current row.<\/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